CN113656437B - Model construction method for predicting execution cost stability of reference - Google Patents

Abstract

The present disclosure provides a model construction method for predicting stability of a reference execution cost, where basic information of the same or similar query steps has the same features, so that the features of the query steps are identified, and a correspondence between the features of the query steps and the reference execution cost is constructed. And updating the reference execution cost of the query step characteristics: the method comprises the steps of obtaining execution result information of any query step completed by a database execution engine, determining characteristics of the query step according to basic information of the query step, determining actual execution cost of the query step information according to the execution result information, and taking the actual execution cost as reference execution cost corresponding to the characteristics of the query step. Thus, when the optimizer engine needs to evaluate the execution cost of a query step, the corresponding reference execution cost can be queried through the characteristics of the query step.

Description

Model building method for predicting reference execution cost stability

Technical field

One or more embodiments of this specification relate to the field of database technology, and in particular, to a model construction method for predicting reference execution cost stability.

Background technique

The database generally includes a parser engine, an optimizer engine and an executor engine. After the database receives a query request for a certain data, the parser engine parses it into a query plan and sends it to the optimizer engine. The optimizer engine analyzes the query plan. After optimization, the optimized query plan is sent to the execution engine. The executor engine completes the query on the data according to the received query plan and obtains the query results.

After the optimizer engine receives the query plan, it will decompose the query plan into query steps and determine whether each query step can be optimized. Specifically, when it is determined that a query step can be replaced by other query steps, it will evaluate the performance of these query steps. Execution cost, determine which query step is more appropriate. For example, if a query involves three tables, the connection order of the three tables is different, and the actual execution cost is also different, then by evaluating the execution cost of each order, determine the table Connection order.

In the existing technology, the optimizer engine generally evaluates the execution cost information of each query step through the basic statistical information of the table. The basic statistical information is stored in the system table of the database, including the number of tables and the rows of each table. However, basic statistical information needs to be traversed through each table for statistics. Therefore, in order not to waste query resources, the basic statistical information is generally updated when the database is idle or there are a large number of data changes, which makes In some cases, although the actual contents of the database have changed, the basic statistical information will not be updated in time, which will affect the accuracy of the optimization results.

Contents of the invention

In view of this, one or more embodiments of this specification provide a reference execution cost determination method.

To achieve the above objectives, one or more embodiments of this specification provide the following technical solutions:

According to the first aspect of one or more embodiments of this specification, a method for optimizing a query plan is proposed. The characteristics of the query steps are used as identifiers to construct a corresponding relationship between the characteristics of the query steps and the reference execution cost. The method includes:

Obtain execution result information after the database execution engine completes any query step;

Determine the characteristics of the query step based on the basic information of the query step, and determine the actual execution cost of the query step information based on the execution result information;

According to the actual execution cost of the query step, the reference execution cost corresponding to the query step feature is updated; the correspondence between the query step feature and the reference execution cost is used to evaluate the cost of any query step when optimizing the query plan.

According to the second aspect of one or more embodiments of this specification, a method for determining an optimal query plan is proposed. The method is applied to a database optimization engine. The method includes:

Parse each query step in the query plan;

For any query step, determine alternative query steps for the query step;

For any alternative query step, determine the characteristics of the alternative query step, and query the reference execution cost corresponding to the alternative query step according to the above-mentioned correspondence;

Select the alternative query step with the smallest execution cost of each query step to obtain the optimal query plan.

According to the third aspect of one or more embodiments of this specification, a model construction method for predicting reference execution cost stability is proposed, including:

According to the preset time period T1, obtain a data sample set of query steps completed during the historical period of time T1. The data sample of any query step includes: the basic information of the query step and the actual execution cost corresponding to the query step;

For any query step data sample, convert the query step data sample into a training sample in the following way:

According to the basic information of the query step, determine the characteristics and completion timestamp of the query step, and obtain the reference execution cost when evaluating the query step by querying the corresponding relationship as above;

Based on the actual execution cost and the reference execution cost, the stability of the reference execution cost corresponding to the query step is calculated;

Using the characteristics of the query step as the feature value and the stability of the reference execution cost of the query step as the label, the training samples corresponding to the query step are obtained;

Perform the conversion step traversally on the data sample set to obtain a training sample set;

A model is obtained by training using the training sample set, and the model is used to predict the reference execution cost stability of any query step based on the characteristics of the query step.

According to the fourth aspect of one or more embodiments of this specification, a method for predicting reference execution cost stability is proposed, which is used to predict the reference execution cost stability corresponding to any query step, including:

receiving a query step and determining characteristics of said query step;

Input the characteristics of the query step into a model used to predict the stability of the reference execution cost; the model is constructed using the above construction method;

According to the output of the model, the reference execution cost stability corresponding to the query step is predicted.

According to the fifth aspect of one or more embodiments of this specification, a method for determining an optimal query plan is proposed. The method is applied to the optimizer execution engine. The method includes:

Parse each query step in the query plan;

For any query step, determine alternative query steps for the query step;

For any alternative query step, use the above method of predicting reference execution cost stability to predict the reference execution cost stability corresponding to the alternative query step; if the reference execution cost stability corresponding to the alternative query step is greater than the preset value, then determine the characteristics of the alternative query step, and query the reference execution cost corresponding to the alternative query step according to the above-mentioned correspondence relationship;

Select the alternative query step with the smallest execution cost of each query step to obtain the optimal query plan.

In one or more embodiments of this specification, since the basic information of the same or similar query steps has the same characteristics, the characteristics of the query steps are used as identifiers to construct a corresponding relationship between the characteristics of the query steps and the reference execution cost. Update the reference execution cost of query step characteristics: obtain the execution result information after the database execution engine completes any query step, determine the characteristics of the query step based on the basic information of the query step, and determine the query based on the execution result information The actual execution cost of the step information is then used as the reference execution cost corresponding to the query step feature. In this way, when the database optimizer engine needs to evaluate the execution cost of a certain query step, it can query the corresponding reference execution cost through the characteristics of the query step.

In one or more embodiments of this description, when the optimizer engine evaluates the execution cost for a query step, the reference execution cost is more specific and has more reference significance than the basic statistical information in the prior art, and the reference is updated. When executing the cost, it does not need to occupy the query resources of the database, reducing the query load.

Description of the drawings

Figure 1 is a schematic flowchart of a method for optimizing a query plan provided by an exemplary embodiment.

Figure 2 is a schematic flowchart of the relationship between each query step of a query plan provided by an exemplary embodiment.

FIG. 3 is a schematic flowchart of a method for determining a reference execution cost provided by an exemplary embodiment.

FIG. 4 is a schematic flowchart of a model construction method for predicting reference execution cost stability provided by an exemplary embodiment.

Figure 5 is a schematic flowchart of a prediction model training method provided by an exemplary embodiment.

FIG. 6 is a schematic flowchart of a method for predicting execution cost stability according to an exemplary embodiment.

