Method, device and equipment for selecting query optimizer and readable storage medium

Abstract

The invention discloses a query optimizer selection method and device, equipment and a storage medium, and belongs to the technical field of data query. Each query optimizer constructs an expert network model, and the method comprises the following steps: inputting a to-be-optimized feature vector of a to-be-executed query statement into each expert network model to respectively obtain each first feature vector, and inputting the to-be-optimized feature vector into a preset ideal expert network model to obtain an ideal feature vector; based on each first feature vector and the ideal feature vector, generating a selected probability of each query optimizer; and taking the query optimizer with the maximum selected probability as a target optimizer of the to-be-executed query statement. Compared with a traditional method, estimation or prediction of vectorization representation of the execution plan is completed through internal interaction of the expert network model, the characteristics representing the execution plan can be rapidly obtained without interaction with a database, the operation efficiency is greatly improved, and the query optimizer selection speed is increased.

Description

Technical Field

[0001] The present application relates to the field of data query technology, and in particular to a method, device, equipment and readable storage medium for selecting a query optimizer. Background Art

[0002] At present, the commonly used selective query optimizer is the Bao optimizer. The core idea of ​​Bao is to use reinforcement learning to train an intelligent agent, which selects a database query optimizer for the current query and infers and selects an "optimal" execution plan with the help of an execution plan cost evaluation model. However, in the process of inferring and selecting the "optimal" execution plan, it is necessary to select a database query optimizer to generate an execution plan, and then evaluate the quality of the database query optimizer based on the generated execution plan. Among them, since the execution plan needs to be generated and the generation of the execution plan involves database interaction, the operation efficiency of the above-mentioned Bao optimizer is relatively low. Summary of the invention

[0003] The main purpose of this application is to provide a query optimizer selection method, device, equipment and readable storage medium, aiming to solve the technical problem of low operating efficiency of Bao optimizer.

[0004] To achieve the above-mentioned purpose, an embodiment of the present application provides a method for selecting a query optimizer, each query optimizer constructs an expert network model, and the method includes:

[0005] Inputting the feature vector to be optimized of the query statement to be executed into each of the expert network models to obtain first feature vectors respectively, and inputting the feature vector to be optimized into a preset ideal expert network model to obtain an ideal feature vector;

[0006] Based on each of the first feature vectors and the ideal feature vector, generating a probability of selection of each of the query optimizers;

[0007] The query optimizer with the highest probability of being selected is used as the target optimizer for the query statement to be executed.

[0008] To achieve the above-mentioned purpose, an embodiment of the present application provides a query optimizer selection device, each query optimizer is constructed with an expert network model, and the query optimizer selection device comprises:

[0009] An estimation module, used for inputting the feature vector to be optimized of the query statement to be executed into each of the expert network models to obtain each first feature vector, and inputting the feature vector to be optimized into a preset ideal expert network model to obtain an ideal feature vector;

[0010] A generating module, configured to generate a probability of selection of each of the query optimizers based on each of the first feature vectors and the ideal feature vector;

[0011] The selection module is used to select the query optimizer with the highest selection probability as the target optimizer of the query statement to be executed.

[0012] To achieve the above-mentioned purpose, an embodiment of the present application provides a query optimizer selection device, wherein the query optimizer selection device comprises: a memory, a processor, and a query optimizer selection program stored in the memory and executable on the processor, wherein the query optimizer selection program implements the steps of the query optimizer selection method as described above when executed by the processor.

[0013] To achieve the above objectives, an embodiment of the present application provides a readable storage medium, on which a query optimizer selection program is stored, and when the query optimizer selection program is executed by a processor, the steps of the query optimizer selection method as described above are implemented.

[0014] An embodiment of the present application discloses a method for selecting a query optimizer, wherein each query optimizer is constructed with an expert network model. The method comprises: inputting a feature vector to be optimized of a query statement to be executed into each of the expert network models to obtain first feature vectors respectively, and inputting the feature vector to be optimized into a preset ideal expert network model to obtain an ideal feature vector; based on each of the first feature vectors and the ideal feature vector, generating a probability of selection of each of the query optimizers; and selecting the query optimizer with the highest probability of selection as the target optimizer for the query statement to be executed.

[0015] In the embodiment of the present application, the query optimizer is constructed with an expert network model. When there is a query statement to be executed, the feature vector to be optimized of the query statement to be executed is input into each expert network model to obtain each first feature vector, wherein any first feature vector is a vectorized representation of the execution plan of the corresponding query optimizer. Compared with directly generating the execution plan through the query optimizer, when the vectorized representation of the execution plan is generated through the expert network model, the estimation or prediction of the vectorized representation of the execution plan is completed through the internal interaction of the expert network model. The features characterizing the execution plan can be quickly obtained without interacting with the database, which greatly improves the operation efficiency and the query optimizer selection speed. At the same time, the ideal feature vector is obtained by presetting the ideal expert network model. The ideal feature vector is the feature vector of the predicted optimal execution plan of the query statement to be executed. The ideal feature vector is the standard for evaluating each first feature vector. Therefore, the probability of each query optimizer being selected can be evaluated by this standard. Finally, the query optimizer with the highest probability of being selected, that is, the best query optimizer, is used as the target optimizer for the query statement to be executed. Therefore, the best query optimizer is selected to complete the query task of the query statement to be executed while improving the operating efficiency, thereby achieving a comprehensive improvement in the overall performance of the selective query optimizer. BRIEF DESCRIPTION OF THE DRAWINGS

[0016] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on the structures shown in these drawings without paying any creative work.

[0017] Figure 1 This is a flow chart of the first embodiment of the method for selecting a query optimizer of the present application;

[0018] Figure 2 A schematic diagram of a framework for selecting a query optimizer in the method for selecting a query optimizer of this application;

[0019] Figure 3 This is a flow chart of a second embodiment of the method for selecting a query optimizer of the present application;

[0020] Figure 4 A schematic diagram of the framework of the expert network model training process in the query optimizer selection method of this application;

[0021] Figure 5 This is a flow chart of a third embodiment of the method for selecting a query optimizer of the present application;

