Database execution plan selection method based on robustness and cost fusion

Abstract

The present invention discloses a method for selecting a database execution plan based on the fusion of robustness and cost, which mainly solves the problem that the execution plan selected by the prior art is unstable during long-term execution. The implementation solution is as follows: using the cardinality-integral robustness metric to quantify the robustness of the execution plan; expanding the upper and lower bounds of the single cardinality estimate [f ↓ , f ↑ for the cardinality estimate; combining the actual data to explore the cardinality and its probability distribution within the range; using the cardinality of the probability distribution to improve the optimizer, and using the optimizer to perform weighted area integration within [f ↓ , f ↑ to normalize the robustness and the estimated cost into a single value; selecting candidate plans according to this value to achieve the optimization of the cost model in the optimizer stage. The execution plan found by the present invention can simultaneously meet the dual indicators of low plan cost and strong robustness, avoid the sensitivity to uncertain execution plans, improve the stability of the operation of the database system, and can be used for database system queries.

Description

Technical Field

[0001] The invention belongs to the technical field of databases, and in particular relates to a database execution plan selection method, which can be used for querying a database system. Background Art

[0002] Traditional databases use a classic cost-based approach to obtain efficient query execution plans. The query optimizer enumerates a subset of valid join orders, uses cardinality estimates as its primary input, and then selects the lowest-cost plan from semantically equivalent plans. In theory, as long as the cardinality estimates and cost model are accurate, this architecture can obtain the optimal query plan.

[0003] In database systems, cardinality estimation has always been an important and difficult problem. Researchers have been trying to use various methods and techniques to improve the accuracy and robustness of estimation. Traditional cardinality estimation methods can be roughly divided into two categories: summary-based and sampling-based methods.

[0004] The summary-based cardinality estimation method collects some statistical information of the database in advance and can easily and quickly solve the query cardinality based on simple assumptions of independence and uniform data distribution. For example, the histogram-based method, data profiling, etc.

[0005] Sampling-based methods randomly extract a certain proportion or a certain number of tuples from the original data table, and finally obtain an estimate of the cardinality of the query in the original database by dividing the result size after executing the query on the sampled set by the corresponding scaling ratio. However, sampling-based methods can easily produce results that do not match the sampled set due to the randomness of sampling.

[0006] The methods used in the above cardinality estimation are all based on simple assumptions of consistency and data independence, but outdated statistics or invalid assumptions may lead to significant cardinality estimation errors. If the cardinality estimation is significantly biased, even a detailed enumeration of join orders and a completely accurate cost model cannot select the optimal execution plan.

[0007] The cost model selects a plan from the search space, and the cost-based approach used can be traced back to System R. The rules used by current database query optimizers to calculate plan costs are mathematical formulas that include I / O overhead and CPU overhead, but due to the incorrect assumptions of uniform distribution and independence of data in the mathematical formula and the limitation that the mathematical formula itself cannot cope with complex predicates, it is not always possible to select the optimal query execution plan.

[0008] In recent years, many optimization and improvement methods for cardinality estimation and cost models have been proposed, such as machine learning. Although the current methods are more accurate than traditional methods, they also take a lot of time to train the models, and most models are difficult to adapt to the update environment. When a large amount of data is inserted or deleted, these models are difficult to track these changes, so they need to be retrained.

[0009] Some existing plans that take robustness into consideration also have the following technical defects:

[0010] The Risk Score proposed by Brendle M et al. in their paper "Robustness metrics for relational query execution plans" (Proceedings of the VLDB Endowment, 2018, 11(11): 1360-1372) is an indicator for measuring the robustness of a plan, indicating the fragility of the plan under different execution conditions. However, since different execution times are required, it cannot predict the risk score during optimization, so the set of robust plan candidates is limited.

[0011] Proactive Re-Optimization proposed by Babcock B et al. in their paper "Towards a robust query optimizer: a principled and practical approach" (Proceedings of the 2005 ACM SIGMOD international conference on Management of data, 2005: 119-130) searches for two heuristically selected plans for each cardinality estimate and tries to determine the optimal, robust plan among them. If such a plan does not exist, it triggers runtime re-optimization. However, due to the increase in the number of plans, it is limited to left-deep trees, and this limitation excludes other possible robust plans.

[0012] Abhirama M et al. proposed Cost-Stable Plans in the paper “On the stability of plan costs and the costs of plan stability” (Proceedings of the VLDB Endowment, 2010, 3(1-2): 1137-1148), which limits the number of plans by early pruning outliers with large cost differences from the optimal plan. However, it also has the disadvantage of excluding other possible robust plans.

[0013] Most of the above methods are online selections and are limited to certain tree structures or plans that are optimal for certain cardinalities, which greatly reduces the accuracy.

[0014] Wolf F et al. proposed three robustness metrics in the paper "Robustness metrics for relational query execution plans" (Proceedings of the VLDB Endowment, 2018, 11(11): 1360-1372) in combination with statistical information to measure the robustness of the plan. However, this solution uses the robustness metrics as a post-processing step and cannot screen the plans in the optimizer stage. In addition, there is no unified measurement standard for the three metrics, which will result in the inability to select the optimal plan. Summary of the invention

[0015] The purpose of the present invention is to address the above-mentioned existing deficiencies and propose a database execution plan selection method based on the fusion of robustness and cost to avoid premature exclusion of candidate plans and improve the accuracy and stability of selecting execution tasks.

[0016] To achieve the above object, the technical solution of the present invention includes the following:

[0017] (1) Use the cardinality-integral robustness metric to assign a numerical value to the robustness of the execution plan to quantify the robustness of the query execution plan;

[0018] (2) Change the single cardinality estimate in the existing optimizer cost model to the estimated cardinality lower limit f ↓ and the estimated upper limit of the cardinality f ↑ Limit range [f ↓ ,f ↑ ] cardinality estimates within;

[0019] (3) Combined with actual data, explore [f ↓ ,f ↑ ], that is, the exploration of the estimated cardinality probability distribution is divided into two categories: atomic predicates and compound predicates:

[0020] 3a) Setting the atomic predicate distribution probability to 50%, enumerating all possible values ​​of the atomic predicate, and obtaining the estimated cardinality of the atomic predicate and its corresponding probability value;

[0021] 3b) Setting the probability of compound predicate distribution to 50%, the compound predicate is first decomposed into the superposition of multiple atomic predicates, and then each atomic predicate is enumerated to obtain the estimated cardinality of the compound predicate and its corresponding probability value;

