Decision Tree-based Parameter Optimization Method for Database Cost Model and Its Query Method

Abstract

The present invention discloses a method for optimizing database cost model parameters based on decision trees and its query method. For a database instance under specific software and hardware settings, the present invention establishes a database cost model parameter tree, uses database configuration parameters and query statement features as splitting dimensions to partition the parameter space, and solves the optimal cost model parameters by linear fitting of training samples in each partition. During operation, the parameter tree assigns different cost model parameters to query statements under different parameter configurations and data distributions, so as to perform accurate cost prediction. Experiments show that this method improves the prediction accuracy of traditional rule-based estimation models and optimizes the query performance of the database.

Description

Technical Field

[0001] The present invention designs a method for optimizing parameters of a database cost model based on a decision tree, belonging to the field of query optimization in database management system software. Background Art

[0002] A database management system software (hereinafter collectively referred to as the database) is responsible for storing and retrieving data. After a user inputs a query statement, the database will obtain data from the storage according to the generated execution plan and perform corresponding processing, and return the data that meets the requirements. The same query statement may generate multiple possible execution plans in the database, and the differences include data scanning methods, table connection methods, table connection orders, etc. Different execution plans will result in different execution times, and there may even be an order-of-magnitude difference. To improve the performance of the database, the database should be able to select an execution plan with a shorter execution time in the execution plan search space.

[0003] Currently, most implementations of databases adopt a cost-based query optimization method, setting a cost model to estimate the costs of different query execution plans and selecting the execution plan with the minimum cost for execution. A good cost model needs to provide accurate cost estimates to help the database optimize the execution time of query statements and improve the performance of the database; at the same time, the cost model should be able to quickly estimate the cost of the execution plan because the database query optimizer module will call the cost model multiple times to evaluate the performance of the execution plan during the process of selecting the execution plan.

[0004] Traditional cost-based query optimization methods use a rule-based cost model to estimate the execution cost of operations by reading the number of pages and the number of processed tuples, and allow database administrators to perform parameter tuning according to the performance of physical machines through adjustable cost parameters. However, the overly simplified cost model ignores the impacts of some hardware and software configurations, database parameter configurations, and data distributions of data sets, resulting in deviations between the cost estimation results of query statements and the actual execution costs during the actual operation of the database. For this reason, learning-based cost models have emerged, which predict the execution cost of query plans by extracting features of data operations or query plans, improving the prediction accuracy of the cost model. However, learning-based cost models require long training and inference times, and need to be retrained when the database configuration or data distribution changes, with a narrow scope of application. Summary of the Invention

[0005] The rule-based cost model considers many fine-grained features during implementation, has better interpretability and generality compared with the learning-based cost model. However, its effect depends on the selection of parameters, and these parameters are affected by the hardware and software configurations, database parameter configurations, and data distributions of the dataset. Aiming at the deficiencies of the prior art, the purpose of the present invention is to provide a method for optimizing the parameters of a database cost model based on a decision tree, and select the cost model parameters that most conform to the actual execution environment for the rule-based cost model according to the database and dataset parameter configurations during the execution of the query statement. The present invention first determines the variables affecting the parameters of the rule-based cost model, and then trains the root node of the cost model parameter tree through a predefined set of query statements, and continuously trains the cost model parameter tree in an incremental learning manner when the database executes the workload query statement set. In actual use, given a configuration combination, the model returns a recommended setting of the cost model parameters, and the cost model uses these parameters to estimate the cost of the execution plan.

[0006] The specific technical solution adopted by the present invention is as follows:

[0007] In the first aspect, the present invention provides a method for optimizing the parameters of a database cost model based on a decision tree, which includes the following steps:

[0008] S1. Run a predefined set of query statements in the database instance to obtain the execution results of each query statement in the set; extract the key features of the data operation and the execution time from the execution results of each query statement respectively as a first training sample; use all the first training samples as fitting data to fit the cost model in the form of a linear model, and use the initial value of the cost model parameters obtained by fitting as the root node of the cost model parameter tree;