FIG. 7 is a schematic flowchart of another reference execution cost determination method provided by an exemplary embodiment.

FIG. 8 is a schematic diagram of an embodiment framework of a method for determining a reference execution cost provided by an exemplary embodiment.

Figure 9 is a block diagram of a device for optimizing a query plan provided in an exemplary embodiment.

FIG. 10 is a block diagram of a device for determining a reference execution cost provided in an exemplary embodiment.

FIG. 11 is a block diagram of a model construction device for predicting reference execution cost stability provided by an exemplary embodiment.

FIG. 12 is a block diagram of a device for predicting stability of reference execution costs provided by an exemplary embodiment.

FIG. 13 is a block diagram of another device for determining a reference execution cost provided by an exemplary embodiment.

Figure 14 is a schematic structural diagram of a device provided by an exemplary embodiment.

Detailed ways

Exemplary embodiments will be described in detail herein, examples of which are illustrated in the accompanying drawings. When the following description refers to the drawings, the same numbers in different drawings refer to the same or similar elements unless otherwise indicated. The implementations described in the following exemplary embodiments do not represent all implementations consistent with one or more embodiments of this specification. Rather, they are merely examples of apparatus and methods consistent with some aspects of one or more embodiments of this specification as detailed in the appended claims.

It should be noted that in other embodiments, the steps of the corresponding method are not necessarily performed in the order shown and described in this specification. In some other embodiments, methods may include more or fewer steps than described in this specification. In addition, a single step described in this specification may be broken down into multiple steps for description in other embodiments; and multiple steps described in this specification may also be combined into a single step in other embodiments. describe.

Basic statistical information mainly refers to a type of information that describes the size, scale, database distribution, etc. of tables and indexes in the database, such as the number of rows and blocks in the table, the average size of each row, the number of rows in the index field, and the number of different values. size etc. Among them, these basic statistical information can only be obtained by traversing each table. During traversal, it is necessary to frequently call the I/O process to access these tables. However, in actual applications, when querying data, the relevant calculations are not the most time-consuming. Calling the I/O process to access each table for I/O is the more time-consuming part. It can be seen that the I/O process resources are relatively precious. Therefore, in order to ensure efficient external access to the database, resources will not be wasted for counting basic statistical information when the database is frequently accessed from the outside (when the database is busy).

On the one hand, the optimizer engine relies on basic statistical information to evaluate the cost of the query plan. The accuracy of the basic statistical information affects the accuracy of the optimization. However, when the database is busy, the basic statistical information is difficult to update, and it is difficult to ensure the accuracy of the basic statistical information. nature; on the other hand, the basic statistical information belongs to the macroscopic statistical information of the database, and the execution cost of the evaluation is also the macroscopic execution cost.

The inventor found in practice that in some scenarios where there are a large number of access requests, the query data and query types have temporal locality and spatial locality. Taking the analytical database application scenario as an example, you will face a large number of select statements (query statements) , In a period of time, certain libraries or tables of the database may be frequently accessed, and in another period of time, several other libraries or tables may be frequently accessed, and the queries may be the same or similar. The query step is composed of multiple query steps. For example, the first step is to connect table A and table B, and the second step is to select a row with a certain keyword that is not empty. When the executor engine executes the query plan, The query is completed step by step. Correspondingly, after each step is executed, the execution process information related to that step will be obtained, such as how many rows were read, how many rows were output, how long it took, etc. Therefore, if the database optimizer engine evaluates the execution cost of a certain query step, and the same or similar query steps have been completed in a recent period of time, then the optimizer engine will directly use the same or similar query steps in the recent period of time. It will obviously be more accurate to evaluate the execution cost of completing the query step based on the actual execution cost after the execution is completed.

Based on this, this specification provides a method for updating the reference execution cost of query step characteristics. The basic information of the same or similar query steps has the same characteristics. Therefore, using the characteristics of the query step as an identifier, the query step characteristics and the reference execution cost are constructed. corresponding relationship. And update the reference execution cost of the query step characteristics: obtain the execution result information after the database execution engine completes any query step, determine the characteristics of the query step based on the basic information of the query step, and determine the query step based on the execution result information. The actual execution cost of the query step information is then used as the reference execution cost corresponding to the query step characteristics. In this way, when it is necessary to evaluate the execution cost of a certain query step, the corresponding reference execution cost can be queried through the characteristics of the query step.

In one or more embodiments of this description, when the optimizer engine evaluates the execution cost for a query step, the reference execution cost is more specific and has more reference significance than the basic statistical information in the prior art, and the reference is updated. When executing the cost, it does not need to occupy the query resources of the database, reducing the query load.

Based on the above ideas, this manual provides methods for optimizing query plans, methods for determining optimal query plans, methods for building models for predicting the stability of reference execution costs, and methods for predicting stability of reference execution costs.

Among them, the method of optimizing the query plan is to use the corresponding relationship between the constructed query step characteristics and the reference execution cost. The corresponding relationship referenced by other methods to evaluate the cost of any query step is updated based on this method.

The reference execution cost determination method is to determine the reference execution cost of the query step based on the characteristics of the query step and query the corresponding relationship updated by the above update method.

In the model construction method, when constructing training samples, it is necessary to query the above-mentioned corresponding relationships based on the characteristics of the sample query step, and then use the constructed training sample set to train a model for predicting reference execution cost stability.

The method of predicting the stability of the reference execution cost uses the model constructed by the model construction method. Before determining the reference execution cost of the query step, you can first predict the stability of the current reference execution cost. The higher the stability, the higher the current reference execution cost. precise.

These methods are described in detail below.

First, the method of optimizing the query plan is explained, as shown in Figure 1, which is a schematic flow chart of the method of optimizing the query plan shown in this explanation.

Before executing this method, it is necessary to first use the characteristics of the query step as an identifier to construct a corresponding relationship between the characteristics of the query step and the reference execution cost. Among them, the correspondence between the query step characteristics and the reference execution cost is used to evaluate the cost of any query step when optimizing the query plan.

Each query step will have its basic information, including the query plan to which the query step belongs, the query type, the expression of the query step (including the operators, databases, tables, and columns involved), the submission time of the query step, etc. , for example, the query step is "table A inner join table B on A.key = B.key", the ID of the query plan to which this query step belongs is 1 (each query plan has its own unique identifier), and the query type is JOIN type , the operator involved is "=", involving table A and the key column of table A, table B and the key column of table B.

Among them, the same or similar query steps will have the same or similar query statements, that is, the databases, tables, and columns involved are the same, and the expressions involved are the same or similar. Therefore, in practical applications, the databases involved in each query step can be , tables, columns and expressions as characteristics of query steps, such as List<ExpressionType, List<Symbol>>.

The reference execution cost is used to evaluate the execution cost of executing the query step, such as how many rows need to be input, how many rows are output, how much memory is consumed, etc.