[0022] 3c) Use [f↓ ,f ↑ ] replaces the single cardinality value in the traditional optimizer with the cardinality of the corresponding predicates in the range and their probability distribution to obtain the modified optimizer;

[0023] (4) Use the modified optimizer by setting the range [f ↓ ,f ↑ ]The weighted area integral within the ℓ normalizes the robustness and estimated cost into a single value, resulting in the candidate plan cost:

[0024]

[0025] Among them, cost() is the mathematical formula used by the optimizer to calculate the execution plan cost, f i is the ith estimated base value within the set range, freq(f i ) is the probability of the i-th estimated cardinality;

[0026] (5) Based on the candidate plan costs obtained in step (4), select an execution plan that meets both low cost and strong robustness indicators to meet the needs of stable operation of the database for a long time.

[0027] Compared with the prior art, the present invention has the following beneficial effects:

[0028] First, by extending the single cardinality estimate to a limit [f ↓ ,f ↑ ] range, and combined with actual data, explore [f ↓ ,f ↑ ]The probability distribution of the cardinality within the range is used to optimize the statistical information;

[0029] Second, by performing weighted area integration on the cardinality estimates within the upper and lower bounds, robustness is combined with the estimated cost, thus achieving the optimization of the cost model in the optimizer stage;

[0030] Third, by optimizing the query optimization of the traditional optimizer from two aspects: statistical information and cost model, it is possible to effectively select query plans that are relatively insensitive in the specified scenario, so that the database system can execute stably over a long period of time. BRIEF DESCRIPTION OF THE DRAWINGS

[0031] Figure 1 It is a general flow chart for realizing the present invention;

[0032] Figure 2 A parameter cost function curve diagram of the robustness metric building module in the present invention;

[0033] Figure 3 The upper and lower limits of the base number calculated in the present invention are estimated [f↓ ,f ↑ ]'s sub-flowchart;