[0022] Figure 6 This is a functional module diagram of a selection device of a query optimizer according to an embodiment of the present application;

[0023] Figure 7 This is a schematic diagram of the hardware structure of the device involved in the embodiment of the present application.

[0024] The realization of the purpose, functional features and advantages of this application will be further explained in conjunction with embodiments and with reference to the accompanying drawings. DETAILED DESCRIPTION

[0025] It should be understood that the specific embodiments described herein are only used to explain the present application and are not used to limit the present application.

[0026] The following will be combined with the drawings in the embodiments of the present application to clearly and completely describe the technical solutions in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.

[0027] It should be noted that all directional indications in the embodiments of the present application (such as up, down, left, right, front, back, etc.) are only used to explain the relative position relationship, movement status, etc. between the components under a certain specific posture (as shown in the accompanying drawings). If the specific posture changes, the directional indication will also change accordingly.

[0028] In this application, unless otherwise clearly specified and limited, the terms "connection", "fixation", etc. should be understood in a broad sense. For example, "fixation" can be a fixed connection, a detachable connection, or an integral connection; it can be a mechanical connection or an electrical connection; it can be a direct connection or an indirect connection through an intermediate medium, it can be the internal connection of two elements or the interaction relationship between two elements, unless otherwise clearly defined. For ordinary technicians in this field, the specific meanings of the above terms in this application can be understood according to specific circumstances.

[0029] In addition, in this application, descriptions such as "first", "second", etc. are only used for descriptive purposes and cannot be understood as indicating or implying their relative importance or implicitly indicating the number of technical features indicated. Therefore, the features defined as "first" and "second" may explicitly or implicitly include at least one of the features. In addition, the technical solutions between the various embodiments can be combined with each other, but they must be based on the ability of ordinary technicians in this field to implement them. When the combination of technical solutions is contradictory or cannot be implemented, it should be deemed that such combination of technical solutions does not exist and is not within the scope of protection required by this application.

[0030] In view of the above situation, this application proposes a method for selecting a query optimizer, referring to Figure 1 , Figure 1This is a flow chart of a first embodiment of a method for selecting a query optimizer of the present application. In this embodiment, each query optimizer constructs an expert network model, and the method for selecting a query optimizer includes:

[0031] Step S10, inputting the feature vector to be optimized of the query statement to be executed into each of the expert network models to obtain first feature vectors respectively, and inputting the feature vector to be optimized into a preset ideal expert network model to obtain an ideal feature vector;

[0032] It should be noted that, in this embodiment, each query optimizer will construct an expert network model, which is actually a model trained based on the characteristics of the query optimizer. The expert network model can output the characteristics of the execution plan corresponding to the query optimizer, that is, the first characteristic vector mentioned above. Therefore, the result actually output by the query optimizer can be used as the label of the statement input to the query optimizer to form a training sample, and the expert network model is trained, so that the expert network model can output the execution plan characteristics corresponding to the query optimization. The above-mentioned query optimizer can be the default query optimizer of Postgresql (a database management system), or it can be a query optimizer formed by changing different database global optimization parameters on the basis of the default query optimizer, for example, prohibiting nested loops, prohibiting merge sorting, etc. Different optimization parameters can form different query optimizers. In addition, the result of the above-mentioned preset ideal expert network model can be characterized, and the query optimizer that is most suitable for the query statement to be executed in each query optimizer executes the execution plan obtained by the query statement to be executed. It is worth noting that compared with the expert network model constructed based on the query optimizer, the ideal expert network model is not constructed for a certain query optimizer, but the best result in each query optimizer is selected for the training of the ideal expert network model. For example, there are query optimizers A, B, and C, which correspond to expert network models A, B, and C, respectively. Each query optimizer has execution plans A, B, and C based on the training query statements. In the training phase, execution plan A will be used as a training sample for expert network model A, execution plan B will be used as a training sample for expert network model B, and execution plan C will be used as a training sample for expert network model C. For the ideal expert network model, the best execution plan will be selected from execution plans A, B, and C. For example, execution plan A will be used as a training sample for the ideal expert network model for training, that is, the training sample used by the query optimizer A's results will be used for training. In the next round of training phase, when a new training query statement is used for training, if the query optimization of query optimizer B is optimal at this time, the ideal expert network model will be trained based on the query results of query optimizer B. Therefore, the ideal expert network model is not targeted at a certain query optimizer, and its output result is the expected optimal result.

[0033] In addition, it should be noted that the network results of each expert network model and the ideal expert network model can be the same or different. In this embodiment, each expert network model and the ideal expert network model can use the same network model, such as a feedforward neural network, where the expert network model can use expert i (·) indicates that the ideal expert network can use expert m(·) indicates that the expert network model expert i (·) is used as an example to illustrate:

[0034]

[0035] In the formula, x q is the feature vector to be optimized, and are the model parameters of the expert network model.

[0036] Exemplarily, when there is a query demand, the feature vector to be optimized of the corresponding query statement to be executed is input into each expert network model, wherein the feature vector to be optimized is the result of vectorizing the query statement to be executed, which is convenient for input into each expert network model. Each expert network model outputs a first feature vector respectively. For any first feature vector, the first feature vector can represent the execution plan of the corresponding query optimizer. For example, suppose that the above feature vector to be optimized is input into the expert network model A to obtain the first feature vector A, wherein the expert network model A is obtained based on the training of the query optimizer A, then the first feature vector A will represent the execution plan obtained by the query optimizer A executing the above query statement to be executed. Similarly, the feature vector to be optimized is input into the preset ideal expert network model to obtain the ideal feature vector. The ideal feature vector will be used as a basis for evaluating the pros and cons of each first feature vector.

[0037] It can be understood that by outputting the first feature vector through the expert network model, compared with directly executing the query statement to be executed through the query optimizer to obtain the execution plan, the expert network model does not need to interact with the actual database, and can quickly obtain the characteristics of the corresponding query optimizer execution plan (i.e., vectorized representation), which greatly improves the operating efficiency and the query optimizer selection speed.

[0038] Step S20, generating a probability of being selected for each of the query optimizers based on each of the first feature vectors and the ideal feature vector;