[0009] S2. Execute different query statements in the database workload, and for each query statement, use the latest cost model parameter tree as a decision tree, navigate to the corresponding node in the decision tree through the database configuration parameters and query statement features, so as to determine the cost model parameters for calculating the costs of different execution plans, and extract the key features of the data operation and the execution time from the query statement execution results as the second training sample associated with the corresponding node; when the second training sample associated with a node in the decision tree reaches the node splitting condition, use the database configuration parameters and query statement features as the splittable dimensions, and use the Model-based Recursive Partitioning (MOB) method to split the cost model parameter tree to form child nodes corresponding to different subspaces, and then divide the second training samples on the parent node to each child node and fit the cost model parameters corresponding to each child node respectively;

[0010] S3. Continuously execute S2 to iteratively train the decision tree in an incremental learning manner, thereby continuously splitting nodes, so that different leaf nodes of the trained decision tree respectively have cost model parameters corresponding to different database configuration parameters and query statement features; finally, use the trained decision tree to determine the cost model parameters for the query statement under the given database configuration parameters, which are used for the database instance to estimate the cost of the execution plan.

[0011] Based on the above technical solutions, the present invention can further provide the following preferred ways. And the technical features in each preferred way of the present invention can be combined correspondingly without conflict.

[0012] As a preference of the first aspect above, in S1, the predefined query statement set is a set of single-table scan statements generated for the test data set, and each single-table scan statement includes two data operations: index scan and sequential scan.

[0013] As a preference of the first aspect above, in S1, the key features of the data operation include the number of times N of sequentially reading disk pages s , the number of times N of randomly reading disk pages r , the number of times N of executing operators or functions o , the number of data rows N processed in the query t , the number of index entries N processed in the index scan i .

[0014] As a preference of the first aspect above, in S1, the form of the cost model is

[0015] Cost = N t × r t + N o × r o + N i × r i + N s × r s + N r × r r

[0016] In the formula: Cost represents the cost, r t represents the cost estimate of one sequential disk page fetch, r r represents the cost estimate of one random disk page read, r o represents the cost estimate of processing each operator or function in one query, r t represents the cost estimate of processing each row in one query, r i represents the cost estimate of processing each index entry in one index scan.