[0034] Figure 4 Schematic diagram of calculation robustness and cost normalization in the present invention. DETAILED DESCRIPTION

[0035] In order to make the purpose, technical solutions and advantages of the embodiments of the present invention clearer, the technical solutions 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 only part of the embodiments of the present invention, not all of the embodiments.

[0036] The embodiments of the present invention are further described in detail below with reference to the accompanying drawings.

[0037] Reference Figure 1 , the implementation steps of this example are as follows:

[0038] In step one, a numerical value is assigned to the robustness of the execution plan using the cardinality-integral robustness metric.

[0039] There are currently three metrics for quantifying the robustness of query execution plans, namely cardinality-slope robustness metric, selectivity-slope robustness metric, and cardinality-integral robustness metric. This example uses, but is not limited to, the cardinality-integral robustness metric to specify a numerical value for the robustness of a plan.

[0040] The cardinality-integral robustness metric, whose definition is based on the cardinality-slope robustness metric, is defined as follows:

[0041]

[0042] Among them, P is a complete query execution plan;

[0043] e is an intermediate result of an execution plan with estimated and actual statistics;

[0044] E is the set of all intermediate results e of the execution plan;

[0045] It is an edge weighted function that specifies an error sensitivity for each intermediate result e of the execution plan, expressed as

[0046] δ f,e is the base-slope value, indicating that given the estimated base f * When the PCF of an execution plan intermediate result e is f,e The slope value of PCF f,eTo model the execution plan P as a cost parameter function of cardinality f on each intermediate result e in the plan; PCF A and PCF B Respectively represent the robustness of two different execution plans A and B, which are modeled as functions of cost with respect to cardinality, PCF B The slope is less than PCF A The slope of indicates that execution plan B is more robust than execution plan A. Figure 2 shown.

[0047] Cardinality-integral robustness measure r ∫f (P) Replace the single cardinality estimate in the cardinality-slope robustness measure with the integral of the cardinality estimates within given upper and lower bounds, which is defined as follows:

[0048]

[0049] Among them, ∫ f,e is to estimate [f ↓ ,f ↑ ] range for PCF f,e The base number for integration - the integral value, expressed as

[0050] This step uses r ∫f (P) To specify a value for the robustness of the execution plan, set r ∫f (P) is specified as the robustness value of the candidate execution plan. The smaller the value, the more robust the corresponding candidate plan is and the more stable the plan is.

[0051] Step 2: Change the single cardinality estimate in the existing optimizer cost model to a given cardinality estimate [f ↓ ,f ↑ ] range of cardinality estimates.

[0052] The existing implementation method of exploring the range of cardinality estimation is to set the lower limit of the cardinality to 0 and the upper limit of the cardinality to the maximum value, but this does not conform to the actual situation. Therefore, this example uses the method of constructing a histogram using interpolation splines to determine the upper and lower limits of the cardinality estimation. Its goal is to construct a result spline with a given error q-error and approximate the cumulative frequency distribution with the minimum q-error. The specific implementation is as follows:

[0053] 2.1) Obtain the true selectivity and estimated selectivity of each column from the data table involved in the query, and calculate the error range q-error required to construct the interpolation spline:

[0054]

[0055] Among them, b i , b′ iare the actual selection rate and the estimated selection rate of the i-th column of the data table, respectively, and b is the vector b i The set of b′ is the vector b′ i A collection of;

[0056] 2.2) Use the interpolation spline greedy algorithm to construct the resulting spline R for a given q-error:

[0057] Reference Figure 3 , the implementation of this step is as follows:

[0058] 2.2.1) Initialize the first point S[1] of the spline curve S of the original data as the base point B, the third point S[3] as the current point C, the upper error limit U as S[2]+(q-error), the lower error limit L as S[2]-(q-error), and set i=3;

[0059] 2.2.2) Select the first point in the spline curve S of the original data as the starting point of the result spline R, and then scan S iteratively;

[0060] 2.2.3) Check whether the line segment between the base point B and the current point C is within the error range q-error:

[0061] If the line segment BC exceeds the upper error limit U or the lower error limit L, it means that the line segment is not within the error range. The upper error limit U is updated to C+(q-error), and the lower error limit L is updated to C-(q-error). The current point C in the previous iteration round is selected and added as the new base point B in the result spline R and the next iteration round begins.

[0062] If the line segment BC does not exceed the upper limit U or the lower limit L, it means that the line segment does not exceed the error range, then update i=i+1, select S[i] as the next current point C, and execute 2.2d)

[0063] 2.2.4) Remember U ′ C+(q-error), connect BU and BU ′ ; Note L ′ For C-(q-error), connect BL and BL ′ , and determine whether the upper limit U and lower limit L need to be updated:

[0064] If line segment BU is on line segment BU ′ Outside, update the upper limit U to U ′ ;

[0065] If line segment BL is on line segment BL ′ Outside, update the lower limit L to L ′ ;

[0066] 2.2.5) Repeat steps 2.2.3) to 2.2.4) until the last point S[n] within the given error range is reached, and the resulting spline R = ;

[0067] 2.3) Use the result spline to determine the upper and lower limits of the cardinality estimate, that is, traverse the result spline R and take the minimum and maximum points in R as the lower limit f of the required cardinality estimate ↓ and the upper limit f ↑ .

[0068] Step 3: Combine actual data to explore ↓ ,f ↑ ]Probability distribution of cardinality within the range.

[0069] The specific implementation of this step is as follows:

[0070] 3a) Setting the atomic predicate distribution probability to 50%, enumerating all possible values ​​of the atomic predicate, and obtaining the estimated cardinality of the atomic predicate and its corresponding probability value;

[0071] 3b) Setting the probability of compound predicate distribution to 50%, the compound predicate is first decomposed into the superposition of multiple atomic predicates, and then each atomic predicate is enumerated to obtain the estimated cardinality of the compound predicate and its corresponding probability value;

[0072] 3c) Use [f ↓ ,f ↑ ]The cardinality of the corresponding predicates in the range and their probability distribution replace the single cardinality value in the traditional optimizer to obtain the modified optimizer.

[0073] Step 4: Use the modified optimizer to calculate the cost of the candidate plan.

[0074] Reference Figure 4 , the specific implementation of this step is as follows:

[0075] In the upper and lower limits [f ↓ ,f ↑ ] to perform weighted area integration on the estimated cardinality within the range, normalizing the robustness and estimated cost into a single value The cost() function is expressed as follows:

[0076] cost=basecost+RowCount*perRowCost

[0077] Among them, basecost is the optional cost of reading data and the preparation cost of performing filtering operations; perRowCost is the cost of processing each row of data; RowCount is the estimated value of the cardinality that meets the filtering conditions, that is, the setting range [f ↓ ,f ↑ ] within the estimated cardinality.

[0078] For different operation operators, the cost() function has different representations in the Cockroach database. For example, Scan, Select, Hash Join, Merge Join, and Lookup Join are five commonly used operators, which are represented as follows:

[0079] The cost() function of the Scan operator is:

[0080] cost=basecost+rowCount*(seqIOCostFactor+perRowCost)

[0081] Among them, basecost is the basic cost of scanning and loading data, including optional reverse scanning, partitioning, virtual tables, etc.; rowCount is the estimated number of rows in the scan operation result, and the value is [f ↓ ,f ↑ ]; seqIOCostFactor is the continuous read and write factor, which is 1; perRowCost is the cost of scanning a row of data calculated by the auxiliary function rowScanCost();

[0082] The cost() function of the Select operator is:

[0083] cost=inputRowCount*filterPerRow+filterSetup

[0084] Among them, inputRowCount is the number of input rows of the Select operation, and the value is [f ↓ ,f ↑ ]; filterPerRow is the cost of performing the filtering operation for each row of data; filterSetup is the preparation cost for calculating the filtering operation specified by Select;

[0085] The cost() function of the Hash Join operator is:

[0086] cost=1.25*leftRowCount+1.75*rightRowCount*cpuCostFactor

[0087] +filterSetup+rowsProcessed*filterPerRow