[0039] Exemplarily, based on each first feature vector and the ideal feature vector, the probability of each query optimizer being selected is generated. It can be understood that the ideal feature vector can characterize the characteristics of the optimal execution plan expected for the query statement to be executed. Therefore, the closer the first feature vector is to the ideal feature vector, the better the execution plan corresponding to it is. Accordingly, the probability of the query optimizer that obtains the first feature vector being selected is also higher. For example, each first feature vector can be compared with the ideal feature vector to obtain the similarity between each first feature vector and the ideal feature vector, and the probability of each query optimizer being selected is obtained based on the similarity, wherein the probability of being selected is proportional to the similarity.

[0040] Step S30, the query optimizer with the highest probability of being selected is used as the target optimizer for the query statement to be executed.

[0041] Exemplarily, the query optimizer with the highest probability of being selected is selected as the optimal query optimizer for executing the query statement to be executed, that is, the target optimizer. The query task is then executed based on the query statement to be executed by the selected target optimizer.

[0042] In this embodiment, each query optimizer is constructed with an expert network model, and the query optimizer selection method includes: inputting the feature vector to be optimized of the query statement to be executed into each expert network model to obtain each first feature vector, and inputting the feature vector to be optimized into a preset ideal expert network model to obtain an ideal feature vector; based on each first feature vector and the ideal feature vector, generating the probability of being selected of each query optimizer; and taking the query optimizer with the largest probability of being selected as the target optimizer of the query statement to be executed. That is, the query optimizer of the embodiment of the present application is constructed with an expert network model, and when there is a query statement to be executed, the feature vector to be optimized of the query statement to be executed is input into each expert network model to obtain each first feature vector, wherein any first feature vector is a vectorized representation of the execution plan of the corresponding query optimizer, and compared with directly generating the execution plan through the query optimizer, when the vectorized representation of the execution plan is generated through the expert network model, the estimation or prediction of the vectorized representation of the execution plan is completed by the internal interaction of the expert network model, and the features characterizing the execution plan can be quickly obtained without interacting with the database, which greatly improves the operation efficiency and the selection speed of the query optimizer. At the same time, the ideal feature vector is obtained by presetting the ideal expert network model. The ideal feature vector is the feature vector of the predicted optimal execution plan of the query statement to be executed. The ideal feature vector is the standard for evaluating each first feature vector. Therefore, the probability of each query optimizer being selected can be evaluated by this standard. Finally, the query optimizer with the highest probability of being selected, that is, the best query optimizer, is used as the target optimizer for the query statement to be executed. In this way, the best query optimizer is selected to complete the query task of the query statement to be executed while improving the operating efficiency, thereby achieving a comprehensive improvement in the overall performance of the selective query optimizer.

[0043] In a feasible implementation manner, generating the probability of selection of each query optimizer based on each of the first feature vectors and the ideal feature vector includes:

[0044] Step S210, for a first feature vector corresponding to any query optimizer, generating a similarity between the first feature vector and the ideal feature vector;

[0045] Step S220: determining the probability of the query optimizer being selected based on the similarity, wherein the probability of the query optimizer being selected is proportional to the similarity.

[0046] Exemplarily, after obtaining each first feature vector and the ideal feature vector, for any first feature vector, the similarity between the first feature vector and the ideal feature vector can be generated by a preset gating network, and the gating network determines the probability of the query optimizer being selected based on the similarity, and the probability of the query optimizer being selected is proportional to the similarity. For example, the input of the gating network is each first feature vector and the ideal feature vector, such as:

[0047]

[0048] Ideal eigenvector = expert m (x q )

[0049] In the formula, expert m (x q ) represents the ideal expert network model input feature vector x to be optimized q The output result is the ideal feature vector; expert i (x q ) represents the expert network model i input feature vector x to be optimized q The final result is output, namely the first eigenvector i.

[0050] The output of the gating network is the probability of each query optimizer being selected, such as based on the feature vector to be optimized x q , for the probability of any query optimizer being selected:

[0051] p i (x q )=Similarity(expert i (x q ),expert m (x q ))

[0052] In the formula, the similarity function Similarity(·) can use cosine similarity, Euclidean distance, Manhattan distance, Minkowski distance, etc., and cosine similarity is used as an example for explanation:

[0053]

[0054] In the formula, · represents the inner product of the vector, ‖·‖ represents the modulus of the vector, and x1 can represent expert i (x q ), x2 can represent expert m (xq ).

[0055] It should also be noted that the final probability of selection of the query optimizer is proportional to the similarity obtained by comparing the first feature vector corresponding to the query optimizer with the ideal feature vector, and there is no specific restriction on the conversion method between the specific similarity and the probability of selection.

[0056] In a feasible implementation manner, before inputting the feature vectors to be optimized of the query statements to be executed into the expert network models to obtain the first feature vectors, the method includes:

[0057] Step S110, inputting the query statement to be executed into a preset optimizer to obtain a query execution plan;

[0058] Step S120, encoding each execution node in the query execution plan, and concatenating and combining each encoding as a query feature vector to be optimized for the query statement.

[0059] Exemplarily, for the feature vector to be optimized of the query statement to be executed, it can be generated based on a fixed preset optimizer, for example, the preset optimizer can be the default query optimizer of Postgresql. The query statement to be executed can be directly input into the preset optimizer, and the preset optimizer obtains the query execution plan according to the query statement to be executed, and then encodes the operator type, query predicate statement, connection type and table involved in the node operation of each execution node of the query execution plan, and then splices these codes together as the embedded vector representation of this query statement, that is, the above-mentioned optimized feature vector. For the encoding model of the query execution plan, a network model including multiple Transformer layers (such as 8 Transformer layers can be selected) can be used to capture the dependency relationship of different parts of different execution nodes for encoding to obtain the feature vector to be optimized. In addition, for the encoding process, GNN (Graph Neural Network, graph neural network model) and TreeLSTM, i.e., LSTM (Long Short-Term Memory) of tree structure, can be used to complete the encoding to realize the vectorization of the query execution plan.