Step 102: Obtain the execution result information of any query step.

After the executor engine receives a query plan (also called an execution plan), it will execute the query plan according to the steps in the query plan. Each query step will have several corresponding query steps. Each query step They are all executed by operators in the executor engine (an operator is responsible for completing a basic data processing logic, and a group of operators completes a set of query steps for the data according to the query plan. In other words, a query step is at least The result is obtained after the execution of an operator). After each operator is executed, it will show how many rows it read during its execution, how many rows it output, and how much data it read in total (KB). Report the total size of data output, the total time spent, and the total memory usage. The execution engine generally encapsulates each query step belonging to a query plan as execution result information of a query plan. That is, the execution result information of a query plan includes execution result information of multiple query steps. Therefore, in practical applications, execution result information can be obtained from the execution engine in units of one query plan.

For example, a database has three tables, nation, region, and customer, and a certain query statement is:

SELECT count(*)

FROM

nation, region, customer

WHERE

c_nationkey = n_nationkey

AND n_regionkey = r_regionkey

AND r_name = 'ASIA';

After the optimization engine completes optimization, the query plan sent to the execution engine is:

1. Scan the table nation and find the columns n_nationkey and n_regionkey.

2. Scan the table region, find the column r_regionkey, and filter out the r_name = 'ASIA' row.

3. Innerjoin the tables obtained in the first and second steps with c_nationkey = n_nationkey.

4. Scan the customer table and find column c_nationkey.

5. Perform inner join (innerjoin) on the tables obtained in steps 4 and 5 with n_regionkey = r_regionkey.

6. Calculate how many rows the table obtained in step 5 has.

As shown in Figure 2, it is the execution relationship of each query step executed by the above query plan. For example, steps 1 and 2 can be performed at the same time and handed over to two operators to complete the results. Then, based on the results obtained in steps 1 and 2, an inner join is performed. The inner join step is completed by another operator, and so on. .

In actual applications, the query plan completed by the database executor engine may be of a large order of magnitude. Therefore, in one or more embodiments of this description, acquisition conditions can be set, for example, a sampling ratio is set. Every time the database execution engine completes 5 query plan, obtain the execution result information of one of the query plans; or set the acquisition period, every 1ms, obtain the execution result information of a query plan, etc.; or receive the specified command, start to obtain the execution result information of each query plan.

Step 104: Determine the characteristics and actual execution cost of the query step.

As mentioned above, each query plan will have several query steps. After each query step is executed, an execution result information will be obtained including the execution process of the query step, including how many rows (rows) were read during execution, and the output How many rows have been read, the total size of data (KB) read, the total size of data output, the total time taken, the total memory usage, etc.

The execution process of the query step can reflect the cost of executing the query step. Therefore, by obtaining the execution result information of the query step, one or more execution process data are selected as the actual execution cost of executing the query step.

Step 106: Update the reference execution cost corresponding to the query step feature.

Among them, the reference execution cost corresponding to the query step feature is updated according to the actual execution cost determined in step 104.

In practical applications, you can only update the corresponding relationship between the query step characteristics and the reference execution cost. The following table shows the corresponding relationship before update:

After receiving a query step whose feature is feature A, the actual execution cost of the query step is cost A2, then after the update, the corresponding relationship becomes:

You can also construct the corresponding relationship between the actual execution cost and the corresponding relationship between the timestamp and the query step characteristics. At this time, the actual execution cost of each time the query step characteristics are obtained can be saved sequentially in the chronological order of the timestamp (i.e. , also update the actual execution cost and the corresponding relationship between the timestamp and the characteristics of the query step), and use the latest corresponding actual execution cost as the reference actual execution cost of the query step, as shown in the following table, which is the corresponding relationship before update:

After receiving a query step whose characteristic is characteristic A, the actual execution cost of the query step is the actual cost A3 and the timestamp is TimeA3, and then the actual execution cost and the completion timestamp are added to the query step characteristic. Corresponding identification, then after the update, the corresponding relationship becomes:

Among them, the value of the reference execution cost corresponding to the feature of each query step is the value of the actual execution cost corresponding to the latest timestamp. As shown in Table 3, the actual execution cost corresponding to the latest timestamp of feature A is A2. Therefore, feature A The corresponding reference execution cost is the reference cost A2 (that is, the reference cost A2 = the actual cost A2). The actual execution cost corresponding to the latest timestamp of feature B is, 2. Therefore, the reference execution cost corresponding to feature B is the reference cost. B2, as shown in Table 4, the actual reference cost corresponding to the latest timestamp of feature A is reference cost A3. Therefore, the reference execution cost corresponding to feature A is reference cost A3.

In practical applications, if the actual execution cost and timestamp after the execution of each query step are saved without restrictions, the amount of data will be quite large if accumulated over a long period of time. Therefore, in one or more embodiments of this specification, it is possible to Set the saving conditions. For example, if the actual execution cost corresponding to any feature exceeds 20 items, delete the first 10 items before saving; or if the time stamp is more than 4 hours from the current timestamp, delete the corresponding actual execution cost and the corresponding timestamp.

What this description shows is that the query step characteristics, the reference execution cost, the actual execution cost, and the timestamp are stored in a table. In fact, a correspondence table can be established between the query step characteristics and the reference execution cost, and the query step characteristics and the reference execution cost can be established in a table. A corresponding relationship table is established between the actual execution cost and timestamp.

In actual applications, in some cases, the query step characteristics corresponding to a certain query step are not found in the corresponding relationship (for example, when building the corresponding relationship, there are no query step characteristics, or similar queries have not been executed before. Step), at this time, you can add the identification of the query step characteristics in the corresponding relationship, and use the actual execution cost corresponding to the query step as the reference execution cost.

In practical applications, the corresponding relationship can be stored in the form of a table or saved as an index, as long as the corresponding reference execution cost can be found according to the query steps. When storing, it can be stored in the system table of the database like basic statistical information, or it can be stored in the memory, or it can also be stored in the hard disk, etc.

The above is an explanation of the method of optimizing the query plan. It constructs and updates the corresponding relationship between the query step characteristics and the reference execution cost. Compared with the basic statistical information, the reference meaning is more specific. For example, when performing table connection, table A has 20 rows. Table B has 10 rows, so after performing the inner join, there will be at most 10 rows. When the optimizer engine performs evaluation, it will only evaluate it to 10 rows or a rough number. The reference execution cost is obtained by using the actual execution result (that is, the actual execution cost) after the inner connection between table A and table B. For example, after the actual inner connection between table A and table B, there are 4 rows, then table A will be evaluated later. After calculating the execution cost of the join within table B, it can be determined that 4 rows will be output after the join between table A and table B. Moreover, since what is obtained is the execution result information after the executor engine executes any query step (that is, what is obtained is the execution result information of the query step that the execution engine originally needs to execute), it will not occupy the query resources of the database. It will not cause query load to the database (no queries unrelated to the business will be performed).

