Query plan generation method and device, electronic equipment and storage medium

Abstract

The invention discloses a query plan generation method and device, electronic equipment and a storage medium. A predicted resource consumption value of a structured query language is generated based on historical query according to a meta-learning model in a query optimizer, and at least one execution plan is generated according to physical query optimization; obtaining a first execution plan, calculating a first resource consumption value of the first execution plan, and calculating a first comparison similarity between the first resource consumption value and the predicted resource consumption value; and after determining that the first comparison similarity is greater than or equal to a preset similarity threshold, determining the first execution plan as a target execution plan. A structured query language is estimated based on historical query through a preposed meta-learning model to obtain a predicted resource consumption value, and similarity calculation is performed on the predicted resource consumption value and the resource consumption value of the generated execution plan to determine a final target execution plan, so that the cost estimation number of the execution plan is reduced, and the efficiency of the execution plan is improved. The output speed of the final execution plan is accelerated, and the calculation overhead is reduced.

Description

Technical Field

[0001] The present disclosure relates to the field of artificial intelligence technology, and in particular to a method and device for generating a query plan, an electronic device, and a storage medium. Background Art

[0002] The query optimizer is an important module for distributed database systems to improve query efficiency. Its main responsibility is to determine the best way to execute a query. When a user submits a query request to the database, the query optimizer analyzes the query and generates one or more possible execution plans for it. It then evaluates the cost of these execution plans and selects the plan with the lowest cost to execute, in order to minimize the query response time and resource consumption.

[0003] Although the above method can realize the generation of execution plans, this method requires the cost of each execution plan to be displayed on the screen, resulting in a large amount of data calculation, occupying a large amount of computing resources, and a long response time. Summary of the invention

[0004] The present disclosure provides a query plan generation method, device, electronic device and storage medium, which are mainly intended to solve the problem of large data calculation volume, large amount of computing resources occupied and long response time.

[0005] According to a first aspect of the present disclosure, a method for generating a query plan is provided, comprising:

[0006] Input the parsed structured query language into the query optimizer;

[0007] Generate a predicted resource consumption value for the structured query language based on historical queries according to a meta-learning model in the query optimizer, and generate at least one execution plan according to physical query optimization;

[0008] Obtaining a first execution plan from the execution plan, calculating a first resource consumption value of the first execution plan, and calculating a first comparison similarity between the first resource consumption value and the predicted resource consumption value;

[0009] After determining that the first comparison similarity is greater than or equal to the preset similarity threshold, determining the first execution plan as a target execution plan.

[0010] Optionally, after calculating a first comparison similarity between the first resource consumption value and the predicted resource consumption value, the method further includes:

[0011] After determining that the first comparison similarity is less than a preset similarity threshold, obtaining a second execution plan and calculating a second resource consumption value;

[0012] Calculate a second comparison similarity between the second resource consumption value and the predicted resource consumption, compare the second comparison similarity with the preset similarity threshold, and stop calculating the resource consumption value of the execution plan until the comparison similarity is greater than or equal to the similarity threshold.

[0013] Optionally, inputting the parsed structured query language into the query optimizer further comprises:

[0014] Inputting the parsed structured query language and the first timestamp into the query optimizer;

[0015] The generating a predicted resource consumption value for the structured query language based on historical queries according to the meta-learning model in the query optimizer further includes:

[0016] Searching a preset database for a historical execution plan that is close in time to the first timestamp and similar in language to the structured query language;

[0017] The meta-learning model predicts the predicted resource consumption value of the structured query language according to the historical execution plan.

[0018] Optionally, after determining that the first execution plan is the target execution plan, the method further includes:

[0019] generating a second timestamp of the target execution plan;

[0020] The target execution plan and the second timestamp are stored in the preset database.

[0021] Optionally, after calculating a first comparison similarity between the first resource consumption value and the predicted resource consumption value, and after determining that the first comparison similarity is greater than or equal to a preset similarity threshold, and before determining that the first execution plan is a target execution plan, the method further includes:

[0022] Calculating the prediction similarity of the prediction plan, and determining whether the prediction similarity is greater than or equal to a preset similarity threshold;

[0023] When the predicted similarity is greater than or equal to the preset similarity threshold, the first comparative similarity between the first resource consumption value and the predicted resource consumption value is calculated, and after determining that the first comparative similarity is greater than or equal to the preset similarity threshold, the first execution plan is determined to be the target execution plan.

[0024] Optionally, the meta-learning model includes a first feature extractor and a second feature extractor, and before generating a predicted resource consumption value for the structured query language based on historical queries according to the meta-learning model in the query optimizer, the method further includes:

[0025] Obtain the historical execution plan and the target execution plan from the preset database;

[0026] Extracting a first embedding vector of the historical execution plan according to the first feature extractor, and extracting a second embedding vector of the target execution plan according to the second feature extractor;

[0027] Calculating a prototype point according to the first embedding vector, and determining the prototype point to which the target domain data belongs according to the Euclidean distance between the target domain data and the prototype point; wherein different prototype points correspond to different labels;

[0028] Calculating a first loss function according to the Euclidean distance, and calculating a second loss function according to the prototype point to which the target domain data belongs, the label of the prototype point to which the target domain data belongs, the first embedding vector, and the second embedding vector;

[0029] Calculating a target loss function according to the first loss function and the second loss function; wherein the first loss function and the second loss function have different weights;

[0030] Back propagation is performed according to the target loss function to train the first feature extractor and the second feature extractor.

[0031] According to a second aspect of the present disclosure, a query plan generation device is provided, comprising:

[0032] An input unit, used for inputting the parsed structured query language into the query optimizer;