[0060] Reference Figure 2, which is a schematic diagram of the framework for selecting a query optimizer in this application. The query statement to be executed in the figure will be input into the default query optimizer to obtain a query execution plan, and the query execution plan will be vectorized to obtain a feature vector to be optimized. The feature vector to be optimized will also be input into each expert network model 1 to expert network model n, and the ideal expert network model to obtain first vector features 1 to first vector features n, and ideal vector features. The first vector features 1 to first vector features n and the ideal vector features are then input into the gating network, and the gating network calculates the similarity between each first vector feature and the ideal vector feature, for example, the similarity between the first vector feature 1 and the ideal vector feature is 1. The gating network then determines the probability 1 of the query optimizer corresponding to the expert network model 1 being selected through the similarity 1, and the probability of being selected is proportional to the similarity. After the gating network obtains the probability of being selected for each query optimizer, the query optimizer with the highest probability of being selected is used as the target optimizer.

[0061] In a feasible implementation manner, each of the query optimizers includes a traditional optimizer and / or a learning optimizer, wherein the traditional optimizer is generated by changing optimization parameters of a preset basic query optimizer.

[0062] It should be noted that in this embodiment, the types of optional query optimizers may include only traditional optimizers, only learning optimizers, or both traditional optimizers and learning optimizers. Among them, the traditional optimizer is a preset basic query optimizer or a preset basic query optimizer generated by changing the optimization parameters. For example, the basic query optimizer may be the default query optimizer of Postgresql. On the basis of the default query optimizer, different database global optimization parameters are changed to form a new traditional query optimizer, for example, nested loops are prohibited, merge sort is prohibited, etc. The learning optimizer may be QueryFormer, etc. It should be noted that when the types of query optimizers include traditional optimizers and learning optimizers, the explorable space when selecting a query optimizer is increased. Since the optimal query optimizer is selected each time, the present application can give full play to the advantages of each query optimizer and improve the upper limit of query performance.

[0063] Reference Figure 3 Based on the first embodiment of the present application, a second embodiment of the present application is proposed. In this embodiment, the same or similar parts as the above embodiment can refer to the above content and will not be repeated here. Before inputting the feature vector to be optimized of the query statement to be executed into each of the expert network models to obtain each first feature vector, the method includes:

[0064] Step A10, for any training query statement in the training sample set, based on the training query statement and each of the expert network models, generating a predicted probability of being selected by each of the query optimizers, and obtaining a set of predicted probabilities;

[0065] It should be noted that this embodiment is mainly a stage for training each expert network model. The above-mentioned training sample set will include different training query statements. For any training query statement, the predicted probability of selection of each query optimizer can be generated based on the training query statement and each expert network model to obtain a collection of predicted probabilities. For the training query statement, the process of obtaining the predicted probability of selection of each query optimizer can refer to the above-mentioned first embodiment, and the query statement to be executed can be replaced with the training query statement. For example, the feature vector of the training query statement is input into each of the expert network models to obtain each second feature vector, and the feature vector of the training query statement is input into the preset ideal expert network model to obtain the predicted ideal feature vector; based on each of the second feature vectors and the predicted ideal feature vector, the predicted probability of selection of each query optimizer is generated. At this stage, the predicted probability of selection in the collection of predicted probabilities is usually not accurate, so the gap between the predicted probability of selection and the actual probability of selection can be used to obtain the training of each expert network model.

[0066] Step A20, based on the predicted probability of being selected, selecting a preset number of expert network models to obtain a model collection;

[0067] It should be noted that all expert network models can be trained in each round of training. For example, the above preset number can be the sum of all expert network models, or some expert network models can be selected for training. Usually, there are more query optimizers to choose from, and correspondingly, there are more expert network models. Therefore, in order to improve the training efficiency, in each round of training, some expert models corresponding to query optimizers with a higher probability of being selected are selected to participate in the training. For example, the expert network models of the top k query optimizers with a relatively high predicted probability of being selected are selected to obtain a model collection. In this case, the above preset number can be k. In addition, in order to better explore experts, on the basis of selecting k query optimizers, t query optimizers can be randomly selected from the query optimizers that have not been selected and added to the above model collection. In this case, the above preset number is k+t.

[0068] Step A30, inputting the training query statement into each associated query optimizer of the model collection for execution, and obtaining a collection of actual execution times of the training query statement;

[0069] Exemplarily, the above training query statement is input into the query optimizer of each expert network model in the model collection, that is, input into each associated query optimizer in the model collection, and the actual associated query optimizer executes the training query statement, and the execution time actually spent by each associated query optimizer after executing the training query statement is recorded. The actual execution time constitutes the actual execution time collection. For example, the actual execution time collection is set as:

[0070]

[0071] Where q is the training query statement, GroundTruth i (q) is the actual execution time of the associated query optimizer i executing the training query statement q.

[0072] Step A40, determining the actual probability of selection of each of the associated query optimizers based on the actual execution time collection, and obtaining an actual probability collection;

[0073] It should be noted that, for any associated query optimizer, the shorter the actual execution time it takes to execute the training query statement, the higher the efficiency of the associated query optimizer in executing the training query statement, which means that the associated query optimizer is more suitable for the training query statement and the higher the probability of being selected. Therefore, the actual probability of being selected of the associated query optimizer can be determined by the actual execution time of the associated query optimizer in the actual execution time collection, wherein the actual probability of being selected is inversely proportional to the actual execution time, that is, the shorter the actual execution time, the higher the actual probability of being selected.

[0074] Step A50, constructing a first loss function by using the predicted probability set and the actual probability set;

[0075] It should be noted that the predicted probability collection is the prediction or estimation result generated by each expert network model and the ideal expert network model. The actual probability collection is the actual result obtained through the actual execution time of each associated query optimizer. Assume that for any associated query optimizer in the model collection, the probability of its selection in the predicted probability collection is a, and the probability of its selection in the actual probability collection is b. If a and b are the same, it means that the result of the model prediction or estimation is accurate. Otherwise, it is inaccurate, and the model parameters of the expert network model of the associated query optimizer and the ideal expert network model can be updated based on the difference between a and b. Among them, the model parameter update method can be back propagation update.

[0076] Exemplarily, the first loss function is used to characterize the difference between the predicted probability of being selected and the actual probability of being selected of each associated query optimizer, so the first loss function can be:

[0077]

[0078] In the formula, p i (q) represents the predicted probability of being selected by query optimizer i for training query statement q, Represents the actual probability of query optimizer i being selected for training query statement q.

[0079] Step A60: Based on the function loss of the first loss function, train and update each expert network model in the model collection and the ideal expert network model.

[0080] Exemplarily, it can be understood that since the probability of the prediction of any associated query optimizer in the first loss function being selected is obtained based on the output of the expert network model of the associated query optimizer and the output of the ideal expert network model, the model parameters of each expert network model in the model collection and the ideal expert network model can be updated based on the function loss of the first loss function. The specific model parameter updating process can also refer to the existing method, which will not be repeated here.

[0081] Reference Figure 4 , which is a framework diagram of the expert network model training process in this application. In the figure, for any training query statement, after its query execution plan is vectorized, it will be used by expert network model 1 to expert network model n and the ideal expert network model, and the predicted probability of each query optimizer will be obtained through the gating network, that is, the predicted probability of being selected. And the training query statement will also be input into each query optimizer, and the actual probability corresponding to each query optimizer will be obtained according to the actual execution time of each query optimizer, that is, the actual probability of being selected. Then, based on each predicted probability of being selected and each actual probability of being selected, a first loss function is constructed, and the function loss of the first loss function will also be used for the training of the expert network model and the ideal expert network model.

[0082] In a feasible implementation manner, determining the actual probability of selection of each of the associated query optimizers based on the actual execution time collection includes:

[0083] Step A410, for any actual execution time in the actual execution time collection, determine whether the actual execution time meets a preset high delay condition;

[0084] Step A420, when the preset high latency condition is met, the actual probability of selection of the associated query optimizer of the actual execution time is assigned to zero.

[0085] It should be noted that for some query optimizers with relatively long actual execution time, it is not expected to recommend the query optimizer to execute the query statement. Therefore, for the query optimizer with relatively long actual execution time, the probability of the query optimizer being actually selected can be targeted to lower.

[0086] Exemplarily, for any actual execution time in the actual execution time collection, it can be determined whether the actual execution time meets the preset high latency condition, wherein the preset high latency condition may be a relatively long actual execution time in the actual execution time collection, for example, the n actual execution times with the longest actual execution times in the actual execution time collection, where n may be the product of the total number of actual execution times in the actual execution time collection and the preset proportion. In the case where the actual execution time meets the preset high latency condition, the actual probability of selection of the associated query optimizer of the actual execution time can be directly assigned to zero. That is, it is prevented that the associated query optimizer that meets the preset high latency condition is selected as the final target optimizer.

[0087] In a feasible implementation manner, after determining whether the actual execution time satisfies a preset high delay condition, the method includes:

[0088] Step A430, when the preset high latency condition is not met, the actual probability of selection of the associated query optimizer of the actual execution time is obtained by normalizing the activation function.

[0089] For example, when the actual execution time does not meet the preset high latency condition, the actual probability of the associated query optimizer being selected for the actual execution time can be obtained by a normalized activation function. The normalized activation function is as follows:

[0090]

[0091] In the formula, represents the actual probability of query optimizer i being selected for training query statement q, represents the actual execution time of the query optimizer i associated with the training query statement q, It is a comprehensive feature representation of the actual execution time of each associated query optimizer for the set of training query statements q models.

[0092] In a feasible implementation manner, determining whether the actual execution time satisfies a preset high delay condition includes:

[0093] Step A411, determining the actual shortest execution time in the actual execution time collection, and calculating the ratio between the actual execution time and the shortest execution time;

[0094] Step A412, when the ratio is greater than or equal to a preset high delay ratio threshold, determining that the actual execution time satisfies the preset high delay condition;

[0095] Step A413, when the ratio is less than the preset high delay ratio threshold, it is determined that the actual execution time does not meet the preset high delay condition.

[0096] It should be noted that whether the actual execution time meets the preset high latency condition can also be judged based on the actual shortest execution time in the actual execution time collection.

[0097] Exemplarily, the actual shortest execution time in the actual execution time collection is determined, and then the ratio between each actual execution time and the actual shortest execution time is calculated. If the ratio is large, that is, the ratio is greater than or equal to the preset high delay ratio threshold (wherein the preset high delay ratio threshold can be set by technical personnel according to actual conditions), it can be indicated that the actual execution time corresponds to the query optimizer execution efficiency is slow, so it is determined that the actual execution time meets the preset high delay condition. On the contrary, if the ratio is small, that is, the ratio is less than the preset high delay ratio threshold, it can be determined that the actual execution time does not meet the preset high delay condition.

[0098] In a feasible implementation manner, after selecting a preset number of expert network models based on the predicted probability of selection to obtain a model collection, the method further includes:

[0099] Step S210, obtaining each second feature vector of each expert network model in the model collection relative to the training query statement;

[0100] Step S220, inputting each of the second feature vectors into a preset delay prediction model to obtain a set of predicted execution times of the training query statements;

[0101] Step S230, constructing a second loss function through the predicted execution time collection and the actual execution time collection;

[0102] Step S240: training and updating each expert network model in the model collection based on the function loss of the second loss function.

[0103] It should be noted that, in addition to the above-mentioned first loss function, this embodiment will also construct a second loss function for the training of each expert network model through a collection of predicted execution times and a collection of actual execution times. First, for the acquisition of each second feature vector, the feature vector of the training query statement can be input into each expert network model in the model collection to obtain each second feature vector respectively, or each second feature vector can be obtained from an intermediate quantity in the process of obtaining the probability of each prediction being selected. Then, each second feature vector is input into the preset delay prediction model to obtain a collection of predicted execution times for the training query statement. Among them, the preset delay prediction model has been trained in advance, and the training samples used by the preset delay prediction model can be composed of a vectorized representation of the query execution plan and the actual execution time of the query execution plan. The preset delay prediction model is pre-trained through such training samples, so that the preset delay prediction model has the ability to predict the execution time based on the feature vector. It can be understood that for any predicted execution time in the predicted execution time collection, the predicted execution time is the estimation result of the preset delay prediction model for the second eigenvector of the input, and the second eigenvector is the output result of the corresponding expert network model. Therefore, based on the difference between the predicted execution time and the actual execution time, the model parameters of the above-mentioned corresponding expert network model can be updated. The specific updating process can refer to the above content or existing methods, which will not be repeated here.