The method for determining the optimal query plan is described below. This method uses the correspondence between the query step characteristics updated by the above update method and the reference execution cost. The above update method improves the accuracy of the reference execution cost of query step features (compared to basic statistical information, the reference meaning is more specific and the update speed is faster, so it is more accurate). Therefore, whether technical personnel obtain the reference execution cost of each query step Whether the cost determines the database running status or the optimizer engine uses the reference execution cost to generate the optimal query plan, the reference significance of the reference execution cost is higher than the basic statistical information.

As shown in Figure 3, it is a schematic flow chart of a method for determining the optimal query plan shown in this specification. This method is applied to the optimizer engine and includes the following steps:

Step 302: Analyze each query step in the query plan.

After the optimizer engine receives a query plan, it will parse out each query step in the query plan, shaped like a tree.

Step 304: For any query step, determine alternative query steps for the query step.

Some query steps have alternative query steps. As mentioned above, when two tables are connected, they can be left joined or right joined, etc.

Step 306: For any alternative query step, determine the characteristics of the alternative query step, and query the reference execution cost corresponding to the alternative query step according to the above-mentioned correspondence relationship.

When the optimizer engine optimizes each query step, it is based on cost. Therefore, the cost of executing each query step needs to be evaluated to select the alternative query step with the smallest execution cost.

In this specification, the optimizer engine optimizes and evaluates the cost of executing each query step by querying the correspondence between the above query step characteristics and the reference execution cost to obtain the execution cost of each query step.

Among them, the corresponding relationship between the query step and the reference execution cost is the corresponding relationship obtained by the above update method.

Step 308: Select the alternative query step with the smallest execution cost of each query step to determine the optimal query plan.

In practical applications, when it is necessary to determine the reference execution cost of a certain query step, it will be found that the query step characteristics of the query step do not exist in the corresponding relationship (that is, no similar query step has been executed before). In this case, you can refer to Basic statistical information is used as the basis for evaluation. In this way, after the query step is executed, the actual execution cost can be obtained, which can be used as the reference execution cost for subsequent identical or similar query steps.

Using the above optimized query plan method and determination method can greatly improve the accuracy of the optimizer engine in evaluating each query step. However, in some cases, the data changes rapidly, making the actual execution cost significantly different. , Based on this, this specification provides a model construction method for predicting the stability of the reference execution cost.

The following is a detailed description of the model construction method for predicting the stability of the reference execution cost. As shown in Figure 4, it is a schematic flow chart of the model construction method for predicting the stability of the reference execution cost shown in this specification, including the following step:

Step 402: Obtain the query step data sample.

When obtaining data samples, it is necessary to obtain a data sample set of query steps completed within the historical period of time T1 based on the preset time length T1. Among them, the data sample of any query step includes: the basic information of the query step, the corresponding query step actual execution cost.

Since data changes are localized in time and space, the data samples selected for the query step to build the model need to be samples within a recent period of time, such as within the past 30ms. For query data samples long ago, it is of little significance to model construction. For example, there are a large number of data modification operations during 6:00-6:10, making the reference execution cost of some query steps very unstable. If 6 is used :00-6:10 During this period, query step data samples are used to build a model. When predicting the reference execution cost of some query steps, unstable prediction results will be obtained. If there are not many data changes between 16:00 and 16:10, which means that the reference execution cost of each query step is stable, if you use the model built from 6:00 to 6:10, you will get wrong prediction results. Therefore, when building the model, it is necessary to obtain a collection of data samples of query steps completed within the historical period of time T1.

Step 404: Construct a training sample set.

Among them, for any query step data sample, the query step data sample is converted into a training sample in the following way:

According to the basic information of the query step, determine the characteristics and completion timestamp of the query step, and obtain the reference execution cost when evaluating the query step by querying the corresponding relationships shown in Table 3 and Table 4;

Based on the actual execution cost and the reference execution cost, the stability of the reference execution cost corresponding to the query step is calculated;

Using the characteristics of the query step as the feature value and the stability of the reference execution cost of the query step as the label, the training samples corresponding to the query step are obtained;

The conversion step is traversed and performed on the data sample set to obtain a training sample set.

Among them, the reference execution cost is updated with time. Therefore, when determining the reference execution cost of each query step, it is necessary to determine the time of the query step. The time is different, and the reference execution cost when evaluating the execution cost is different, as shown in Table 4. As shown, assuming that the completion timestamp of a certain query step is Time5, then when evaluating the execution cost of this query step, the data set 2 corresponding to Time2 is used.

As shown in Figure 5, it is a flow chart of a prediction model training method shown in this specification. The data sample of any query step includes the basic information of the query step and the actual execution cost after the step is completed. Through the query step Based on the basic information, the characteristics of the query step are extracted, and the actual execution cost of actually executing the query step is determined through the execution result information of the query step.

Taking the execution cost as the number of input rows and the number of output rows as an example, let StatsDiff = (PlanNodeStates-OperatorStates)/Max (PlanNodeStates, OperatorStates), where StatsDiff is used to represent the difference between the actual execution cost and the reference execution cost, and PlanNodeStates represents the acquisition The reference execution cost of , if 1-StatsDiff is used as the label, then the closer the label is to 1, the more stable the reference execution cost is, and the closer it is to 0, the more unstable the reference execution cost is.

In addition, in one or more embodiments of this specification, when each query step is completed, both the reference execution cost of the query step during evaluation and the actual execution cost of the query step can be saved as a query step data sample.

Step 406: Train a model for predicting the stability of the reference execution cost of any query step.

Use the constructed training sample set to train a model for predicting the stability of the reference execution cost of any query step. Among them, the above-mentioned models for predicting reference execution cost stability can be constructed based on different algorithms. For example, the model can be constructed based on support vector machine, the model can be constructed based on neural network, the model can be constructed based on random forest, etc. It is sufficient that the model can achieve the purpose shown in this manual.

Take random forest as an example:

Assume that the number of samples in the constructed training sample set is N. For any tree, n (n < N) training samples are randomly selected from the training sample set with replacement as the training sample set of the tree.

If the feature dimension of each sample is M, specify a constant m<<M, and randomly select m feature subsets from the M feature monkeys. Each time the tree is split, the optimal one is selected from these m feature summaries. .

Each tree is grown to its maximum extent without any pruning process.

In practical applications, the model can be constructed under preset conditions. For example, the preset condition can be that the model is constructed at a time point in a preset period, and a prediction model is constructed once every time period passes. It can also be the construction time. When it is determined that the model has not been updated for a long time, in order to ensure the accuracy of model prediction, the model needs to be updated, that is, rebuilt.

The above is an explanation of the model construction method for predicting the stability of the reference execution cost. Based on the above model construction method, this explanation also provides a corresponding method of using the model, that is, a method of predicting the stability of the reference execution cost. Next, the method of predicting the stability of the reference execution cost is explained in detail.