[0033] A first generating unit, configured to generate a predicted resource consumption value for the structured query language based on historical queries according to a meta-learning model in the query optimizer, and to generate at least one execution plan according to physical query optimization;

[0034] A first calculation unit, configured to obtain a first execution plan from the execution plan, calculate a first resource consumption value of the first execution plan, and calculate a first comparison similarity between the first resource consumption value and the predicted resource consumption value;

[0035] A determining unit is configured to determine that the first execution plan is a target execution plan after determining that the first comparison similarity is greater than or equal to the preset similarity threshold.

[0036] Optionally, the device further comprises:

[0037] A first acquisition unit is configured to, after the first calculation unit calculates a first comparison similarity between the first resource consumption value and the predicted resource consumption value, and after determining that the first comparison similarity is less than a preset similarity threshold, acquire a second execution plan and calculate a second resource consumption value;

[0038] The second calculation unit is used to calculate the second comparative similarity between the second resource consumption value and the predicted resource consumption, and compare the second comparative similarity with the preset similarity threshold until the comparative similarity is greater than or equal to the similarity threshold, and then stop calculating the resource consumption value of the execution plan.

[0039] Optionally, the input unit is further used for:

[0040] Inputting the parsed structured query language and the first timestamp into the query optimizer;

[0041] The first generating unit further includes:

[0042] Searching a preset database for a historical execution plan that is close in time to the first timestamp and similar in language to the structured query language;

[0043] The meta-learning model predicts the predicted resource consumption value of the structured query language according to the historical execution plan.

[0044] Optionally, the device further comprises:

[0045] A second generating unit, configured to generate a second timestamp of the target execution plan after the determining unit determines that the first execution plan is the target execution plan;

[0046] A storage unit is used to store the target execution plan and the second timestamp in the preset database.

[0047] Optionally, the device further comprises:

[0048] A third calculation unit, configured to calculate a prediction similarity of the prediction plan and determine whether the prediction similarity is greater than or equal to a preset similarity threshold before the first calculation unit calculates a first comparison similarity between the first resource consumption value and the predicted resource consumption value;

[0049] A fourth determination unit is used to calculate the first comparative similarity between the first resource consumption value and the predicted resource consumption value when the predicted similarity is greater than or equal to the preset similarity threshold, and after determining that the first comparative similarity is greater than or equal to the preset similarity threshold, determine that the first execution plan is the target execution plan.

[0050] Optionally, the meta-learning model includes a first feature extractor and a second feature extractor, and the device further includes:

[0051] A second acquisition unit is used to acquire the historical execution plan and the target execution plan in the preset database before the first generation unit generates the predicted resource consumption value of the structured query language based on the historical query according to the meta-learning model in the query optimizer;

[0052] a training unit, configured to extract a first embedding vector of the historical execution plan according to the first feature extractor, and to extract a second embedding vector of the target execution plan according to the second feature extractor;

[0053] The training unit is further used to calculate a prototype point according to the first embedding vector, and determine the prototype point to which the target domain data belongs according to the Euclidean distance between the target domain data and the prototype point; wherein different prototype points correspond to different labels;

[0054] The training unit is further used to calculate a first loss function according to the Euclidean distance, and calculate a second loss function according to the prototype point to which the target domain data belongs, the label of the prototype point to which the target domain data belongs, the first embedding vector and the second embedding vector;

[0055] The training unit is further used to calculate a target loss function according to the first loss function and the second loss function; wherein the weights of the first loss function and the second loss function are different;

[0056] The training unit is further used to perform back propagation according to the target loss function to train the first feature extractor and the second feature extractor.

[0057] According to a third aspect of the present disclosure, there is provided an electronic device, including:

[0058] at least one processor; and

[0059] a memory communicatively connected to the at least one processor; wherein,

[0060] The memory stores instructions that can be executed by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to perform the method described in the first aspect.

[0061] According to a fourth aspect of the present disclosure, a non-transitory computer-readable storage medium storing computer instructions is provided, wherein the computer instructions are used to enable the computer to execute the method described in the first aspect.

[0062] According to a fifth aspect of the present disclosure, a computer program product is provided, comprising a computer program, wherein when the computer program is executed by a processor, the computer program implements the method as described in the first aspect above.

[0063] The query plan generation method, device, electronic device and storage medium provided by the present disclosure mainly include: inputting the parsed structured query language into the query optimizer; generating a predicted resource consumption value for the structured query language based on historical queries according to the meta-learning model in the query optimizer, and generating at least one execution plan according to physical query optimization; obtaining a first execution plan in the execution plan, calculating a first resource consumption value of the first execution plan, and calculating a first comparative similarity between the first resource consumption value and the predicted resource consumption value; after determining that the first comparative similarity is greater than or equal to the preset similarity threshold, determining that the first execution plan is the target execution plan. Compared with the related art, the embodiment of the present application estimates the structured query language based on historical queries through a pre-placed meta-learning model to obtain a predicted resource consumption value, and performs similarity calculation between the predicted resource consumption value and the resource consumption value of the generated execution plan to determine the final target execution plan, thereby reducing the number of cost estimates of the execution plan, accelerating the output speed of the final execution plan, and reducing the computational overhead.

[0064] It should be understood that the content described in this section is not intended to identify the key or important features of the embodiments of the present application, nor is it intended to limit the scope of the present application. Other features of the present application will become easily understood through the following description. BRIEF DESCRIPTION OF THE DRAWINGS

[0065] The accompanying drawings are used to better understand the present solution and do not constitute a limitation of the present disclosure.

[0066] Figure 1 A flowchart of a method for generating a query plan provided by an embodiment of the present disclosure;