[0088] Among them, leftRowCount is the number of rows in the left table; rightRowCount is the number of rows in the right table; cpuCostFactor is the cost of the CPU processing a tuple, which is 0.01; filterSetup is the preparation cost for calculating the filtering operation specified by Hash Join; rowsProcessed is the estimated number of rows participating in Hash Join, which is [f ↓ ,f ↑ ]; filterPerRow is the cost of Hash Join for each row;

[0089] The cost() function of the Merge Join operator is:

[0090] cost=(leftRowCount+rightRowCount)*cpuCostFactor+filterSetup

[0091] + rowsProcessed*filterPerRow

[0092] Among them, leftRowCount is the number of rows in the left table; rightRowCount is the number of rows in the right table; cpuCostFactor is the cost of the CPU processing a tuple, which is 0.01; (leftRowCount+rightRowCount)*cpuCostFactor is the table reading cost; filterSetup is the preparation cost for calculating the filtering operation specified by Merge Join; rowsProcessed is the estimated number of rows participating in Merge Join, which is [f ↓ ,f ↑ ] is the cardinality integral value evenly distributed within the .filterPerRow is the cost of doing Merge Join for each row;

[0093] The cost() function of the Lookup Join operator is:

[0094] cost=lookupCount*perLookupCost+filterSetup+rowsProcessed*perRowCost

[0095] Where, lookupCount is the number of rows in the small table; perLookupCost is the cost required to detect each row in the left table in the right table; filterSetup is the preparation cost for calculating the filtering operation specified by Lookup Join; rowsProcessed is the estimated number of rows participating in Lookup Join, and its value is [f ↓ ,f ↑ ]; filterPerRow is the cost of doing a Lookup Join for each row.

[0096] Step 5: Select an execution plan that meets both low cost and strong robustness.

[0097] This step is to formulate selection rules to select candidate plans based on the estimated cost calculated in step 4. The specific implementation is as follows:

[0098] 5.1) Establish selection rules:

[0099] Limit the selected execution plan cost to at most 1.2 times the cost of the best execution plan selected by the traditional optimizer;

[0100] Limit the robustness value to the first 30% of all candidate plans when their robustness values ​​are arranged from small to large;

[0101] 5.2) Select candidate plans according to the rules established in 5.1):

[0102] When multiple plans that meet the rule 5.1) are selected, the plan with the lowest cost is selected as the final execution plan;

[0103] When a plan that satisfies the rule in 5.1) cannot be selected, the best execution plan selected by the traditional optimizer is used as the final execution plan.

[0104] The detailed description of the above-mentioned embodiments is not intended to limit the scope of the invention claimed for protection, but merely represents selected embodiments of the present invention. Based on the ideas in the present invention, professionals in the field may make various modifications and changes in any form and details, but these amendments and changes based on the ideas of the present invention are still within the scope of protection of the claims of the present invention.

Claims

1. A database execution plan selection method based on robustness and cost fusion, characterized in that: The steps include: 1) Use the cardinality-integral robustness metric to assign a numerical value to the robustness of the execution plan to quantify the robustness of the query execution plan; 2) Change the single cardinality estimate in the existing optimizer cost model to the estimated cardinality lower limit f ↓ and the estimated upper limit of the cardinality f ↑ Limit range [f ↓ ,f ↑ ] cardinality estimates within; 3) Combined with actual data, explore [f ↓ ,f ↑ ], that is, the exploration of the estimated cardinality probability distribution is divided into two categories: atomic predicates and compound predicates: 3a) Setting the atomic predicate distribution probability to 50%, enumerating all possible values ​​of the atomic predicate, and obtaining the estimated cardinality of the atomic predicate and its corresponding probability value; 3b) Setting the probability of compound predicate distribution to 50%, the compound predicate is first decomposed into the superposition of multiple atomic predicates, and then each atomic predicate is enumerated to obtain the estimated cardinality of the compound predicate and its corresponding probability value; 3c) Use [f ↓ ,f ↑ ] replaces the single cardinality value in the traditional optimizer with the cardinality of the corresponding predicates in the range and their probability distribution to obtain the modified optimizer; 4) Use the modified optimizer by setting the range [f ↓ ,f ↑ ], normalize the robustness and estimated cost into a single value, and get the candidate plan cost: Among them, cost() is the mathematical formula used by the optimizer to calculate the execution plan cost, f i is the ith estimated base value within the set range, freq(f i ) is the probability of the i-th estimated cardinality; 5) Based on the candidate plan costs obtained in step 4), select an execution plan that meets both low cost and strong robustness indicators to meet the needs of stable operation of the database for a long time.