In actual applications, the data of some tables and columns in the database will change drastically, and the reference execution cost of query steps involving these tables or columns will also change greatly. Due to the rapid and drastic changes in data, the actual execution cost after this execution is also far different from the actual execution cost after the last execution.

As shown in Figure 6, a schematic flowchart of the method for predicting reference execution cost stability shown in this description includes the following steps:

Step 602: Determine the characteristics of the query step.

Among them, the received query steps can be sent by the optimizer engine, or they can be input by testers for testing, depending on the actual application scenario.

The characteristics of the query step are determined based on the basic information of the query step, which is the same as the method of determining the characteristics of the query step mentioned above.

Step 604: Input the features of the query step into the model.

The prediction model uses the above-mentioned model construction method for predicting reference execution cost stability, which will not be described in detail here.

Step 606: According to the output of the model, predict the stability of the reference execution cost of the query step.

Since the data samples used to build the prediction model are query steps within a preset historical period, if the database data is stable in the recent historical period, then the reference execution cost of each query step will also be stable. Use the model to predict the reference in the near future. The execution cost is also stable. If the database data in the recent historical period is unstable, then the reference execution cost of each query step is also unstable, and the reference execution cost predicted by the model in the near future is also unstable.

In one or more embodiments of this specification, when using a prediction model for prediction, the construction time of the prediction model can be determined. If the prediction model has been constructed for a long time, it means that the model has not been updated for a long time. At this time, the model is used The prediction refers to the execution cost stability, and the results obtained do not have great reference significance. Therefore, when it is determined that the construction time of the prediction model exceeds the preset time, the prediction model can be updated.

The above is an explanation of the method of predicting the stability of the reference execution cost. In practical applications, before using the above-mentioned method of determining the reference execution cost to determine the reference execution cost, the above-mentioned method of predicting the stability of the reference execution cost can also be used to predict the reference execution cost. Whether it is stable or not, based on this, this specification also provides a method for determining the execution cost by reference, which will be described in detail next.

As shown in Figure 7, it is another method of determining the optimal query plan shown in this specification. This method is applied to the optimizer execution engine and includes the following steps:

Step 702: Analyze each query step in the query plan.

Step 704: For any query step, determine alternative query steps for the query step.

Step 706: For any alternative query step, use the method of predicting reference execution cost stability as described above to predict the reference execution cost stability corresponding to the alternative query step; if the reference execution cost corresponding to the alternative query step is stable If the degree is greater than the preset value, then the characteristics of the alternative query step are determined, and based on the above-mentioned corresponding relationship, the reference execution cost corresponding to the alternative query step is queried.

Step 708: Select the alternative query step with the smallest execution cost of each query step to determine the optimal query plan.

If the stability is greater than the preset value, it means that the reference execution cost corresponding to the query step feature is relatively stable in the recent historical period. Therefore, the reference execution cost saved at this time has high credibility (high accuracy) and can be directly queried and obtained. .

Wherein, obtaining the reference execution cost of the query step is to use the reference execution cost determination method as mentioned above.

If the stability is less than the predicted value, it means that the reference execution cost corresponding to the query step feature is relatively unstable in the recent historical period. Therefore, the credibility of the saved reference execution cost at this time is not high (low accuracy) and the reference significance is not high. big.

In actual applications, if the stability is less than the preset value, the model used to predict the stability of the reference execution cost is updated.

In one or more embodiments of this specification, the prediction results of the model used to predict the stability of the reference execution cost are directly related to the sample data used to build the model. If the training samples used to build the prediction model are unstable, then the obtained prediction The results are also unstable. If the training samples used to build the prediction model are stable, then the prediction results obtained will also be stable. Therefore, when the stability is determined to be less than The reference execution cost of this query step is also stable.

In addition, in actual applications, the order in which the optimizer engine determines the reference execution cost and predicts the stability of the reference execution cost for any alternative query step may not be certain. It can first obtain the reference execution cost, and then predict the reference execution cost. Stability, the reference execution cost determined by the optimizer engine whether it needs to be obtained.

FIG. 8 is a schematic diagram of an embodiment of a reference execution cost determination method shown in this specification, which will be described in detail next.

The data collection module is responsible for collecting data in the database executor engine, including the execution result information of any query step, which can be organized in the form of [QueryId, TimeStamp, PlanInfo, ExecutionInfo], where QueryId is the unique identifier of each query plan, and TimeStamp is the timestamp of query submission, PlanInfo is the plan information of the query plan, including multiple PlanNodeInfo (query step information), and ExecutionInfo is the execution result information, including the execution result information after each query step is completed.

Among them, each PlanNodeInfo has the unique identifier PlanNodeId of the query step, the type of the query step PlanNodeType, the reference execution cost of the query step PlanNodeStats, the expression list of the query step List<ExpressionType, List<Symbol>>. Taking the reference execution cost as the number of input rows and the number of output rows as an example, PlanNodeStats=[InputRowCount, OutputRowCount], where the number of input data rows InputRowCount and the number of output data rows OutputRowCount are taken as an example.

The above Symbol can include SchemaId, TableId, and ColumnId, which are the unique identifiers of the database, table, and column respectively. ExpressionType is the various expression operators supported by the database.

ExecutionInfo includes the actual execution cost after each query step is completed, corresponding to the reference execution cost, the actual execution cost OperatorStats=[InputRowCount, OutputRowCount].

The data storage module stores the data collected by the data collection module, using the PlanNodeId of each query step as the unique identifier, and uses [QueryId, TimeStamp, PlanNodeType, List＜ExpressionType, List＜Symbol＞＞, PlanNodeStats, OperatorStats] as structure for storage.

The intelligent decision-making module can control the data collection of the data collection module (whether to collect and the sampling ratio), and can also use the data stored in the data storage module for model training (where the model needs to be updated all the time to ensure the accuracy of prediction), database optimization When the server engine queries the intelligent decision-making module for the reference execution cost of any query step, on the one hand, the intelligent decision-making module finds the reference execution cost corresponding to the query step from the data storage module, and on the other hand, the intelligent decision-making module uses the model to predict the cost of the query step. Refer to whether the execution cost is stable.

This specification also provides devices, electronic equipment, and computer-readable storage media corresponding to the above methods. The following is a description of the devices, equipment, and storage media corresponding to the above methods.

This specification provides an optimized query plan device. As shown in Figure 9, the characteristics of the query steps are used as identifiers to construct a corresponding relationship between the characteristics of the query steps and the reference execution cost. The method includes:

Execution result information acquisition model 902 is used to obtain execution result information after the database execution engine completes any query step;

The characteristic and actual execution cost determination module 904 is used to determine the characteristics of the query step based on the basic information of the query step, and determine the actual execution cost of the query step information based on the execution result information;

The update module 906 is used to update the reference execution cost corresponding to the query step characteristics according to the actual execution cost of the query step; the correspondence between the query step characteristics and the reference execution cost is used to evaluate any query step when optimizing the query plan. price.