[0067] Figure 2 A framework for generating a query plan provided by an embodiment of the present disclosure;

[0068] Figure 3 A flowchart of a method for generating a query plan provided by an embodiment of the present disclosure;

[0069] Figure 4 A flowchart of a method for generating a query plan provided by an embodiment of the present disclosure;

[0070] Figure 5 A flowchart of a method for generating a query plan provided by an embodiment of the present disclosure;

[0071] Figure 6A flowchart of a method for generating a query plan provided by an embodiment of the present disclosure;

[0072] Figure 7 A schematic diagram of a meta-learning model training process provided by an embodiment of the present disclosure;

[0073] Figure 8 This is a schematic diagram of a feature extractor provided by an embodiment of the present disclosure for an embodiment of the present application;

[0074] Fig. 9 A schematic diagram of the structure of a query plan generation device provided in an embodiment of the present disclosure;

[0075] Fig.10 A schematic diagram of the structure of a query plan generation device provided in an embodiment of the present disclosure;

[0076] Fig.11 A schematic block diagram of an exemplary electronic device provided for an embodiment of the present disclosure. DETAILED DESCRIPTION

[0077] The following is a description of exemplary embodiments of the present disclosure in conjunction with the accompanying drawings, including various details of the embodiments of the present disclosure to facilitate understanding, which should be considered as merely exemplary. Therefore, it should be recognized by those of ordinary skill in the art that various changes and modifications may be made to the embodiments described herein without departing from the scope and spirit of the present disclosure. Similarly, for the sake of clarity and conciseness, descriptions of well-known functions and structures are omitted in the following description.

[0078] The following describes a query plan generation method, device, electronic device, and storage medium according to an embodiment of the present disclosure with reference to the accompanying drawings.

[0079] Figure 1 A flowchart of a method for generating a query plan provided in an embodiment of the present disclosure.

[0080] like Figure 1 As shown, the method comprises the following steps:

[0081] Step 101: input the parsed structured query language into a query optimizer.

[0082] See also Figure 2 , Figure 2 A framework for generating a query plan provided in an embodiment of the present application, such as Figure 2 As shown, Figure 2 The Structured Query Language (SQL) parser module, other computing nodes, and data backup nodes are omitted.

[0083] like Figure 2As shown in Figure 1, the SQL statement parsed by the parser is input into the query optimizer, which analyzes the query and generates one or more possible execution plans for it. Then, it evaluates the cost of these execution plans and selects the plan with the lowest cost to execute, so as to minimize the query response time and the use of system resources.

[0084] Step 102, generating a predicted resource consumption value for the structured query language based on historical queries according to the meta-learning model in the query optimizer, and generating at least one execution plan according to physical query optimization;

[0085] Please continue reading Figure 2 After the SQL statement is input into the query optimizer, on the one hand, the SQL statement is input into the meta-learning model based on historical query information, and the prediction plan is obtained by using the historical query information; on the other hand, it is input into the logical query optimization module for query rewriting to output the logical execution plan, and at least one execution plan is generated according to the physical query optimization. In some embodiments, the prediction plan includes memory, CPU, I / O, Net consumption, etc., which can be set according to actual needs, and the embodiments of this application are not limited to this.

[0086] Step 103: Obtain a first execution plan from the execution plan, calculate a first resource consumption value of the first execution plan, and calculate a first comparison similarity between the first resource consumption value and the predicted resource consumption value.

[0087] The output of the logical execution plan and the meta-learning model are input into the physical query optimization module. After the query plan cost is estimated, the estimated memory, CPU, I / O, and Net consumption are compared with the consumption estimated based on historical query information through modified cosine similarity to obtain the comparative similarity.

[0088] Step 104: after determining that the first comparison similarity is greater than or equal to the preset similarity threshold, determine that the first execution plan is a target execution plan.

[0089] In some embodiments, the preset similarity threshold may be set by the user. When the similarity of a certain execution plan reaches the set threshold, the execution plan is directly determined as the final execution plan, and the cost estimation of the remaining query plans is not continued.

[0090] The query plan generation method provided by the present disclosure mainly includes the following technical solutions: inputting the parsed structured query language into the query optimizer; generating a predicted resource consumption value for the structured query language based on historical queries according to the meta-learning model in the query optimizer, and generating at least one execution plan according to physical query optimization; obtaining a first execution plan in the execution plan, calculating a first resource consumption value of the first execution plan, and calculating a first comparative similarity between the first resource consumption value and the predicted resource consumption value; after determining that the first comparative similarity is greater than or equal to the preset similarity threshold, determining that the first execution plan is the target execution plan. Compared with the related art, the embodiment of the present application estimates the structured query language based on historical queries through a pre-placed meta-learning model to obtain a predicted resource consumption value, and performs similarity calculation between the predicted resource consumption value and the resource consumption value of the generated execution plan to determine the final target execution plan, thereby reducing the number of cost estimates for the execution plan, accelerating the output speed of the final execution plan, and reducing computational overhead.

[0091] The query optimizer will evaluate the cost of the execution plan and select the plan with the lowest cost to execute in order to minimize the query response time and the use of system resources. Therefore, after the comparison similarity reaches the preset expectation, it stops calculating the resource consumption value of the execution plan and enters the target execution plan. Figure 3 A flowchart of a method for generating a query plan provided by an embodiment of the present disclosure is provided in the following. Figure 3 ,include:

[0092] Step 201: after determining that the first comparison similarity is less than a preset similarity threshold, obtain a second execution plan and calculate a second resource consumption value.

[0093] After determining that the comparison similarity is less than a preset similarity threshold, the second execution plan is sequentially obtained in the execution plan, and the resource consumption value of the second execution plan is calculated.