[0104] Exemplarily, the second loss function constructed based on the predicted execution time collection and the actual execution time collection is as follows:

[0105]

[0106] In the formula, latency i (q) is the predicted execution time of query optimizer i associated with training query statement q, GroundTruth i (q) is the actual execution time of the associated query optimizer i for the training query statement q, and S is the total number of associated query optimizers in the model collection. The function loss of the second loss function can be used to update each expert network model in the model collection.

[0107] Reference Figure 4 Based on the first and second embodiments of the present application, the third embodiment of the present application is proposed. In this embodiment, the same or similar parts as the above embodiments can refer to the above content and will not be repeated here. Before inputting the feature vector to be optimized into the preset ideal expert network model to obtain the ideal feature vector, the method also includes:

[0108] Step B10, for any training query sentence in the training sample set, inputting the feature vector of the training query sentence into the preset ideal expert network model to obtain a predicted ideal feature vector;

[0109] Step B20, inputting the predicted ideal feature vector into a preset delay prediction model to obtain the ideal shortest execution time of the training query statement;

[0110] Step B30, determining the actual shortest execution time in the actual execution time collection;

[0111] Step B40, constructing a third loss function by using the ideal shortest execution time and the actual shortest execution time;

[0112] Step B50: training and updating the preset ideal expert network model based on the function loss of the third loss function.

[0113] It should be noted that this embodiment will also construct a second loss function for the training of the ideal expert network model through the ideal shortest execution time and the actual shortest execution time. First, the feature vector of the training query statement can be input into the ideal expert to obtain a predicted ideal feature vector. The predicted ideal feature vector is then input into the preset delay prediction model to obtain the ideal shortest execution time of the training query statement. Among them, the preset delay prediction model has been trained in advance, and can refer to the above embodiment, which will not be repeated here. It can be understood that for the ideal shortest execution time, it is the estimation result of the preset delay prediction model for the input predicted ideal feature vector, and the predicted ideal feature vector is the output result of the ideal expert network model. Therefore, based on the difference between the pre-ideal shortest execution time and the actual shortest execution time in the actual execution time collection, the model parameters of the above-mentioned ideal expert network model can be updated. The specific update process can refer to the above content or existing methods, which will not be repeated here.

[0114] Exemplarily, the third loss function constructed based on the ideal shortest execution time and the actual shortest execution time is as follows:

[0115]

[0116] Where GroundTruth min (q) is the actual shortest execution time in the actual execution time collection obtained for the training query statement q, latency m (q) is the predicted execution time required for the query execution feature corresponding to the predicted ideal feature vector obtained by the ideal expert network model for the training query statement q. Similarly, the function loss based on the third loss function can be used to update the preset ideal expert network model.

[0117] In addition, it should be noted that the first loss function, the second loss function and the third loss function can be combined into a comprehensive loss function, and the comprehensive loss function is as follows:

[0118] Loss synthesis = α·loss KL +(1-α)·loss MSE

[0119]

[0120] In the formula, loss is a comprehensive loss function, loss KL is the first loss function mentioned above, is the second loss function mentioned above, is the third loss function mentioned above, α is the weight value, which can be set by technicians according to actual needs, for example, α is 0.8.

[0121] It should be noted that what is disclosed above is only a preferred embodiment of the present application, and it certainly cannot be used to limit the protection scope of the present application. Ordinary technicians in this field can understand that all or part of the processes of implementing the above embodiment and the equivalent changes made according to the claims of the present invention are still within the scope covered by the present application.

[0122] That is to say, the above specific embodiments are only used to help understand the technical concept of the embodiments of the present application, and do not constitute a limitation of the present application. More simple transformations based on the technical concept should all be within the scope of protection of the present application.

[0123] In addition, the present application embodiment also proposes a query optimizer selection device, referring to Figure 6 , Figure 6 A functional module diagram of an embodiment of a query optimizer selection device of the present application.

[0124] In this embodiment, the query optimizer selection device includes:

[0125] The estimation module 10 is used to input the feature vector to be optimized of the query statement to be executed into each of the expert network models to obtain each first feature vector, and input the feature vector to be optimized into a preset ideal expert network model to obtain an ideal feature vector;

[0126] A generating module 20, configured to generate a probability of selection of each of the query optimizers based on each of the first feature vectors and the ideal feature vector;

[0127] The selection module 30 is used to select the query optimizer with the highest selection probability as the target optimizer for the query statement to be executed.

[0128] In an optional embodiment, the query optimizer selection device further includes a training module 40, and the training module 40 is used to:

[0129] For any training query statement in the training sample set, based on the training query statement and each of the expert network models, generate a predicted probability of being selected by each of the query optimizers to obtain a set of predicted probabilities;

[0130] Based on the predicted probability of being selected, a preset number of expert network models are selected to obtain a model collection;

[0131] Inputting the training query statements into each associated query optimizer of the model collection for execution, and obtaining a collection of actual execution times of the training query statements;

[0132] Determine the actual probability of selection of each of the associated query optimizers based on the actual execution time collection, and obtain an actual probability collection;

[0133] Constructing a first loss function by using the predicted probability collection and the actual probability collection;

[0134] Based on the function loss of the first loss function, each expert network model in the model collection and the ideal expert network model are trained and updated.

[0135] In an optional embodiment, the training module 40 is further used for:

[0136] For any actual execution time in the actual execution time collection, determining whether the actual execution time meets a preset high latency condition;

[0137] When the preset high latency condition is met, the actual probability of selection of the query optimizer associated with the actual execution time is assigned to zero.

[0138] In an optional embodiment, the training module 40 is further used for:

[0139] When the preset high latency condition is not met, the actual probability of selection of the associated query optimizer of the actual execution time is obtained by normalizing the activation function.

[0140] In an optional embodiment, the training module 40 is further used for:

[0141] Determine the actual shortest execution time in the actual execution time collection, and calculate the ratio between the actual execution time and the shortest execution time;

[0142] In a case where the ratio is greater than or equal to a preset high delay ratio threshold, determining that the actual execution time satisfies the preset high delay condition;

[0143] When the ratio is less than the preset high delay ratio threshold, it is determined that the actual execution time does not meet the preset high delay condition.

[0144] In an optional embodiment, the training module 40 is further used for:

[0145] Obtaining each second feature vector of each expert network model in the model collection relative to the training query statement;

[0146] Inputting each of the second feature vectors into a preset delay prediction model to obtain a collection of predicted execution times of the training query statements;

[0147] Constructing a second loss function by using the predicted execution time collection and the actual execution time collection;

[0148] Based on the function loss of the second loss function, each expert network model in the model collection is trained and updated.

[0149] In an optional embodiment, the training module 40 is further used for:

[0150] For any training query sentence in the training sample set, inputting the feature vector of the training query sentence into the preset ideal expert network model to obtain a predicted ideal feature vector;

[0151] Inputting the predicted ideal feature vector into a preset delay prediction model to obtain the ideal shortest execution time of the training query statement;

[0152] Determine the actual shortest execution time in the set of actual execution times;

[0153] Constructing a third loss function by using the ideal shortest execution time and the actual shortest execution time;

[0154] Based on the function loss of the third loss function, the preset ideal expert network model is trained and updated.

[0155] In an optional embodiment, the generating module 20 is further used for:

[0156] For a first feature vector corresponding to any query optimizer, generating a similarity between the first feature vector and the ideal feature vector;

[0157] The probability of the query optimizer being selected is determined based on the similarity, wherein the probability of the query optimizer being selected is proportional to the similarity.

[0158] In an optional embodiment, the query optimizer selection device further includes a vectorization module 50, and the vectorization module 50 is used to:

[0159] Inputting the query statement to be executed into a preset optimizer to obtain a query execution plan;

[0160] Each execution node in the query execution plan is encoded, and each encoding is concatenated and combined as the query feature vector to be optimized of the query statement.

[0161] In an optional embodiment, each of the query optimizers includes a traditional optimizer and / or a learning optimizer, wherein the traditional optimizer is generated by changing optimization parameters of a preset basic query optimizer.

[0162] The query optimizer selection device provided in this embodiment and the query optimizer selection method provided in the above-mentioned embodiment belong to the same inventive concept. The technical details not fully described in this embodiment can be found in the embodiment of the query optimizer selection method mentioned above. This embodiment has the same beneficial effects as the embodiments of the query optimizer selection method, which will not be repeated here.

[0163] The device embodiments described above are merely illustrative, and the units described as separate components may or may not be physically separated, that is, they may be located in one place or distributed on multiple network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the solution of this embodiment.

[0164] In addition, an embodiment of the present application also provides a query optimizer selection device, which may be, for example, a base station, or a mobile network access management entity or node.

[0165] Reference Figure 7 , Figure 7 A schematic diagram of the hardware structure of a device provided in an embodiment of the present application. Figure 7 As shown, the device may include: a processor 1001, such as a central processing unit (CPU), a communication bus 1002, a user interface 1003, a network interface 1004, and a memory 1005. Among them, the communication bus 1002 is used to realize the connection and communication between these components. The user interface 1003 may include a display screen (Display), an input unit such as a keyboard (Keyboard), and the optional user interface 1003 may also include a standard wired interface and a wireless interface. The network interface 1004 may optionally include a standard wired interface and a wireless interface (such as a wireless fidelity (WIreless-FIdelity, WI-FI) interface). The memory 1005 may be a high-speed random access memory (Random Access Memory, RAM) memory, or a stable non-volatile memory (Non-Volatile Memory, NVM), such as a disk memory. The memory 1005 may also be a storage device independent of the aforementioned processor 1001.

[0166] Those skilled in the art will understand that Figure 7 The structure shown in the figure does not constitute a limitation of the device, and may include more or fewer components than shown in the figure, or combine certain components, or arrange the components differently. Figure 7 As shown, the memory 1005 as a storage medium may include an operating system, a data storage module, a network communication module, a user interface module, and a query optimizer selection program.

[0167] exist Figure 7 In the device shown, the network interface 1004 is mainly used for data communication with other devices; the user interface 1003 is mainly used for data interaction with the user; the processor 1001 and the memory 1005 in this embodiment can be set in the communication device, and the communication device calls the query optimizer selection program stored in the memory 1005 through the processor 1001, and executes the selection method applied to the query optimizer provided in any of the above embodiments.

[0168] The terminal proposed in this embodiment and the selection method applied to the query optimizer proposed in the above embodiment belong to the same inventive concept. The technical details not fully described in this embodiment can be referred to any of the above embodiments, and this embodiment has the same beneficial effects as the selection method of executing the query optimizer.

[0169] In addition, an embodiment of the present application also proposes a storage medium, which is a computer storage medium. The computer-readable storage medium can be a non-volatile computer-readable storage medium. A query optimizer selection program is stored on the computer-readable storage medium. When the query optimizer selection program is executed by the processor, the query optimizer selection method of the present application as described above is implemented.

[0170] The various embodiments of the device and computer-readable storage medium of the present application may all refer to the various embodiments of the method for selecting the query optimizer of the present application, which will not be described in detail here.

[0171] It should be noted that, in this article, the terms "include", "comprises" or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, article or system including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or system. In the absence of further restrictions, an element defined by the sentence "comprises a ..." does not exclude the existence of other identical elements in the process, method, article or system including the element.

[0172] The serial numbers of the above-mentioned embodiments of the present application are for description only and do not represent the advantages or disadvantages of the embodiments.

[0173] Through the description of the above implementation methods, those skilled in the art can clearly understand that the above-mentioned embodiment methods can be implemented by means of software plus a necessary general hardware platform, and of course by hardware, but in many cases the former is a better implementation method. Based on such an understanding, the technical solution of the present application is essentially or the part that contributes to the prior art can be embodied in the form of a software product, which is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk) as described above, and includes a number of instructions for a device (which can be a base station, a computer, a server, or a network device, etc.) to execute the methods described in each embodiment of the present application.