The execution cost is execution process information that can be obtained from execution result information.

The corresponding relationship also includes: the actual execution cost and the corresponding relationship between the timestamp and the query step characteristics. At this time, the optimized query plan device may also include a completion timestamp determining device (not shown) for determining according to the query. The basic information of the step determines the completion timestamp of the query step; at this time, the update module 906 is used to add the actual execution cost and the completion timestamp to the identifier corresponding to the query step feature, and add the query step The value of the reference execution cost corresponding to the step feature is updated to the value of the actual execution cost.

This specification provides a reference execution cost determination device, as shown in Figure 10, used to determine the reference execution cost of any query step, including:

The parsing module 1002 is used to parse each query step in the query plan;

The determination module 1004 determines, for any query step, alternative query steps for the query step;

The reference execution cost determination module 1006 determines the characteristics of any alternative query step for any alternative query step, and queries the reference execution cost corresponding to the alternative query step according to the corresponding relationship as claimed in claim 1;

The optimal query plan determination module 1008 selects the alternative query step with the smallest execution cost of each query step to obtain the optimal query plan.

This specification provides a model construction device for predicting the stability of reference execution costs, as shown in Figure 11, including:

The data sample acquisition module 1102 is used to obtain a data sample set of query steps completed within the historical period of time T1 according to the preset time length T1. The data sample of any query step includes: the basic information of the query step, the corresponding query step actual execution cost;

The training sample determination module 1104 includes three sub-units, which are used to convert any query step data sample into a training sample in the following manner:

The query subunit 1114 is used to determine the characteristics and completion timestamp of the query step based on the basic information of the query step, and obtain the reference execution cost when evaluating the query step by querying the above-mentioned correspondence;

The stability calculation subunit 1124 is used to calculate the stability of the reference execution cost corresponding to the query step based on the actual execution cost and the reference execution cost;

The training sample determination subunit 1134 is used to obtain the training sample corresponding to the query step using the feature of the query step as the feature value and the stability of the reference execution cost of the query step as the label;

The training sample set determination module 1106 is used to traverse and perform the conversion step on the data sample set to obtain a training sample set;

The model training module 1108 is used to train a model using the training sample set, and the model is used to predict the stability of the reference execution cost of any query step based on the characteristics of the query step.

This description provides a device for predicting the stability of the reference execution cost, as shown in Figure 12. It is used to predict the stability of the reference execution cost corresponding to any query step, including:

Feature determination module 1202, used to receive the query step and determine the characteristics of the query step;

The input module 1204 is used to input the characteristics of the query step into a model used to predict the stability of the reference execution cost; the model is constructed using the above-mentioned model construction device;

The prediction result acquisition module 1206 is used to predict the stability of the reference execution cost corresponding to the query step according to the output of the model.

This specification also provides another reference execution cost determination device, as shown in Figure 13, including:

The parsing module 1302 is used to parse each query step in the query plan;

The determination module 1304 is used to determine, for any query step, alternative query steps for the query step;

The reference execution cost determination module 1306 is used for predicting the reference execution cost stability corresponding to the alternative query step using the above-mentioned device for predicting the stability of the reference execution cost for any alternative query step; if the alternative query step If the stability of the corresponding reference execution cost is greater than the preset value, then the characteristics of the alternative query step are determined, and based on the above-mentioned corresponding relationship, the reference execution cost corresponding to the alternative query step is queried;

The optimal query plan determination module 1308 is used to select alternative query steps with the smallest execution cost of each query step to obtain the optimal query plan.

The device may further include an update module (not shown), configured to update a model used to predict the stability of the reference execution cost if the stability of the reference execution cost corresponding to the query step is less than a preset value.

The devices, modules or units described in the above embodiments may be implemented by computer chips or entities, or by products with certain functions. A typical implementation device is a computer, which may be in the form of a personal computer, a laptop, a cellular phone, a camera phone, a smart phone, a personal digital assistant, a media player, a navigation device, an email transceiver, or a game controller. desktop, tablet, wearable device, or a combination of any of these devices.

This instruction manual provides an electronic device, including:

processor;

Memory used to store instructions executable by the processor;

Wherein, the processor implements a method of optimizing the query plan by running the executable instructions, using the characteristics of the query step as an identifier, and constructing a corresponding relationship between the characteristics of the query step and the reference execution cost. The method at least includes:

Obtain execution result information after the database execution engine completes any query step;

Determine the characteristics of the query step based on the basic information of the query step, and determine the actual execution cost of the query step information based on the execution result information;

According to the actual execution cost of the query step, the reference execution cost corresponding to the query step feature is updated; the correspondence between the query step feature and the reference execution cost is used to evaluate the cost of any query step when optimizing the query plan.

This instruction manual also provides an electronic device, including:

processor;

Memory used to store instructions executable by the processor;

Wherein, the processor implements a method for determining the optimal query plan by running the executable instructions, and the method at least includes:

Parse each query step in the query plan;

For any query step, determine alternative query steps for the query step;

For any alternative query step, determine the characteristics of the alternative query step, and query the reference execution cost corresponding to the alternative query step according to the corresponding relationship between the query step characteristics and the reference execution cost;

Select the alternative query step with the smallest execution cost of each query step to obtain the optimal query plan.

This instruction manual also provides an electronic device, including:

processor;

Memory used to store instructions executable by the processor;

Wherein, the processor executes the executable instructions to implement a model construction method for predicting reference execution cost stability, and the method at least includes:

According to the preset time period T1, obtain a data sample set of query steps completed during the historical period of time T1. The data sample of any query step includes: the basic information of the query step and the actual execution cost corresponding to the query step;

For any query step data sample, convert the query step data sample into a training sample in the following way:

According to the basic information of the query step, determine the characteristics and completion timestamp of the query step, and obtain the reference execution cost when evaluating the query step by querying the correspondence between the query step characteristics and the reference execution cost;

Based on the actual execution cost and the reference execution cost, the stability of the reference execution cost corresponding to the query step is calculated;

Using the characteristics of the query step as the feature value and the stability of the reference execution cost of the query step as the label, the training samples corresponding to the query step are obtained;

Perform the conversion step traversally on the data sample set to obtain a training sample set;

A model is obtained by training using the training sample set, and the model is used to predict the reference execution cost stability of any query step based on the characteristics of the query step.

This instruction manual also provides an electronic device, including:

processor;

Memory used to store instructions executable by the processor;

Wherein, the processor implements a method of predicting reference execution cost stability by running the executable instructions, and is used to predict the reference execution cost stability corresponding to any query step. The method at least includes:

receiving a query step and determining characteristics of said query step;

Input the characteristics of the query step into a model used to predict the stability of the reference execution cost; the model is constructed using the above construction method;

According to the output of the model, the reference execution cost stability corresponding to the query step is predicted.