[0094] Step 202, calculate a second comparative similarity between the second resource consumption value and the predicted resource consumption, compare the second comparative similarity with the preset similarity threshold, and stop calculating the resource consumption value of the execution plan until the comparative similarity is greater than or equal to the similarity threshold.

[0095] The predicted resource consumption and the second resource consumption value are calculated by modified cosine similarity. Cosine similarity measures the similarity between two vectors by measuring the cosine value of the angle between them. It is only related to the direction of the vector. Modified cosine similarity is to subtract the mean of each item so that the result is also related to the length of the vector. It is very suitable for comparing the similarity of two groups of numbers, and its value is [-1,1]. The closer to 1, the more similar. The similarity is named similarity_cost, and the calculation formula is:

[0096]

[0097] in

[0098]

[0099] Among them, predicted I / O cost, predicted CPU cost, predicted memory cost, and predicted Net cost are the predicted resource consumption values ​​output by the meta-learning model, estimated I / O cost, estimated CPU cost, estimated memory cost, and estimated Net cost are the resource consumption values ​​of the second execution plan, ω1, ω2, and ω3 are weight factors, which respectively represent the correlation coefficients of the conversion between CPU, memory, and Net to I / O. The values ​​directly refer to the values ​​of each weight factor when calculating the resource consumption value of the second execution plan.

[0100] When the similarity reaches the preset similarity threshold expect_similarity_cost, that is

[0101] similarity_cost≥expect_similarity_cost

[0102] The current execution plan is used as the final execution plan, and no cost estimation is performed on other plans in the execution plan list.

[0103] When predicting the meta-learning model, input SQL and the current timestamp at the same time to reduce the impact of real-time problems on historical data caused by changes in the system environment. Figure 4 As shown, Figure 4 A flowchart of a method for generating a query plan provided by an embodiment of the present disclosure includes:

[0104] Step 301: input the parsed structured query language and the first timestamp into the query optimizer.

[0105] In some embodiments, the query optimizer timestamp indicates the generation time of the structured query language. The unit of the first timestamp can be set to seconds. Specifically, it can be set according to actual accuracy requirements, and the embodiments of the present application do not limit this.

[0106] Step 302: Search a preset database for a historical execution plan that is close in time to the first timestamp and similar to the structured query language.

[0107] When making predictions, the model tends to favor similar SQL statements with the closest execution time, that is, the memory, CPU, I / O, and Net consumption that are closest to the current system environment status, thereby reducing the impact of real-time problems on historical data caused by changes in the system environment.

[0108] In some embodiments, a historical execution plan with a similar structured query language structure and close to the execution time is obtained according to the first timestamp, and a subsequent prediction task is executed according to the determined historical execution plan.

[0109] Step 303: The meta-learning model predicts the predicted resource consumption value of the structured query language according to the historical execution plan.

[0110] Based on the historical execution plans obtained from the query, including information such as the selection of the execution plan, resource consumption, and execution time, the meta-learning model is trained using historical data to generate predicted resource consumption values.

[0111] When the prediction model is used for prediction, the SQL and the current timestamp are input at the same time. When the model is trained, the timestamp of the actual execution of the historical query is additionally input. When the model makes predictions, it will tend to use the SQL with the closest execution time among similar SQLs, that is, the memory, CPU, I / O, and Net consumption are closest to the current system environment status, thereby reducing the impact of real-time problems on historical data caused by changes in the system environment.

[0112] In some embodiments, after determining that the first execution plan is a target execution plan, the method further includes:

[0113] generating a second timestamp of the target execution plan;

[0114] The target execution plan and the second timestamp are stored in the preset database.

[0115] The SQL executed this time, the execution timestamp, and the actual memory, CPU, I / O, and Net consumption are input into the meta-learning model as training samples to improve the timeliness and reliability of the estimated results of the meta-learning model.

[0116] like Figure 5 As shown, Figure 5 A flowchart of a method for generating a query plan provided by an embodiment of the present disclosure includes:

[0117] Step 401, calculate the prediction similarity of the prediction plan, and determine whether the prediction similarity is greater than or equal to a preset similarity threshold.

[0118] Taking into account that there will be new queries and less historical data in the initial stage of system establishment, there will be no similar SQL, resulting in low credibility of the prediction results. Therefore, when generating the prediction plan, the prediction similarity is generated synchronously, and the preset similarity threshold set by the user is used to determine whether the prediction similarity is greater than or equal to the preset similarity threshold.

[0119] Step 402, when the predicted similarity is greater than or equal to the preset similarity threshold, calculate the first comparative similarity between the first resource consumption value and the predicted resource consumption value, and after determining that the first comparative similarity is greater than or equal to the preset similarity threshold, determine that the first execution plan is the target execution plan.

[0120] When the predicted similarity is greater than or equal to the preset similarity threshold, the predicted plan is used to calculate the similarity compared with the first execution plan. For the specific implementation method, please refer to the description of the above application embodiment, and the present application embodiment will not be described one by one here.

[0121] like Figure 6 As shown, Figure 6 A flowchart of a method for generating a query plan provided by an embodiment of the present disclosure includes:

[0122] Step 501: Obtain the historical execution plan and the target execution plan in the preset database.

[0123] The source domain refers to the data set or task used for training the model. It contains the original data from which we need to extract features or knowledge. In the embodiment of this application, it is the historical execution plan initially collected by the distributed database system. The target domain refers to the new data set or task to which the model is expected to be applied. In the embodiment of this application, it is the target execution plan input into the meta-learning model as a training sample after the SQL is executed, that is, the SQL, execution timestamp, and actual memory, CPU, I / O, and Net consumption.