[0174] The above are only preferred embodiments of the present application, and are not intended to limit the patent scope of the present application. Any equivalent structure or equivalent process transformation made using the contents of the present application specification and drawings, or directly or indirectly applied in other related technical fields, are also included in the patent protection scope of the present application.

Claims

1. A method for selecting a query optimizer, characterized in that: Each query optimizer is constructed with an expert network model, and the method comprises: Inputting the feature vector to be optimized of the query statement to be executed into each of the expert network models to obtain first feature vectors respectively, and inputting the feature vector to be optimized into a preset ideal expert network model to obtain an ideal feature vector; Based on each of the first feature vectors and the ideal feature vector, generating a probability of selection of each of the query optimizers; The query optimizer with the highest probability of being selected is used as the target optimizer for the query statement to be executed.

2. The method for selecting a query optimizer according to claim 1, wherein: Before inputting the feature vectors to be optimized of the query statements to be executed into the expert network models to obtain the first feature vectors respectively, the method includes: For any training query statement in the training sample set, based on the training query statement and each of the expert network models, generate a predicted probability of being selected by each of the query optimizers to obtain a set of predicted probabilities; Based on the predicted probability of being selected, a preset number of expert network models are selected to obtain a model collection; Inputting the training query statements into each associated query optimizer of the model collection for execution, and obtaining a collection of actual execution times of the training query statements; Determine the actual probability of selection of each of the associated query optimizers based on the actual execution time collection, and obtain an actual probability collection; Constructing a first loss function by using the predicted probability collection and the actual probability collection; Based on the function loss of the first loss function, each expert network model in the model collection and the ideal expert network model are trained and updated.

3. The method for selecting a query optimizer according to claim 2, wherein: The determining the actual probability of selection of each of the associated query optimizers based on the actual execution time collection includes: For any actual execution time in the actual execution time collection, determining whether the actual execution time meets a preset high latency condition; When the preset high latency condition is met, the actual probability of selection of the query optimizer associated with the actual execution time is assigned to zero.

4. The method for selecting a query optimizer according to claim 3, wherein: After determining whether the actual execution time satisfies a preset high delay condition, the method includes: When the preset high latency condition is not met, the actual probability of selection of the associated query optimizer of the actual execution time is obtained by normalizing the activation function.

5. The method for selecting a query optimizer according to claim 3, wherein: The determining whether the actual execution time satisfies the preset high delay condition comprises: Determine the actual shortest execution time in the actual execution time collection, and calculate the ratio between the actual execution time and the shortest execution time; In a case where the ratio is greater than or equal to a preset high delay ratio threshold, determining that the actual execution time satisfies the preset high delay condition; When the ratio is less than the preset high delay ratio threshold, it is determined that the actual execution time does not meet the preset high delay condition.

6. The method for selecting a query optimizer according to claim 2, wherein: After selecting a preset number of expert network models based on the predicted probability of selection to obtain a model collection, the method further includes: Obtaining each second feature vector of each expert network model in the model collection relative to the training query statement; Inputting each of the second feature vectors into a preset delay prediction model to obtain a collection of predicted execution times of the training query statements; Constructing a second loss function by using the predicted execution time collection and the actual execution time collection; Based on the function loss of the second loss function, each expert network model in the model collection is trained and updated.

7. The method for selecting a query optimizer according to claim 2, wherein: Before inputting the feature vector to be optimized into a preset ideal expert network model to obtain an ideal feature vector, the method further includes: For any training query sentence in the training sample set, inputting the feature vector of the training query sentence into the preset ideal expert network model to obtain a predicted ideal feature vector; Inputting the predicted ideal feature vector into a preset delay prediction model to obtain the ideal shortest execution time of the training query statement; Determine the actual shortest execution time in the set of actual execution times; Constructing a third loss function by using the ideal shortest execution time and the actual shortest execution time; Based on the function loss of the third loss function, the preset ideal expert network model is trained and updated.

8. The method for selecting a query optimizer according to claim 1, wherein: The generating the selection probability of each query optimizer based on each of the first feature vectors and the ideal feature vector comprises: For a first feature vector corresponding to any query optimizer, generating a similarity between the first feature vector and the ideal feature vector; The probability of the query optimizer being selected is determined based on the similarity, wherein the probability of the query optimizer being selected is proportional to the similarity.

9. The method for selecting a query optimizer according to claim 1, wherein: Before inputting the feature vectors to be optimized of the query statements to be executed into the expert network models to obtain the first feature vectors, the method includes: Inputting the query statement to be executed into a preset optimizer to obtain a query execution plan; Each execution node in the query execution plan is encoded, and each encoding is concatenated and combined as the query feature vector to be optimized of the query statement.

10. The method for selecting a query optimizer according to any one of claims 1 to 8, characterized in that: Each of the query optimizers includes a traditional optimizer and / or a learning optimizer, wherein the traditional optimizer is generated by changing optimization parameters of a preset basic query optimizer.

11. A query optimizer selection device, characterized in that: Each query optimizer is constructed with an expert network model, and the selection device of the query optimizer includes: An estimation module, used for inputting the feature vector to be optimized of the query statement to be executed into each of the expert network models to obtain each first feature vector, and inputting the feature vector to be optimized into a preset ideal expert network model to obtain an ideal feature vector; A generating module, configured to generate a probability of selection of each of the query optimizers based on each of the first feature vectors and the ideal feature vector; The selection module is used to select the query optimizer with the highest selection probability as the target optimizer of the query statement to be executed.

12. A query optimizer selection device, characterized in that: The query optimizer selection device includes: a memory, a processor, and a query optimizer selection program stored in the memory and executable on the processor. When the query optimizer selection program is executed by the processor, the steps of the query optimizer selection method according to any one of claims 1 to 10 are implemented.

13. A readable storage medium, characterized in that: The readable storage medium is a computer-readable storage medium, and a query optimizer selection program is stored on the readable storage medium. When the query optimizer selection program is executed by a processor, the steps of the query optimizer selection method according to any one of claims 1 to 10 are implemented.

Images