This instruction manual also provides an electronic device, including:

processor;

Memory used to store instructions executable by the processor;

Wherein, the processor implements a method for determining the optimal query plan by running the executable instructions, and the method at least includes:

Parse each query step in the query plan;

For any query step, determine alternative query steps for the query step;

For any alternative query step, predict the stability of the reference execution cost corresponding to the alternative query step; if the stability of the reference execution cost corresponding to the alternative query step is greater than the preset value, determine the characteristics of the alternative query step, According to the corresponding relationship between the query step characteristics and the reference execution cost, query the reference execution cost corresponding to the alternative query step;

Select the alternative query step with the smallest execution cost of each query step to obtain the optimal query plan.

Figure 14 is a schematic structural diagram of a device provided by an exemplary embodiment. Please refer to Figure 14. At the hardware level, the device includes a processor 1402, an internal bus 1404, a network interface 1406, a memory 1408, and a non-volatile memory 1410. Of course, it may also include other hardware required by the business. One or more embodiments of this specification may be implemented based on software. For example, the processor 1402 reads the corresponding computer program from the non-volatile memory 1410 into the memory 1408 and then runs it. Of course, in addition to software implementation, one or more embodiments of this specification do not exclude other implementations, such as logic devices or a combination of software and hardware, etc. That is to say, the execution subject of the following processing flow is not limited to each A logic unit can also be a hardware or logic device.

Among them, the devices shown in Figures 9 to 13 can be applied to the equipment shown in Figure 14 to implement the technical solution of this specification.

In a typical configuration, a computer includes one or more processors (CPUs), input/output interfaces, network interfaces, and memory.

Memory may include non-permanent storage in computer-readable media, random access memory (RAM), and/or non-volatile memory in the form of read-only memory (ROM) or flash memory (flash RAM). Memory is an example of computer-readable media.

This specification provides a computer-readable storage medium on which computer instructions are stored. When the instructions are executed by a processor, a method for optimizing a query plan is implemented. The characteristics of the query steps are used as identifiers to construct the characteristics of the query steps and the reference execution cost. corresponding relationship, the method includes:

Obtain execution result information after the database execution engine completes any query step;

Determine the characteristics of the query step based on the basic information of the query step, and determine the actual execution cost of the query step information based on the execution result information;

According to the actual execution cost of the query step, the reference execution cost corresponding to the query step feature is updated; the correspondence between the query step feature and the reference execution cost is used to evaluate the cost of any query step when optimizing the query plan.

This specification also provides a computer-readable storage medium on which computer instructions are stored. When the instructions are executed by the processor, a method for determining the optimal query plan is implemented. The method at least includes:

Parse each query step in the query plan;

For any query step, determine alternative query steps for the query step;

For any alternative query step, determine the characteristics of the alternative query step, and query the reference execution cost corresponding to the alternative query step according to the corresponding relationship between the query step characteristics and the reference execution cost;

Select the alternative query step with the smallest execution cost of each query step to obtain the optimal query plan.

This specification also provides a computer-readable storage medium on which computer instructions are stored. When the instructions are executed by a processor, a model construction method for predicting reference execution cost stability is implemented. The method at least includes:

According to the preset time period T1, obtain a data sample set of query steps completed during the historical period of time T1. The data sample of any query step includes: the basic information of the query step and the actual execution cost corresponding to the query step;

For any query step data sample, convert the query step data sample into a training sample in the following way:

According to the basic information of the query step, determine the characteristics and completion timestamp of the query step, and obtain the reference execution cost when evaluating the query step by querying the correspondence between the query step characteristics and the reference execution cost;

Based on the actual execution cost and the reference execution cost, the stability of the reference execution cost corresponding to the query step is calculated;

Using the characteristics of the query step as the feature value and the stability of the reference execution cost of the query step as the label, the training samples corresponding to the query step are obtained;

Perform the conversion step traversally on the data sample set to obtain a training sample set;

A model is obtained by training using the training sample set, and the model is used to predict the reference execution cost stability of any query step based on the characteristics of the query step.

This specification also provides a computer-readable storage medium on which computer instructions are stored. When the instructions are executed by the processor, a method for predicting the stability of the reference execution cost is used to predict the reference execution cost corresponding to any query step. Stability, the method at least includes:

receiving a query step and determining characteristics of said query step;

Input the characteristics of the query step into a model used to predict the stability of the reference execution cost; the model is constructed using the above construction method;

According to the output of the model, the reference execution cost stability corresponding to the query step is predicted.

This specification also provides a computer-readable storage medium on which computer instructions are stored. When the instructions are executed by the processor, the instructions can be executed to achieve a method for determining the optimal query plan. The method at least includes:

Parse each query step in the query plan;

For any query step, determine alternative query steps for the query step;

For any alternative query step, predict the stability of the reference execution cost corresponding to the alternative query step; if the stability of the reference execution cost corresponding to the alternative query step is greater than the preset value, determine the characteristics of the alternative query step, According to the corresponding relationship between the query step characteristics and the reference execution cost, query the reference execution cost corresponding to the alternative query step;

Select the alternative query step with the smallest execution cost of each query step to obtain the optimal query plan.