[0124] Step 502: extracting a first embedding vector of the historical execution plan according to the first feature extractor, and extracting a second embedding vector of the target execution plan according to the second feature extractor.

[0125] See also Figure 7 , Figure 7 A schematic diagram of a meta-learning model training process provided in an embodiment of the present application is shown in FIG. Figure 7 As shown in , the historical query information of the source domain and the target domain are input into two identical lightweight feature extractors to obtain the embedding vectors of the source domain data and the target domain data respectively. Figure 8 , Figure 8 This is a schematic diagram of a feature extractor provided in the embodiment of the present application, such as 8. Figure 7 The lightweight feature extraction network in Figure 8As shown in the figure, the MG block consists of two Ghost modules and a deep convolution layer with a convolution kernel size of 3×3. It follows the inverted residual structure of MobileNetV2. It first uses 1×1 point convolution to expand the low-dimensional tensor to a high-dimensional space, then uses a 3×3 convolution kernel for deep convolution, and finally uses a 1×1 convolution kernel to compress the number of channels to achieve the effect of dimensionality reduction. The convolution kernel size of the ordinary convolution of the first Ghost module is set to 1×1, and the nonlinear activation function ReLU is used. The number of linear transformations s is set to 2, the convolution kernel size is 3×3, and the nonlinear activation function ReLU is also used. Then, the generated phantom features are added to the real features after ordinary convolution; the convolution kernel size of the ordinary convolution of the second Ghost module is set to 1×1. According to the structure of the linear bottleneck layer of MobileNetV2, ReLU is not used but linear transformation is used for dimensionality reduction to save more feature information. The number of linear transformations s is set to 2, the convolution kernel size is 3×3, and then the generated phantom features are added to the real features after ordinary convolution. And considering that the output tensor size is inconsistent with the input tensor size when the convolution step size is 2, the shortcut structure is only used when the convolution step size is 1.

[0126] The structural parameters of MobileNet-Ghost are shown in Table 1, which is a table of the overall network structure parameters of MobileNet-Ghost provided in an embodiment of the present application.

[0127] Table 1

[0128]

[0129] Kernel is the convolution kernel size, Stride is the stride, and Expansion indicates the expansion coefficient of the MG block. For example, if Expansion is 3, the tensor is tripled in the first Ghost module in the MG block. The MobileNet-Ghost network simplifies the overall network structure of MobileNetV2. MobileNetV2 is mainly composed of 7 bottlenecks, while MobileNet-Ghost is mainly composed of 3 MG blocks with Strides of 2, 1, and 2, which greatly reduces the complexity of the network, the number of model parameters, the amount of calculation, the training speed, and the use of system computing resources.

[0130] Step 503, calculating a prototype point according to the first embedding vector, and determining the prototype point to which the target domain data belongs according to the Euclidean distance between the target domain data and the prototype point; wherein different prototype points correspond to different labels.

[0131] Please continue reading Figure 7, calculate the prototype point for the embedding vector of the source domain data.

[0132] Prototype point P k It can be defined as:

[0133]

[0134] where |s k | represents the number of support sets for each class, x i represents the sample of the support set, y i Represents its label. After getting the prototype of each class, Input query set samples into the embedding space.

[0135] After calculating the prototype, the Euclidean distance between the target domain data and the prototype is calculated using the following formula:

[0136]

[0137] According to the calculation results, the target domain data is classified into the category with the closest distance, and the predicted label is output.

[0138] Step 504, calculating a first loss function according to the Euclidean distance, and calculating a second loss function according to the prototype point to which the target domain data belongs, the label of the prototype point to which the target domain data belongs, the first embedding vector and the second embedding vector.

[0139] Please continue reading Figure 7 , calculate the first loss function Loss1:

[0140] Loss1=-logp(y=n|x)

[0141] in

[0142]

[0143] in Represents query features and prototype p n The Euclidean distance between .

[0144] The source domain label, source domain embedding vector, target domain embedding vector, and target domain prediction label are used to calculate the LMMD distance to obtain the second loss function Loss2:

[0145]

[0146] Where λ is the trade-off parameter between domain adaptation loss and classification loss,

[0147]

[0148] in Represent the source domain embedding vector and the target domain embedding vector respectively, and Represent the c-th source domain samples respectively and target domain samples The weight of is defined as follows:

[0149]

[0150] Step 505, calculating a target loss function according to the first loss function and the second loss function; wherein the weights of the first loss function and the second loss function are different.

[0151] Please continue reading Figure 7 , the output loss is:

[0152] Loss=(1-weight)×Loss1+weight×Loss2

[0153] Where weight is the weight.

[0154] Step 506: Perform back propagation according to the target loss function to train the first feature extractor and the second feature extractor.

[0155] Calculate the gradient of the loss function relative to the network output, and then gradually pass it back to each layer of the network. According to the calculated gradient, use an optimization algorithm (such as gradient descent, Adam, etc.) to update the parameters in the network. Specifically, fine-tune each parameter to reduce the value of the loss function. For the specific training process, please refer to any implementation method in the prior art, and the embodiments of this application are not limited to this.

[0156] This proposal uses the prototype network after self-optimization as the meta-learning model. The feature extraction network and loss function are optimized. The feature extraction network uses the self-optimized lightweight network MobileNet-Ghost to improve the model training speed; the loss function combines the adaptive loss, namely the local maximum mean difference (LMMD) loss, on the basis of the cross entropy loss to solve the problem of model domain adaptation and improve the model prediction accuracy.