[0017] Preferably, in the above first aspect, in S1, when fitting the cost model in the form of a linear model, by minimizing the objective function the optimal cost model parameters are obtained Taking these cost model parameters as the root node element of the cost model parameter tree; where I is the training sample set composed of all the first training samples, and the error term of the i-th training sample r = [r t , r o , r i , r s , r r T is the database cost model parameter to be optimized, x i = [N t , N o , N i , N s , N r T is the key feature of the data operation in the query statement execution result in the i-th training sample, is the cost estimation result of the i-th training sample, is the actual execution time of the i-th training sample.

[0018] Preferably, in the above first aspect, the specific process of S2 is as follows:

[0019] S21. The database executes the workload, and while executing, records the execution plan of the query statement and the database configuration parameters when the query statement is executed. Then, according to the query statement features and the database configuration parameters when the query statement is executed, the corresponding leaf node is found on the decision tree. Then, the cost model parameters owned by the leaf node are input into the cost model, the execution cost of each execution plan is calculated, and the execution plan with the minimum cost is selected for execution; after the execution is completed, the database extracts the key features and execution time of the data operation in the execution plan from the recorded query statement execution result, and forms the second training sample under the corresponding leaf node in the cost model parameter tree.

[0020] S22. Continuously repeat S21, accumulate the second training samples on the leaf nodes of the cost model parameter tree. If the second training sample set under a leaf node reaches the node splitting condition, for this leaf node, use the model-based recursive partitioning method to select the splitting dimension and splitting value to form multiple child nodes, and then divide the second training samples on the parent node corresponding to each child node, and use all the second training samples on each child node as the fitting data to refit the cost model, and obtain the cost model parameters corresponding to each child node.

[0021] ​​Preferably, in the split dimensions of the cost model parameter tree, for the dimension of the database configuration parameters, the selected dimensions are the configuration parameters that can be adjusted when the database executes a query statement, and for the dimension of the query statement features, the selected dimensions are the information related to data distribution in the query statement.

[0022] Preferably, in the split dimensions of the cost model parameter tree, the dimensions of the database configuration parameters include but are not limited to work_mem and temp_buffer.

[0023] Preferably, in the split dimensions of the cost model parameter tree, the dimensions of the query statement features include but are not limited to the offset of columns, the correlation between columns, and the data type.

[0024] In a second aspect, the present invention provides a database query method, which is as follows: after obtaining the trained decision tree according to the optimization method described in the first aspect above, the database parses the target query statement to generate a logical execution plan composed of multiple operations, and then generates multiple feasible physical execution plans according to the logical execution plan; input the database configuration parameters and query statement features when executing the target query statement into the trained decision tree, navigate to the corresponding leaf node in the decision tree and obtain the cost model parameters corresponding to the leaf node; substitute the obtained cost model parameters into the cost model, estimate the cost of each physical execution plan of the target query statement, and select the physical execution plan with the smallest cost according to the estimation result to execute the data query and return the query result.

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

[0026] For a query statement, the present invention can match the subspace in the cost model parameter tree according to the database configuration parameters and the information provided by the query statement, obtain the recommended cost model parameters in this space, and provide accurate cost estimation. Compared with the rule-based cost model under default parameters, the estimated cost provided by the present invention is more in line with the actual running time of the execution plan, and can assist the database in selecting an execution plan with a shorter running time, thereby improving the query performance of the database. Compared with the learning-based cost model, the present invention requires a shorter training duration and can quickly train an available model to cope with changes in the database software and hardware configuration and the data distribution of the data set. In summary, the present invention provides an accurate and efficient cost model optimization method. BRIEF DESCRIPTION OF THE DRAWINGS

[0027] Figure 1 is a flowchart of the implementation steps of the database cost model parameter optimization method based on a decision tree of the present invention.

[0028] Figure 2 is a schematic diagram of the execution process of a database query statement.

[0029] Figure 3 It is a schematic diagram of the use of the database cost model parameter tree of the present invention.

[0030] Figure 4 It is a schematic diagram of the node splitting of the database cost model parameter tree of the present invention. Detailed implementation manners

[0031] To make the above objects, features, and advantages of the present invention more obvious and understandable, the following will describe the detailed implementation manners of the present invention in conjunction with the accompanying drawings. Many specific details are set forth in the following description to facilitate a full understanding of the present invention. However, the present invention can be implemented in many other ways different from those described herein, and those skilled in the art can make similar improvements without departing from the connotation of the present invention. Therefore, the present invention is not limited by the specific embodiments disclosed below.

[0032] In the description of the present invention, it should be understood that the terms "first" and "second" are only used for the purpose of distinguishing descriptions, and cannot be understood as indicating or implying relative importance or implicitly indicating the quantity of the indicated technical features. Thus, the features defined with "first" and "second" may explicitly or implicitly include at least one of such features.

[0033] As Figure 1 shown, in a preferred embodiment of the present invention, a method for optimizing database cost model parameters based on a decision tree is provided, which specifically includes the following steps:

[0034] Step 1: For a database instance under a specific hardware and software configuration, first execute a predefined set of query statements to obtain the execution results of each query statement in the set; respectively extract the key features of the data operation and the execution time from the execution results of each query statement as a first training sample; use all the first training samples as fitting data, and fit a cost model in the form of a linear model through a fitting method based on a linear model to obtain the initial value of the optimal cost model parameters under the software and hardware configuration of the database instance, and use the initial value of the cost model parameters obtained by fitting as the root node of the cost model parameter tree. So far, it can be regarded as the operation of database administrators adjusting cost model parameters according to hardware and software configurations in the traditional scenario.

[0035] It should be noted that the software and hardware configuration of the database instance in the present invention includes hardware configuration (hard disk type, memory size, CPU frequency, etc.) and software configuration (system version, database software version, etc.), which will not change during the operation of the database instance. Since the hardware and software configuration affects the selection of the optimal parameters of the cost model, the specific software and hardware configuration of the database instance needs to be set according to the finally required database instance.

[0036] It should be noted that the data operations in the present invention refer to operations such as scanning operations and connection operations, and its key features include five parameters: N s represents the number of times of sequentially reading disk pages, N r represents the number of times of randomly reading disk pages, N o represents the number of times of executing operators or functions, N t represents the number of data rows processed in a query, N i represents the number of index entries processed in an index scan. The execution time of the data operation consists of the IO time and the CPU time. In actual applications, the above data can be obtained according to the functions provided by the database software.

[0037] It should be noted that in a database adopting a cost-based optimization method, the specific execution process of a query statement is as follows: After receiving the query statement, the database parses the statement to generate a logical execution plan composed of multiple operations. The same logical operation can have different physical implementations. For example, the implementation of a scanning operation includes sequential scanning, index scanning, bitmap scanning, etc. Thus, the logical execution plan can further generate multiple feasible physical execution plans. The database running cost model estimates the cost of each physical execution plan, and finally selects the physical execution plan with a lower cost for execution, returns the query result, and records the key features and execution time of the data operation during the process. As Figure 2 shown, for the execution process of a query statement in an example, among the four physical execution plans A to D, the cost of plan B is the smallest in the execution plan cost finally estimated by the cost model, which is 150. Therefore, this physical execution plan is selected as the final execution plan, and finally an execution result including the query result, operation characteristics, and execution time is obtained.

[0038] The traditional rule-based cost model provides five adjustable parameters to database administrators, and each data operation has different cost formulas according to the operation type. A specific query plan is a tree structure of operations, and its cost will be composed of the costs of the corresponding operations. When calculating the initial values of the cost model parameters of the database instance, the predefined query statement set refers to the single-table scan statements generated for the test data set, including two data operations: index scan and sequential scan. Its cost model can be unified into a linear model involving five adjustable parameters:

[0039] Cost=N t ×r t +N o ×r o +N i ×r i +N s ×r s +N r ×r r

[0040] Among them, r t represents the cost estimate of a sequential disk page fetch, and r r represents the cost estimate of a non-sequential disk page fetch (i.e., random disk page read), and r o represents the cost estimate of processing each operator or function in a query, and r t represents the cost estimate of processing each row in a query, and r i represents the cost estimate of processing each index entry in an index scan. Cost represents the cost, which is equivalent to the database query execution time to be predicted.

[0041] Steps 1-3: When calculating the initial value of the cost model of the database instance, a fitting method based on a linear model is used. The fitting method based on the linear model is described in detail below.

[0042] Denote the training sample set composed of all the first training samples obtained after executing the predefined query statement set as I. When fitting the cost model in the form of a linear model, the optimal cost model parameters can be obtained by minimizing the objective function That is, the initial value of the cost model parameters is obtained. Denote the cost model parameters as the root node element of the cost model parameter tree. Among them, the error term of the i-th training sample r = [r t , r o , r i , r s , r r T is the database cost model parameter to be optimized, and x i = [N t , N o , N i , N s , N r T is the key feature of the data operation in the query statement execution result of the i-th training sample, is the cost estimate result of the i-th training sample, is the actual execution time of the i-th training sample.

[0043] ​​Step 2: Execute different query statements in the database workload. For each query statement, use the latest cost model parameter tree as the decision tree. Navigate to the corresponding node in the decision tree through database configuration parameters and query statement features to determine the cost model parameters for calculating the costs of different execution plans. Extract the key features of data operations and the execution time from the query statement execution results as the second training samples associated with the corresponding node. When the second training samples associated with a node in the decision tree reach the node splitting condition, use the database configuration parameters and query statement features as the splittable dimensions, and perform node splitting on the cost model parameter tree using the Model-based Recursive Partitioning (MOB) method to form child nodes corresponding to different subspaces. Then, divide the second training samples on the parent node into each child node and fit them respectively to obtain the cost model parameters corresponding to each child node.

[0044] As a preferred implementation manner of the embodiment of the present invention, the above Step 2 can be implemented through the following Step 2-1 and Step 2-2.

[0045] Step 2-1: The database executes the workload and records the execution plan of the query statement and the database configuration parameters when the query statement is executed while executing. Then, find the corresponding leaf node from the decision tree according to the query statement features and the database configuration parameters when the query statement is executed. Then, input the cost model parameters owned by the leaf node into the cost model to calculate the execution costs of each execution plan, and select the execution plan with the minimum cost for execution. After the execution is completed, the database extracts the key features of data operations and the execution time in the execution plan from the recorded query statement execution results to form the second training samples under the corresponding leaf node in the cost model parameter tree.

[0046] It should be noted that when the present invention executes the query statements in the database workload, various data can be recorded in the database historical query records. Extracting the query plan from the database historical query records can be used to construct training data.

[0047] It should be noted that the splittable dimensions of the cost model parameter tree include two aspects, namely database configuration parameters and query statement features. Among them, the dimensions of database configuration parameters can select the configuration parameters that can be adjusted by the database when executing query statements (such as configuration parameters like work_mem and temp_buffer), while the dimensions of query statement features can select the information related to data distribution in the query statement (such as column offsets, inter-column correlations, data types, etc.). According to the query statement features and the database configuration parameters during the execution of the query statement, the database finds the corresponding leaf node for the data operation, and then the recommended cost model parameters can be obtained. Inputting them into the cost model calculation formula to get the execution cost of the data operation, and select the execution plan with the minimum cost to execute. Then, the database records the features and execution time of the operations in the query statement execution result, forming training samples under the corresponding leaf nodes of the cost model parameter tree.

[0048] In a specific example of the present invention, the structure and usage process of the cost model parameter tree are as Figure 3 shown. The root node of the cost model parameter tree is the initial value of the cost model parameters formed by fitting all the first training samples obtained by running the test data set. The root node is split according to the scan operation mode, including sub-nodes such as sequential scan SeqScan and index scan IndexScan. And the nodes under IndexScan are further divided into different sub-nodes according to the inter-column correlation Index_correlation. During the operation of the workload, the query statement shown in the figure generates two types of execution plans: sequential scan SeqScan and index scan IndexScan. Therefore, the execution plan of SeqScan is navigated to the SeqScan leaf node, and the cost of the execution plan is calculated using the cost model parameters on this node. While the execution plan of IndexScan is navigated to the leaf node where Index_correlation ≤ 0.5, and the cost of the execution plan is calculated using the cost model parameters on this node. After comparing the costs of the two execution plans, finally, the execution plan of IndexScan is selected to execute, and the execution result including operation features and execution time is obtained, which is associated with the leaf node where Index_correlation ≤ 0.5.

[0049] It should be particularly noted that after the initial construction of the cost model parameter tree, there is only the root node. At this time, according to the query statement features and the database configuration parameters during the execution of the query statement, the leaf node found from the decision tree is the root node, and the generated second training samples are also associated with the root node.

[0050] Step 2-2: Continuously repeat Step 2-1, accumulate the second training samples on the leaf nodes of the cost model parameter tree. If the second training sample set under a leaf node reaches the node splitting condition, use the model-based recursive partitioning method for this leaf node to select the splitting dimension and splitting value to form multiple child nodes, then partition the second training samples on the parent node to each child node correspondingly, and use all the second training samples on each child node as the fitting data to refit the cost model to obtain the cost model parameters corresponding to each child node.

[0051] It should be noted that the above model-based recursive partitioning method, i.e., the MOB method, belongs to the prior art. For details, please refer to the prior art literature: Zeileis A, Hothorn T, Hornik K. Model-based recursive partitioning[J]. Journal of Computational and Graphical Statistics, 2008, 17(2): 492-514. For the convenience of understanding, the following details the specific process of using the MOB method to split the child nodes of the cost model parameter tree in an embodiment:

[0052] Step 2-2-1: Define the optimization objective after a node splits into child nodes. Denote the partitioning method as I b to represent the sample set of the partition. For each partition i.e., each split child node, fit the cost model through the linear model The parent node needs to minimize the global objective function to obtain For the given partition set calculate the optimal parameter estimation of each partition greedily to obtain the global optimal parameter estimation

[0053] Step 2-2-2: Determine the value range H of the split dimension variable of the cost model parameter tree, and set the confidence level α. Test the parameter instability for all split dimension variables h∈H according to the MOB partitioning method. By using the supLM test for numerical variables and the chi-square test for categorical variables, each split dimension variable will obtain a p-value representing its stability. Select the smallest p-value representing the variable with the highest instability. If this value is less than the confidence level α, the original node can be split according to the corresponding split dimension variable.

[0054] Step 2-2-3: For the selected split dimension variable h, the value range of its candidate split value is uh For each candidate split value s ∈ u h , the original node will be split into two child nodes where x ≤ s and x > s according to the split value s, and the training samples in the original node will be assigned to the corresponding child nodes. Then, according to the global objective function given in step 2-2-1, calculate the optimal global objective function value G(s) = min J s (I, r) of the original node at this time, and select the split value that minimizes the global objective function of the original node after splitting to split the original node, that is

[0055] In a specific example of the present invention Figure 3 The process of splitting the cost model parameter tree nodes in Figure 4 is as shown

[0056] It should be noted that since the cost model parameter tree of the present invention is continuously iteratively updated during the training process, the decision tree relied on when the database executes the query statement needs to adopt the cost model parameter tree at the latest execution time

[0057] Step 3: Repeat step 2, and continuously split the nodes according to the database configuration parameters and query statement features to divide the parameter subspace, so that query statements with different configurations and different data distributions have independent cost model parameters

[0058] Continuously repeat step 2 to iteratively train the decision tree in an incremental learning manner, so that the decision tree can continuously split the nodes according to the database configuration parameters and query statement features to divide the parameter subspace, and finally make the different leaf nodes of the trained decision tree have cost model parameters corresponding to different database configuration parameters and query statement features

[0059] The training process of the decision tree belongs to the prior art and will not be elaborated here. The final termination condition of the training can refer to the existing decision tree construction algorithm framework

[0060] After the decision tree training is completed, finally use the trained decision tree to determine the cost model parameters for the query statements under the given database configuration parameters, which are used for the database instance to estimate the cost of the execution plan

[0061] Further, in another preferred embodiment of the present invention, a database query method is provided. Specifically, after obtaining the trained decision tree according to the database cost model parameter optimization method based on the decision tree given in the above embodiment, the database parses the target query statement to generate a logical execution plan composed of multiple operations, and then generates multiple feasible physical execution plans according to the logical execution plan; the database configuration parameters and query statement features when executing the target query statement are input into the trained decision tree, navigate to the corresponding leaf node in the decision tree and obtain the cost model parameters corresponding to the leaf node; substitute the obtained cost model parameters into the cost model, estimate the cost of each physical execution plan of the target query statement, and select the physical execution plan with the smallest cost according to the estimation result to execute the data query and return the query result.

[0062] In summary, for a database instance under specific software and hardware settings, the present invention establishes a database cost model parameter tree, uses database configuration parameters and query statement features as splitting dimensions to partition the parameter space, and solves the optimal cost model parameters through linear fitting of training samples in each partition. In practical applications, the cost model parameter tree can assign different cost model parameters to query statements under different parameter configurations and data distributions, so as to perform accurate cost prediction. Experiments show that this method improves the prediction accuracy of traditional rule-based estimation models and optimizes the query performance of the database.

[0063] The above embodiments are only a preferred solution of the present invention, but they are not intended to limit the present invention. Those of ordinary skill in the relevant technical fields can still make various changes and modifications without departing from the spirit and scope of the present invention. Therefore, all technical solutions obtained by adopting the means of equivalent replacement or equivalent transformation fall within the protection scope of the present invention.

Claims

1. A method for optimizing parameters of a database cost model based on a decision tree, characterized in that, The steps are as follows: S1. Run a predefined set of query statements in a database instance to obtain the query statement execution results corresponding to each query statement in the set; extract the key features of data operations and the execution time from each query statement execution result as a first training sample; Use all the first training samples as fitting data to fit a cost model in the form of a linear model, and use the initial values of the cost model parameters obtained by fitting as the root node of the cost model parameter tree; S2. Execute different query statements in the database workload, and for each query statement, use the latest cost model parameter tree as a decision tree. Navigate to the corresponding node in the decision tree through database configuration parameters and query statement features to determine the cost model parameters for calculating the costs of different execution plans, and extract the key features of data operations and the execution time from the query statement execution results as the second training sample associated with the corresponding node; when the second training sample associated with a node in the decision tree reaches the node splitting condition, use the database configuration parameters and query statement features as the split dimensions, and use a model-based recursive partitioning method to split the cost model parameter tree to form child nodes corresponding to different subspaces, and then divide the second training samples on the parent node into each child node and fit the cost model parameters corresponding to each child node respectively; S3. Continuously execute S2 to iteratively train the decision tree in an incremental learning manner to continuously perform node splitting, so that different leaf nodes on the trained decision tree have cost model parameters corresponding to different database configuration parameters and query statement features; finally, use the trained decision tree to determine the cost model parameters for the query statements under the given database configuration parameters for the database instance to estimate the cost of the execution plan; The specific process of S2 is as follows: S21. The database executes the workload and records the execution plan of the query statement and the database configuration parameters when the query statement is executed while executing. Then, according to the query statement features and the database configuration parameters when the query statement is executed, find the corresponding leaf node in the decision tree, and then input the cost model parameters owned by the leaf node into the cost model to calculate the execution costs of each execution plan, and select the execution plan with the minimum cost for execution; after the execution is completed, the database extracts the key features and execution time of the data operations in the execution plan from the recorded query statement execution results to form the second training sample under the corresponding leaf node in the cost model parameter tree; S22. Continuously repeat S21 to accumulate the second training samples on the leaf nodes of the cost model parameter tree. If the second training sample set under a leaf node reaches the node splitting condition, use a model-based recursive partitioning method to select the split dimension and split value for the leaf node to form multiple child nodes, and then divide the second training samples on the parent node into each child node, and use all the second training samples on each child node as fitting data to refit the cost model to obtain the cost model parameters corresponding to each child node.

2. The method for optimizing database cost model parameters based on a decision tree according to claim 1, wherein In S1, the set of predefined query statements is a set of single-table scan statements generated for the test data set, and each single-table scan statement includes two data operations: index scan and sequential scan.

3. The method for optimizing database cost model parameters based on a decision tree according to claim 2, characterized in that, In S1, the key features of the data operation include the number N of times of sequentially reading disk pages s , the number N of times of randomly reading disk pages r , the number N of times of executing operators or functions o , the number N of data rows processed in a query t , the number N of index entries processed in an index scan i .

4. The method for optimizing database cost model parameters based on a decision tree according to claim 3, characterized in that In S1, the form of the cost model is Cost=N t ×r t +N o ×r o +N i ×r i +N s ×r s +N r ×r r Where: Cost represents the cost, r t represents the cost estimate for a sequential disk page fetch, r r represents the cost estimate for a random disk page read, r o represents the cost estimate for processing each operator or function in a query, r t represents the cost estimate for processing each row in a query, r i represents the cost estimate for processing each index entry in an index scan.

5. The method for optimizing database cost model parameters based on a decision tree according to claim 1, characterized in that In S1, when fitting the cost model in the form of a linear model, the optimal cost model parameters are obtained by minimizing the objective function ; Taking these cost model parameters as the root node element of the cost model parameter tree; where I is the training sample set composed of all first training samples, and the error term of the i-th training sample r = [r t , r o , r i , r s , r r ; T are the database cost model parameters to be optimized, x i = [N t , N o , N i , N s , N r ; T is the key feature of the data operation in the query statement execution result of the i-th training sample, is the cost estimation result of the i-th training sample, is the actual execution time of the i-th training sample.

6. The method for optimizing database cost model parameters based on decision tree according to claim 1, characterized in that Among the splittable dimensions of the cost model parameter tree, for the dimension of the database configuration parameters, the selected configuration parameters are those that can be adjusted when the database executes the query statement, and for the dimension of the query statement features, the selected information is the information related to the data distribution in the query statement.

7. The method for optimizing database cost model parameters based on a decision tree according to claim 1, characterized in that The dimensions of the database configuration parameters include work_mem and temp_buffer.

8. The method for optimizing database cost model parameters based on decision tree according to claim 1, wherein, The dimensions of the query statement features include the offset of columns, the correlation between columns, and the data type.

9. A database query method, characterized in that, After obtaining the trained decision tree according to the optimization method described in claim 1, the database parses the target query statement to generate a logical execution plan composed of multiple operations, and then generates multiple feasible physical execution plans according to the logical execution plan; Input the database configuration parameters and query statement features when executing the target query statement into the trained decision tree, navigate to the corresponding leaf node in the decision tree and obtain the cost model parameters corresponding to the leaf node; substitute the obtained cost model parameters into the cost model, estimate the cost of each physical execution plan of the target query statement, and select the physical execution plan with the minimum cost according to the estimation result to execute the data query and return the query result.

Images