Computer-readable media includes both persistent and non-volatile, removable and non-removable media that can be implemented by any method or technology for storage of information. Information may be computer-readable instructions, data structures, modules of programs, or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), and read-only memory. (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technology, compact disc read-only memory (CD-ROM), digital versatile disc (DVD) or other optical storage, Magnetic tape cartridges, magnetic disk storage, quantum memory, graphene-based storage media or other magnetic storage devices, or any other non-transmission medium, can be used to store information that can be accessed by computing devices. As defined in this article, computer-readable media does not include transitory media, such as modulated data signals and carrier waves.

In addition, this description also provides a computer program, which, when executed by one or more processors, is used to implement any of the above methods.

It should also be noted that the terms "comprises," "comprises," or any other variation thereof are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that includes a list of elements not only includes those elements, but also includes Other elements are not expressly listed or are inherent to the process, method, article or equipment. Without further limitation, an element defined by the statement "comprises a..." does not exclude the presence of additional identical elements in a process, method, article, or device that includes the stated element.

The foregoing describes specific embodiments of this specification. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps recited in the claims can be performed in a different order than in the embodiments and still achieve desired results. Additionally, the processes depicted in the figures do not necessarily require the specific order shown, or sequential order, to achieve desirable results. Multitasking and parallel processing are also possible or may be advantageous in certain implementations.

The terminology used in one or more embodiments of this specification is for the purpose of describing particular embodiments only and is not intended to limit the one or more embodiments of this specification. As used in one or more embodiments of this specification and the appended claims, the singular forms "a," "the" and "the" are intended to include the plural forms as well, unless the context clearly dictates otherwise. It will also be understood that the term "and/or" as used herein refers to and includes any and all possible combinations of one or more of the associated listed items.

It should be understood that although one or more embodiments of this specification may use the terms first, second, third, etc. to describe various information, the information should not be limited to these terms. These terms are only used to distinguish information of the same type from each other. For example, without departing from the scope of one or more embodiments of this specification, the first information may also be called second information, and similarly, the second information may also be called first information. Depending on the context, the word "if" as used herein may be interpreted as "when" or "when" or "in response to determining."

The above are only preferred embodiments of one or more embodiments of this specification, and are not intended to limit one or more embodiments of this specification. Within the spirit and principles of one or more embodiments of this specification, Any modifications, equivalent substitutions, improvements, etc. shall be included in the scope of protection of one or more embodiments of this specification.

Claims (11)

1. A model building method for predicting reference execution cost stability, comprising:

according to a preset time length T1, acquiring a query step data sample set which is completed in the historical time period of the time length T1, wherein any query step data sample comprises: basic information of the query step and actual execution cost corresponding to the query step;

for any query step data sample, the query step data sample is converted into a training sample by:

determining the characteristics and the completion time stamp of the query step according to the basic information of the query step, and obtaining the reference execution cost when the query step is evaluated through the corresponding relation between the characteristics of the query step and the reference execution cost;

according to the actual execution cost and the reference execution cost, calculating to obtain the stability of the reference execution cost corresponding to the query step;

Taking the characteristic of the query step as a characteristic value and the stability of the reference execution cost of the query step as a label to obtain a training sample corresponding to the query step;

performing the conversion step on the data sample set traversal to obtain a training sample set;

training with the training sample set to obtain a model, wherein the model is used for: predicting the stability of the reference execution cost of any query step according to the characteristics of the query step;

the correspondence is updated based on a method for optimizing a query plan, and the method for optimizing the query plan comprises the following steps:

acquiring execution result information after the database execution engine completes any query step;

determining the characteristics of the query step according to the basic information of the query step, and determining the actual execution cost of the query step information according to the execution result information;

and updating the reference execution cost corresponding to the characteristic of the query step according to the actual execution cost of the query step.

2. The model construction method according to claim 1, the correspondence further comprising: the method for optimizing the query plan further comprises the following steps of:

Determining a completion time stamp of the query step according to the basic information of the query step;

updating the reference execution cost corresponding to the query step feature according to the actual execution cost of the query step, including:

and adding the actual execution cost and the completion time stamp to an identifier corresponding to the query step feature, and updating the value of the reference execution cost corresponding to the query step feature to the value of the actual execution cost.

3. A method of determining an optimal query plan, the method being applied to a database optimization engine, the method comprising:

analyzing each query step in the query plan;

determining an alternative query step to the query step for any query step;

for any alternative query step, determining the characteristics of the alternative query step, and querying the reference execution cost corresponding to the alternative query step according to the corresponding relation as set forth in claim 1;

and selecting the alternative query step with the minimum execution cost of each query step, and determining the optimal query plan.

4. A method for predicting reference execution cost stability corresponding to any query step, comprising:

Receiving a query step and determining characteristics of the query step;

inputting the characteristics of the query step into a model for predicting the stability of the execution cost of the reference; the model is constructed by using the construction method according to claim 1;

and predicting the stability of the reference execution cost corresponding to the query step according to the output of the model.

5. A method of determining an optimal query plan, the method being applied to an optimizer execution engine, the method comprising:

analyzing each query step in the query plan;

determining an alternative query step to the query step for any query step;

for any alternative query step, predicting the reference execution cost stability corresponding to the alternative query step by using the method as claimed in claim 4; if the stability of the reference execution cost corresponding to the alternative query step is greater than a preset value, determining the characteristics of the alternative query step, and querying the reference execution cost corresponding to the alternative query step according to the corresponding relation as set forth in claim 1;

and selecting the alternative query step with the minimum execution cost of each query step, and determining the optimal query plan.

6. The method of claim 5, further comprising:

And if the stability of the reference execution cost corresponding to the query step is smaller than a preset value, updating a model for predicting the stability of the reference execution cost.

7. A model construction apparatus for predicting reference execution cost stability, comprising:

the data sample acquisition module is configured to acquire a query step data sample set that is completed in a history period of the duration T1 according to a preset duration T1, where any query step data sample includes: basic information of the query step and actual execution cost corresponding to the query step;

the training sample determining module comprises three subunits, and is used for converting any query step data sample into a training sample by the following modes:

the query subunit is used for determining the characteristics and the completion time stamp of the query step according to the basic information of the query step, and obtaining the reference execution cost when the query step is evaluated through the corresponding relation between the characteristics of the query step and the reference execution cost;

the stability calculating subunit is used for calculating the stability of the reference execution cost corresponding to the query step according to the actual execution cost and the reference execution cost;

The training sample determining subunit is used for obtaining a training sample corresponding to the query step by taking the characteristic of the query step as a characteristic value and taking the reference execution cost stability of the query step as a label;

the training sample set determining module is used for performing the conversion step on the data sample set in a traversing way to obtain a training sample set;

the model training module is used for training to obtain a model by using the training sample set, and the model is used for: predicting the stability of the reference execution cost of any query step according to the characteristics of the query step;

the correspondence is updated based on a method for optimizing a query plan, and the method for optimizing the query plan comprises the following steps:

acquiring execution result information after the database execution engine completes any query step;

determining the characteristics of the query step according to the basic information of the query step, and determining the actual execution cost of the query step information according to the execution result information;

and updating the reference execution cost corresponding to the characteristic of the query step according to the actual execution cost of the query step.

8. A device for predicting reference execution cost stability, configured to predict reference execution cost stability corresponding to any query step, including:

The characteristic determining module is used for receiving the query step and determining the characteristics of the query step;

the input module is used for inputting the characteristics of the query step into a model for predicting the stability of the reference execution cost; the model is constructed by using the model construction method according to claim 1;

and the prediction result acquisition module is used for predicting the stability of the reference execution cost corresponding to the query step according to the output of the model.

9. A device for determining an optimal query plan, the device comprising:

the analysis module is used for analyzing each query step in the query plan;

the determining module is used for determining an alternative query step of any query step;

a reference execution cost determining module, configured to predict, for any alternative query step, a reference execution cost stability corresponding to the alternative query step by using the method as set forth in claim 4; if the stability of the reference execution cost corresponding to the alternative query step is greater than a preset value, determining the characteristics of the alternative query step, and querying the reference execution cost corresponding to the alternative query step according to the corresponding relation as set forth in claim 1;

And the optimal query plan determining module is used for selecting the alternative query step with the minimum execution cost of each query step and determining the optimal query plan.

10. An electronic device, comprising:

a processor;

a memory for storing processor-executable instructions;

wherein the processor is configured to implement the method of any of claims 1-6 by executing the executable instructions.

11. A computer readable storage medium having stored thereon computer instructions which, when executed by a processor, implement the steps of the method of any of claims 1-6.