[0157] Corresponding to the above-mentioned query plan generation method, the present invention also proposes a query plan generation device. Since the device embodiment of the present invention corresponds to the above-mentioned method embodiment, details not disclosed in the device embodiment can be referred to the above-mentioned method embodiment, and will not be repeated in the present invention.

[0158] Fig. 9 A schematic diagram of a query plan generation device provided by an embodiment of the present disclosure is shown in FIG. Fig. 9 As shown, including:

[0159] An input unit 61, used to input the parsed structured query language into a query optimizer;

[0160] A first generating unit 62, configured to generate a predicted resource consumption value for the structured query language based on historical queries according to a meta-learning model in the query optimizer, and to generate at least one execution plan according to physical query optimization;

[0161] A first calculation unit 63, configured to obtain a first execution plan from the execution plan, calculate a first resource consumption value of the first execution plan, and calculate a first comparison similarity between the first resource consumption value and the predicted resource consumption value;

[0162] The determination unit 64 is configured to determine that the first execution plan is a target execution plan after determining that the first comparison similarity is greater than or equal to the preset similarity threshold.

[0163] The query plan generation device provided by the present disclosure has a main technical solution including: inputting the parsed structured query language into the query optimizer; generating a predicted resource consumption value for the structured query language based on historical queries according to the meta-learning model in the query optimizer, and generating at least one execution plan according to physical query optimization; obtaining a first execution plan in the execution plan, calculating a first resource consumption value of the first execution plan, and calculating a first comparative similarity between the first resource consumption value and the predicted resource consumption value; after determining that the first comparative similarity is greater than or equal to the preset similarity threshold, determining that the first execution plan is the target execution plan. Compared with the related art, the embodiment of the present application estimates the structured query language based on historical queries through a pre-placed meta-learning model to obtain a predicted resource consumption value, and performs similarity calculation between the predicted resource consumption value and the resource consumption value of the generated execution plan to determine the final target execution plan, thereby reducing the number of cost estimates for the execution plan, accelerating the output speed of the final execution plan, and reducing computational overhead.

[0164] Furthermore, in a possible implementation of the embodiment of the present disclosure, as Fig.10 As shown, the device also includes:

[0165] A first acquisition unit 65 is configured to, after the first calculation unit 63 calculates a first comparison similarity between the first resource consumption value and the predicted resource consumption value, and after determining that the first comparison similarity is less than a preset similarity threshold, acquire a second execution plan and calculate a second resource consumption value;

[0166] The second calculation unit 66 is used to calculate the second comparative similarity between the second resource consumption value and the predicted resource consumption, and compare the second comparative similarity with the preset similarity threshold until the comparative similarity is greater than or equal to the similarity threshold, and then stop calculating the resource consumption value of the execution plan.

[0167] Furthermore, in a possible implementation of the embodiment of the present disclosure, as Fig.10 As shown, the input unit 61 is also used for:

[0168] Inputting the parsed structured query language and the first timestamp into the query optimizer;

[0169] The first generating unit further includes:

[0170] Searching a preset database for a historical execution plan that is close in time to the first timestamp and similar in language to the structured query language;

[0171] The meta-learning model predicts the predicted resource consumption value of the structured query language according to the historical execution plan.

[0172] Furthermore, in a possible implementation of the embodiment of the present disclosure, as Fig.10 As shown, the device also includes:

[0173] A second generating unit 67, configured to generate a second timestamp of the target execution plan after the determining unit 64 determines that the first execution plan is the target execution plan;

[0174] The storage unit 68 is used to store the target execution plan and the second timestamp in the preset database.

[0175] Furthermore, in a possible implementation of the embodiment of the present disclosure, as Fig.10 As shown, the device also includes:

[0176] A third calculation unit 69 is configured to calculate the prediction similarity of the prediction plan and determine whether the prediction similarity is greater than or equal to a preset similarity threshold before the first calculation unit 63 calculates the first comparison similarity between the first resource consumption value and the predicted resource consumption value;

[0177] The fourth determination unit 610 is used to calculate the first comparative similarity between the first resource consumption value and the predicted resource consumption value when the predicted similarity is greater than or equal to the preset similarity threshold, and after determining that the first comparative similarity is greater than or equal to the preset similarity threshold, determine that the first execution plan is the target execution plan.

[0178] Furthermore, in a possible implementation of the embodiment of the present disclosure, as Fig.10 As shown, the meta-learning model includes a first feature extractor and a second feature extractor, and the device also includes:

[0179] A second acquisition unit 611 is used to acquire the historical execution plan and the target execution plan in the preset database before the first generation unit 62 generates the predicted resource consumption value of the structured query language based on the historical query according to the meta-learning model in the query optimizer;

[0180] A training unit 612, configured to extract a first embedding vector of the historical execution plan according to the first feature extractor, and to extract a second embedding vector of the target execution plan according to the second feature extractor;

[0181] The training unit 612 is further configured to calculate a prototype point according to the first embedding vector, and determine the prototype point to which the target domain data belongs according to the Euclidean distance between the target domain data and the prototype point; wherein different prototype points correspond to different labels;

[0182] The training unit 612 is further configured to calculate a first loss function according to the Euclidean distance, and calculate a second loss function according to the prototype point to which the target domain data belongs, the label of the prototype point to which the target domain data belongs, the first embedding vector, and the second embedding vector;

[0183] The training unit 612 is further configured to calculate a target loss function according to the first loss function and the second loss function; wherein the weights of the first loss function and the second loss function are different;

[0184] The training unit 612 is further used to perform back propagation according to the target loss function to train the first feature extractor and the second feature extractor.

[0185] It should be noted that the above explanation of the method embodiment is also applicable to the device of the embodiment of the present disclosure, and the principle is the same, which is no longer limited in the embodiment of the present disclosure.