2. The method according to claim 1, characterized in that Step 1) Use the cardinality-integral robustness metric to assign a value to the robustness of the execution plan. This is the cardinality-integral robustness metric r among the three robustness metrics defined in the calculation robustness index. ∫f (P) and use it as the robustness value of the execution plan: Where P is a complete query execution plan; e is an execution plan intermediate result with estimated and real statistics; E is the set of all execution plan intermediate results e; It is an edge weighted function that specifies an error sensitivity for each intermediate result e of the execution plan; ∫ f,e To estimate [f ↓ ,f ↑ ] range for PCF f,e The cardinality of the integration - the integral value, PCF is a function that represents the cost of the query execution plan and its sub-plans, where the independent variables are the parameters required to calculate the plan cost and the dependent variable is the cost of the plan.

3. The method according to claim 2, characterized in that The cardinality-integral robustness measure r ∫f The edge weight function in (P) It is expressed as follows: in, The error sensitivity value of each intermediate result e is specified between 0.0 and 1.0, which indicates the degree of influence on subsequent results.

4. The method according to claim 1, characterized in that In step 2), the single cardinality estimate in the existing optimizer cost model is changed to a limited range [f ↓ ,f ↑ ], implemented as follows: 2a) Obtain the true selectivity and estimated selectivity of each column from the data table involved in the query, and calculate the error range q-error required to construct the interpolation spline: in, is max(b i ,1 / b i ), b i and b′ i are the true selection rate and the estimated selection rate of the i-th column in the data table, respectively, and b is the vector b i The set of b′ is the vector b′ i A collection of; 2b) Use the interpolation spline greedy algorithm to construct the resulting spline R with a given error range q-error: 2b1) Initialize the first point S[1] of the spline curve S of the original data as the base point B, the third point S[3] as the current point C, the upper error limit U as S[2]+(q-error), the lower error limit L as S[2]-(q-error), and set i=3; 2b2) Select the first point in the spline curve S of the original data as the starting point of the result spline R, and then scan S iteratively, where |S|=n, indicating that the original data has n points; 2b3 Check whether the line segment between the base point B and the current point C is within the error range. New interpolation nodes are continuously added to R during the iteration process: If the line segment BC exceeds the upper error limit U or the lower error limit L, it means that the line segment is not within the error range. The upper error limit U is updated to C+(q-error), and the lower error limit L is updated to C-(q-error). The current point C in the previous iteration round is selected and added as the new base point B in the result spline R and the next iteration round begins. If the line segment BC does not exceed the upper limit U or the lower limit L, it means that the line segment does not exceed the error range, then update i=i+1, and select S[i] as the next current point C; 2b4) Repeat 2b3) until the last point S[n] in the given range is reached to obtain the resulting spline R= ； 2c) Use the resulting spline to determine lower and upper bounds for the cardinality estimate: Traverse the resulting spline R, the minimum and maximum points in R are the lower limit f of the required cardinality estimate ↓ and the upper limit f ↑ .

5. The method according to claim 1, characterized in that 4) The cost() function is expressed as follows: cost=basecost+RowCount*perRowCost Among them, basecost is the optional cost of reading data and the preparation cost of performing filtering operations; perRowCost is the cost of processing each row of data; RowCount is the estimated value of the cardinality that meets the filtering conditions, that is, the setting range [f ↓ ,f ↑ ] within the estimated cardinality.

6. The method according to claim 1, characterized in that In step 5), based on the candidate plan costs, select an execution plan that meets both low cost and strong robustness indicators. The selection is based on the following principles: Limit the selected execution plan cost to at most 1.2 times the cost of the best execution plan selected by the traditional optimizer; Limit the robustness value to the first 30% of all candidate plans when their robustness values ​​are arranged from small to large; When a plan that meets the above requirements cannot be selected, the best execution plan selected by the traditional optimizer is used.

Images