[0186] According to an embodiment of the present disclosure, the present disclosure also provides an electronic device, a readable storage medium and a computer program product.

[0187] Fig.11A schematic block diagram of an example electronic device 700 that can be used to implement an embodiment of the present disclosure is shown. The electronic device is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as personal digital processing, cellular phones, smart phones, wearable devices, and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely examples and are not intended to limit the implementation of the present disclosure described and / or required herein.

[0188] like Fig.11 As shown, the device 700 includes a computing unit 701, which can perform various appropriate actions and processes according to a computer program stored in a ROM (Read-Only Memory) 702 or a computer program loaded from a storage unit 708 to a RAM (Random Access Memory) 703. In the RAM 703, various programs and data required for the operation of the device 700 can also be stored. The computing unit 701, the ROM 702, and the RAM 703 are connected to each other via a bus 704. An I / O (Input / Output) interface 705 is also connected to the bus 704.

[0189] A number of components in the device 700 are connected to the I / O interface 705, including: an input unit 706, such as a keyboard, a mouse, etc.; an output unit 707, such as various types of displays, speakers, etc.; a storage unit 708, such as a disk, an optical disk, etc.; and a communication unit 709, such as a network card, a modem, a wireless communication transceiver, etc. The communication unit 709 allows the device 700 to exchange information / data with other devices through a computer network such as the Internet and / or various telecommunication networks.

[0190] The computing unit 701 may be a variety of general and / or special processing components with processing and computing capabilities. Some examples of the computing unit 701 include, but are not limited to, a CPU (Central Processing Unit), a GPU (Graphic Processing Units), various dedicated AI (Artificial Intelligence) computing chips, various computing units running machine learning model algorithms, a DSP (Digital Signal Processor), and any appropriate processor, controller, microcontroller, etc. The computing unit 701 performs the various methods and processes described above, such as a method for generating a query plan. For example, in some embodiments, the method for generating a query plan may be implemented as a computer software program, which is tangibly contained in a machine-readable medium, such as a storage unit 708. In some embodiments, part or all of the computer program may be loaded and / or installed on the device 700 via the ROM 702 and / or the communication unit 709. When the computer program is loaded into the RAM 703 and executed by the computing unit 701, one or more steps of the method described above may be performed. Alternatively, in other embodiments, the computing unit 701 may be configured to execute the aforementioned query plan generation method in any other appropriate manner (eg, by means of firmware).

[0191] Various embodiments of the systems and techniques described above herein may be implemented in digital electronic circuit systems, integrated circuit systems, FPGAs (Field Programmable Gate Arrays), ASICs (Application-Specific Integrated Circuits), ASSPs (Application Specific Standard Products), SOCs (System On Chips), CPLDs (Complex Programmable Logic Devices), computer hardware, firmware, software, and / or combinations thereof. These various embodiments may include: being implemented in one or more computer programs that may be executed and / or interpreted on a programmable system including at least one programmable processor that may be a special purpose or general purpose programmable processor that may receive data and instructions from a storage system, at least one input device, and at least one output device, and transmit data and instructions to the storage system, the at least one input device, and the at least one output device.

[0192] The program code for implementing the method of the present disclosure may be written in any combination of one or more programming languages. These program codes may be provided to a processor or controller of a general-purpose computer, a special-purpose computer, or other programmable data processing device, so that the program code, when executed by the processor or controller, enables the functions / operations specified in the flow chart and / or block diagram to be implemented. The program code may be executed entirely on the machine, partially on the machine, partially on the machine and partially on a remote machine as a stand-alone software package, or entirely on a remote machine or server.

[0193] In the context of the present disclosure, a machine-readable medium may be a tangible medium that may contain or store a program for use by or in conjunction with an instruction execution system, device, or equipment. A machine-readable medium may be a machine-readable signal medium or a machine-readable storage medium. A machine-readable medium may include, but is not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, device, or device, or any suitable combination of the foregoing. More specific examples of machine-readable storage media may include an electrical connection based on one or more lines, a portable computer disk, a hard disk, a RAM, a ROM, an EPROM (Electrically Programmable Read-Only-Memory) or a flash memory, an optical fiber, a CD-ROM (Compact Dis sc Read-Only Memory), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.

[0194] To provide interaction with a user, the systems and techniques described herein can be implemented on a computer having: a display device (e.g., a CRT (Cathode-Ray Tube) or LCD (Liquid Crystal Display) monitor) for displaying information to the user; and a keyboard and a pointing device (e.g., a mouse or a trackball), through which the user can provide input to the computer. Other types of devices can also be used to provide interaction with the user; for example, the feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including acoustic input, voice input, or tactile input).

[0195] The systems and techniques described herein may be implemented in a computing system that includes backend components (e.g., as a data server), or a computing system that includes middleware components (e.g., an application server), or a computing system that includes frontend components (e.g., a user computer with a graphical user interface or a web browser through which a user can interact with implementations of the systems and techniques described herein), or a computing system that includes any combination of such backend components, middleware components, or frontend components. The components of the system may be interconnected by any form or medium of digital data communication (e.g., a communication network). Examples of communication networks include: LAN (Local Area Network), WAN (Wide Area Network), the Internet, and blockchain networks.

[0196] A computer system may include a client and a server. The client and the server are generally remote from each other and usually interact through a communication network. The relationship between the client and the server is generated by computer programs running on the corresponding computers and having a client-server relationship with each other. The server may be a cloud server, also known as a cloud computing server or cloud host, which is a host product in the cloud computing service system to solve the defects of difficult management and weak business scalability in traditional physical hosts and VPS services ("Virtual Private Server", or "VPS" for short). The server may also be a server of a distributed system, or a server combined with a blockchain.

[0197] It should be noted that artificial intelligence is a discipline that studies how computers can simulate certain human thought processes and intelligent behaviors (such as learning, reasoning, thinking, planning, etc.), and includes both hardware-level and software-level technologies. Artificial intelligence hardware technologies generally include technologies such as sensors, dedicated artificial intelligence chips, cloud computing, distributed storage, and big data processing; artificial intelligence software technologies mainly include computer vision technology, speech recognition technology, natural language processing technology, as well as machine learning / deep learning, big data processing technology, knowledge graph technology, and other major directions.

[0198] It should be understood that the various forms of processes shown above can be used to reorder, add or delete steps. For example, the steps recorded in this disclosure can be executed in parallel, sequentially or in different orders, as long as the desired results of the technical solutions disclosed in this disclosure can be achieved, and this document does not limit this.

[0199] The above specific implementations do not constitute a limitation on the protection scope of the present disclosure. It should be understood by those skilled in the art that various modifications, combinations, sub-combinations and substitutions can be made according to design requirements and other factors. Any modification, equivalent substitution and improvement made within the spirit and principle of the present disclosure shall be included in the protection scope of the present disclosure.

Claims

1. A method for generating a query plan, characterized in that: include: Input the parsed structured query language into the query optimizer; Generate a predicted resource consumption value for the structured query language based on historical queries according to a meta-learning model in the query optimizer, and generate at least one execution plan according to physical query optimization; Obtaining a first execution plan from the execution plan, calculating a first resource consumption value of the first execution plan, and calculating a first comparison similarity between the first resource consumption value and the predicted resource consumption value; After determining that the first comparison similarity is greater than or equal to the preset similarity threshold, determining the first execution plan as a target execution plan.

2. The method according to claim 1, characterized in that After calculating a first comparison similarity between the first resource consumption value and the predicted resource consumption value, the method further includes: After determining that the first comparison similarity is less than a preset similarity threshold, obtaining a second execution plan and calculating a second resource consumption value; Calculate a second comparison similarity between the second resource consumption value and the predicted resource consumption, compare the second comparison similarity with the preset similarity threshold, and stop calculating the resource consumption value of the execution plan until the comparison similarity is greater than or equal to the similarity threshold.

3. The method according to claim 1, characterized in that The step of inputting the parsed structured query language into the query optimizer further comprises: Inputting the parsed structured query language and the first timestamp into the query optimizer; The generating a predicted resource consumption value for the structured query language based on historical queries according to the meta-learning model in the query optimizer further includes: Searching a preset database for a historical execution plan that is close in time to the first timestamp and similar in language to the structured query language; The meta-learning model predicts the predicted resource consumption value of the structured query language according to the historical execution plan.

4. The method according to claim 3, characterized in that After determining that the first execution plan is a target execution plan, the method further includes: generating a second timestamp of the target execution plan; The target execution plan and the second timestamp are stored in the preset database.

5. The method according to claim 1, characterized in that After determining that the first comparison similarity is greater than or equal to a preset similarity threshold and before determining that the first execution plan is a target execution plan, the method further includes: Calculating the prediction similarity of the prediction plan, and determining whether the prediction similarity is greater than or equal to a preset similarity threshold; When the predicted similarity is greater than or equal to the preset similarity threshold, the first comparative similarity between the first resource consumption value and the predicted resource consumption value is calculated, and after determining that the first comparative similarity is greater than or equal to the preset similarity threshold, the first execution plan is determined to be the target execution plan.

6. The method according to claim 4, characterized in that The meta-learning model includes a first feature extractor and a second feature extractor. Before generating a predicted resource consumption value for the structured query language based on historical queries according to the meta-learning model in the query optimizer, the method further includes: Obtain the historical execution plan and the target execution plan from the preset database; Extracting a first embedding vector of the historical execution plan according to the first feature extractor, and extracting a second embedding vector of the target execution plan according to the second feature extractor; Calculating a prototype point according to the first embedding vector, and determining the prototype point to which the target domain data belongs according to the Euclidean distance between the target domain data and the prototype point; wherein different prototype points correspond to different labels; Calculating a first loss function according to the Euclidean distance, and calculating a second loss function according to the prototype point to which the target domain data belongs, the label of the prototype point to which the target domain data belongs, the first embedding vector, and the second embedding vector; Calculating a target loss function according to the first loss function and the second loss function; wherein the first loss function and the second loss function have different weights; Back propagation is performed according to the target loss function to train the first feature extractor and the second feature extractor.

7. A query plan generation device, characterized in that: include: An input unit, used for inputting the parsed structured query language into the query optimizer; A first generating unit, configured to generate a predicted resource consumption value for the structured query language based on historical queries according to a meta-learning model in the query optimizer, and to generate at least one execution plan according to physical query optimization; A first calculation unit, configured to obtain a first execution plan from the execution plan, calculate a first resource consumption value of the first execution plan, and calculate a first comparison similarity between the first resource consumption value and the predicted resource consumption value; A determining unit is configured to determine that the first execution plan is a target execution plan after determining that the first comparison similarity is greater than or equal to the preset similarity threshold.

8. An electronic device, characterized in that: include: at least one processor; as well as a memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to perform the method according to any one of claims 1 to 6.

9. A non-transitory computer-readable storage medium storing computer instructions, characterized in that: The computer instructions are used to cause the computer to execute the method according to any one of claims 1-6.

10. A computer program product, characterized in that The invention comprises a computer program which, when executed by a processor, implements the method according to any one of claims 1 to 6.

Images