Query plan acquisition method, electronic equipment, storage medium and program product

Abstract

The invention discloses a query plan acquisition method, electronic equipment, a storage medium and a program product, and belongs to the technical field of databases. The method comprises the steps of generating at least one first partial query plan based on all tables in a query statement, and taking the at least one first partial query plan as a first partial query plan corresponding to a first-step connection operation; a complete second query plan corresponding to the query statement is obtained through a query optimization method, and the selection process of the second query plan corresponds to a plurality of second partial query plans; through the target model, multiple steps of connection operation are circularly executed to obtain a complete first query plan, and in the process of the ith step of connection operation executed through the target model, a second query plan is obtained; and obtaining a first part query plan corresponding to the ith step of connection operation from all candidate part query plans corresponding to each first part query plan obtained by the (i-1) th step of connection operation and all candidate part query plans corresponding to the (i-1) th second part query plan.

Description

Technical Field

[0001] The embodiments of the present application relate to the technical field of databases, and in particular, to a method for obtaining a query plan, an electronic device, a storage medium, and a program product. Background Art

[0002] A query optimizer is a component of a database management system, which is used to stably generate an efficient execution plan for a query statement input into the system. Traditional query optimizers are written manually and determine the generated query plan based on statistical information in the database. With the development of machine learning, in recent years, some query optimizers based on deep reinforcement learning have emerged, using the search of deep reinforcement learning to partially or completely replace the traditional query optimization algorithm to establish an execution plan.

[0003] Query optimization technology is a key issue in the database field, and its advantages and disadvantages directly determine the speed of query execution. The query optimizer needs to search in a very large search space and find an efficient query plan. Currently, the query optimizers of most relational database management systems, that is, the traditional query optimization methods, adopt heuristic methods and various other strategies to achieve a balance between the execution time complexity of query optimization and the execution performance of the query plan, and can achieve stable performance in some environments.

[0004] Traditional query optimization technology uses heuristic methods and rules formulated by experts to establish an execution plan, achieving relatively stable performance. However, the traditional method can only fine-tune the performance by adjusting a small number of parameters and is difficult to generate a relatively efficient query plan in various different scenarios. Query optimization technology based on deep reinforcement learning uses a deep neural network model to replace part or all of the modules in the traditional query optimization technology, achieving the goal of generating a query plan with high performance under a specific data set. Among them, the generative deep query optimization technology refers to a method that directly generates a query plan by continuously selecting components such as the join order and join operator in the query plan and transforming the query optimization problem into a sequence prediction problem.

[0005] However, for new data and query statements, the stability of the query optimization algorithm based on deep reinforcement learning is insufficient, and it is difficult to ensure the stable generation of a good query plan. Summary of the Invention

[0006] The embodiments of the present application provide a method for obtaining a query plan, an electronic device, a storage medium, and a program product, which can solve the problem of insufficient stability of the query optimization algorithm based on deep reinforcement learning.

[0007] In a first aspect, a query plan acquisition method is provided, including: in response to a received query statement, generating at least one first partial query plan based on all tables in the query statement, and using the at least one first partial query plan as the first partial query plan corresponding to the first step of the join operation; obtaining a complete second query plan corresponding to the query statement through a query optimization method, where the selection process of the second query plan corresponds to multiple second partial query plans; obtaining a complete first query plan by repeatedly performing multiple steps of join operations through a target model, where in the process of the i-th step of the join operation performed through the target model, the first partial query plan corresponding to the i-th step of the join operation is obtained from all candidate partial query plans corresponding to each first partial query plan obtained from the (i - 1)-th step of the join operation and all candidate partial query plans corresponding to the (i - 1)-th second partial query plan obtained through the query optimization method, where i = 2, 3, …, N, and N is the total number of steps of the join operation corresponding to the query statement.

[0008] In a second aspect, an electronic device is provided. The electronic device includes a processor and a memory. The memory stores a program or instruction that can run on the processor. When the program or instruction is executed by the processor, the steps of the query plan acquisition method described in the first aspect are implemented.

[0009] In a third aspect, a readable storage medium is provided. A program or instruction is stored on the readable storage medium. When the program or instruction is executed by a processor, the steps of the query plan acquisition method described in the first aspect are implemented.

[0010] In a fourth aspect, a computer program product is provided. The computer program product includes a computer program stored on a non-transitory computer-readable storage medium. The computer program includes program instructions. When the program instructions are executed by a computer, the computer is caused to execute the steps of the query plan acquisition method described in the first aspect.

[0011] In the embodiments of the present application, when repeatedly performing multiple steps of join operations through a target model, in the process of the i-th step of the join operation, the first partial query plan corresponding to the i-th step of the join operation is obtained from all candidate partial query plans corresponding to each first partial query plan obtained from the (i - 1)-th step of the join operation and all candidate partial query plans corresponding to the (i - 1)-th second partial query plan obtained through the query optimization method, where i = 2, 3, …, N, and N is the total number of steps of the join operation corresponding to the query statement. Thus, the traditional query optimization method can be combined with the AI model, and the traditional query optimization method is used to improve the stability of query optimization of the target model.

[0012] It should be understood that the above general description and the following detailed description are only exemplary and explanatory, and cannot limit the present application. Description of the Drawings

[0013] The drawings herein are incorporated into and constitute a part of this specification, showing embodiments consistent with the present application, and are used together with the specification to explain the principles of the present application.

[0014] Figure 1 It shows a flowchart of a query plan acquisition method provided by an embodiment of the present application; Figure 2 It shows a schematic diagram of the execution process of query plan acquisition in an embodiment of the present application; Figure 3 It shows a flowchart of a query plan acquisition method provided by another embodiment of the present application; Figure 4 It shows a schematic diagram of a method for generating a query data set in an embodiment of the present application; Figure 5 It is a block diagram of the structure of an electronic device shown according to an embodiment. Detailed Description of the Embodiments

[0015] Here, exemplary embodiments will be described in detail, and examples thereof are shown in the drawings. When the following description refers to the drawings, unless otherwise indicated, the same numbers in different drawings represent the same or similar elements. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with the present application. On the contrary, they are merely examples of devices and methods consistent with some aspects of the present application as detailed in the appended claims.

[0016] Figure 1 It shows a flowchart of a query plan acquisition method provided by an exemplary embodiment of the present application. This method can be applied to a database management system. For example, a module can be set in the database management system as part of a query optimizer to generate a query plan, or alternatively, a non-invasive method can be adopted, and a query execution hint can be used to control the database management system to execute a query statement. This method can be executed in a hardware environment with a Graphics Processing Unit (GPU).

[0017] As Figure 1 shown, the method mainly includes the following steps: Step S110, in response to the received query statement, generate at least one first partial query plan based on all the tables in the query statement, and use the at least one first partial query plan as the first partial query plan corresponding to the first step of join operation.

[0018] In an embodiment of the present application, after receiving a query statement, all tables corresponding to the query statement can be obtained, and a first partial query plan can be generated for each table, and the at least one first partial query plan is used as the first partial query plan corresponding to the first step of the join operation.

[0019] Step S112, through a query optimization method, obtain a complete second query plan corresponding to the above query statement, where the selection process of the second query plan corresponds to multiple second partial query plans.

[0020] In an embodiment of the present application, a partial query plan refers to a query plan that only specifies a part of the tables in all the tables corresponding to the query statement in the join order. A partial query plan represents an incomplete query plan. In the generation process, it starts from a partial query plan without any table joins, and joins a certain two tables in each step, and finally forms a complete query plan. A complete query plan refers to a query plan whose join order specifies all the tables corresponding to the query statement. The generation process of the complete query plan is: partial query plan 1, partial query plan 2,..., partial query plan N (i.e., the complete query plan).

[0021] In an embodiment of the present application, the above query optimization method may be a traditional query optimization algorithm. For example, the second query plan is a complete query plan generated by a database traditional optimizer, and a heuristic method and rules formulated by experts can be used to establish the query plan. In an embodiment of the present application, the second partial query plan refers to a partial query plan generated during the generation of the second query plan. For example, regarding the second query plan generated by the traditional query optimization method as the result of a linear selection process obtained, where are the above-mentioned multiple second partial query plans.

[0022] Step S114, through the target model, perform multiple steps of join operations in a loop to obtain a complete first query plan.

[0023] In an embodiment of the present application, the first query plan refers to a complete query plan obtained by using the target model; the first partial query plan refers to a partial query plan (i.e., a sub-query plan) generated during the generation of the first query plan.

[0024] In an embodiment of the present application, during the i-th step of the join operation performed through the target model, from all the candidate partial query plans corresponding to each first partial query plan obtained in the (i - 1)-th step of the join operation and all the candidate partial query plans corresponding to the (i - 1)-th second partial query plan, obtain the first partial query plan corresponding to the i-th step of the join operation, where i = 2, 3,..., N, and N is the total number of steps of the join operation corresponding to the query statement.

[0025] In the embodiments of the present application, a connection operation is performed once in each step. Wherein, the connection operation in each step may include one of the following: selecting any two tables from all tables for connection to obtain a multi-table connection result; selecting any two multi-table connection results obtained in previous steps for connection; selecting any unconnected table and a multi-table connection result obtained in any previous step for connection.

[0026] In the embodiments of the present application, the target model may be a deep neural network model, and this deep neural network model adopts a query optimization method based on deep reinforcement learning to obtain a complete first query plan corresponding to the query statement.

[0027] For example, the target model may adopt a reinforcement learning exploration algorithm in the process of generating a query plan. The generation process of each query plan starts from an initial partial query plan (an unfinished query plan), and gradually selects two tables for connection until a complete query plan that connects all the tables in the query statement is obtained. At the data structure level, each partial query plan can be regarded as a forest of query plan trees. For example, when the query contains 5 tables A, B, C, D, and E, its initial partial query plan contains 5 trees, and each tree only contains 1 leaf node A,..., E. In the embodiments of the present application, in each step of the connection operation, the root nodes of two trees are selected for connection to generate a new tree. For example, the subtrees A and B are merged into tree A→①←B, where ① is a new node representing the result of connecting A and B. The generation process of the query plan is a continuous selection process. For example, first select to connect A and B to obtain node ①, then connect ① and C to obtain node ②, and continuously repeat this process until only one tree remains in the forest, that is, all the tables in the query statement have been connected, then the algorithm terminates and outputs the finally generated complete query plan.

[0028] In the embodiments of the present application, the ε-beam search algorithm may be adopted as the reinforcement learning exploration algorithm in the process of generating a query plan. In the ε-beam search algorithm, in each step of the connection operation, for each existing partial query plan all possible next partial query plans are enumerated. After predicting the minimum execution time of each partial query plan through a transformer model , several partial query plans are selected from them as the next step according to the ε-beam search algorithm, and this process is continuously repeated until a complete query plan is generated.

[0029] To improve the stability of the performance of the generated query plan, in the embodiment of the present application, the query plan generated by using the traditional query optimization method is used as one of the search paths. Taking the ε-beam search algorithm as an example, in the process of ε-beam search, in the embodiment of the present application, in each join operation, several partial query plans are retained as search paths, and the next join operation is selected among all possible next partial query plans (i.e., candidate partial query plans) of the partial query plans selected in the current step. For example, in the first step, partial query plans , and are generated, then the partial query plans , and of the join operation generated in the second step are selected from the enumeration results of the above partial query plans (i.e., all candidate partial query plans corresponding to each first partial query plan obtained in the (i - 1)-th join operation). In the embodiment of the present application, while performing the join operation process, the query plan generated by the traditional query optimization method is regarded as the result obtained by a linear selection process , and the partial query plan of each step is added to the corresponding step of the ε-beam search. For example, in the process of the second step, selection can be made either from the enumeration results of or from the enumeration results of (i.e., all candidate partial query plans corresponding to the (i - 1)-th second partial query plan).

[0030] By using the above join operation method provided in the embodiment of the present application, the stability characteristics of the traditional query optimization method are efficiently utilized, and the target model can independently select to adopt the partial query plans generated by itself or the traditional method in the intermediate steps, while ensuring stability and performance.

[0031] In some embodiments, before step S110, the query statement can be feature-represented. The features of the query statement are the common features of each partial query plan in the search process and play a fundamental role in the embodiment of the present application. In the embodiment of the present application, each query statement can be transformed into a heterogeneous query join graph, where each table involved in the query statement is a table node, each column involved in the query statement for a table is a column node, the edges between the table nodes represent the multi-table join conditions between two tables, and each column node has an edge corresponding to the column to the corresponding node of the table where it is located. Among them, in the embodiment of the present application, the following information is extracted for each table as the features of the table node: 1) The number of rows of the table, represented in the form of the logarithm with base 10; 2) The number of rows selected by all single-table selection conditions on the table obtained through traditional cardinality estimation methods, expressed in the form of the logarithm to the base 10, as well as the square root and square of the logarithm; 3) The total selectivity values of all single-table selection conditions on the table, expressed in logarithmic form, where selectivity refers to the ratio of the number of rows of the table filtered by the condition to the total number of rows; 4) The number of foreign keys (out-degree) that reference other tables on the table; 5) The number of foreign keys (in-degree) whose columns contained in the table are referenced by other tables; 6) The number of columns contained in the table.

[0032] Before training the target model, embodiments of this application collect the values of these values through all query plans generated by a traditional query optimizer on the training set, and transform them into an approximate standard normal distribution using a standardization method. In addition, embodiments of this application obtain clustering labels for each table as other features of the table through the K-means clustering method.

[0033] For each column, embodiments of this application extract the following information as features: 1) Relevant information on the column, including whether the column is included in the primary key of the table, whether the column has an index, the null value ratio of the column, the data type of the column, the unique value ratio of the column, the frequent value ratio of the column, etc.; 2) The estimated selectivity value of the single-table selection condition on the column, expressed in the form of a negative logarithm; 3) The encoding of whether there is a multi-table join condition on the column.

[0034] Each edge in the figure represents a multi-table join condition between two tables. For each edge, embodiments of this application extract the following information as features: 1) The number of rows obtained by joining the two tables corresponding to the edge according to their respective single-table selection conditions and multi-table join conditions through traditional cardinality estimation methods, expressed in the form of the logarithm to the base 10, as well as the square root and square of the logarithm; 2) The estimated selectivity value of the combination of all single-table and multi-table conditions for the join of the two tables, expressed as a negative logarithm; 3) The estimated execution cost of the join of the two tables.

[0035] In addition, embodiments of this application establish a learnable vector representation for each table , and design a method for obtaining the vector representation for all tables that do not appear in the training set. For the tables in the training set , embodiments of this application obtain the vector representation of the table through direct training; for the tables not in the training set , in the embodiment of the present application, first, based on the above features, an autoencoder is used to obtain the encoding of each table, then the K-means clustering algorithm is used to obtain the clustering categories of all tables, and then according to the autoencoder vector representation of the table and the training set tables, the learning vector representations of the 3 training set tables with the closest spatial distance to the table are selected. , and the mean value is taken to obtain the representation of the table. . This method can construct a relatively reliable initial vector representation for unseen tables, and can continue to be adjusted on this basis during the fine-tuning process.

[0036] After obtaining all the features, the embodiment of the present application respectively uses 3 different linear layers to align the features of tables, columns, and edges into hidden layer vectors of the same dimension, and uses a graph transformer network to process the query connection graph. The graph transformer network is a graph neural network on a homogeneous graph. Therefore, in the embodiment of the present application, first, the heterogeneous query connection graph is directly transformed into a homogeneous graph, where each edge of the original graph will be transformed into a node, and on the basis that the two table nodes corresponding to the edge in the original graph are connected to each other, both table nodes are connected to the newly added edge node. In the embodiment of the present application, a 3-layer graph transformer model is used to integrate and extract the information in the query connection graph, and obtain the vector representation of each table in the query statement. .

[0037] In some embodiments, for each node of the first part of the query plan, in addition to the corresponding table vector representation extracted during the query statement representation stage by the embodiment of the present application, the following features are used as additional information: 1) The cost estimate of the node, which can be obtained through traditional cost estimation methods; 2) The category of the node, where the node includes leaf nodes (representing tables in the query statement), branch nodes (representing intermediate results of multi-table joins), and feature aggregation nodes (special nodes for aggregating overall information); 3) The height encoding of the node in the query plan tree , in implementation, nodes with heights from 0 to 7 are respectively encoded as , and nodes with heights exceeding 7 are uniformly represented as ; 4) The index usage information of the node, in implementation, 4-bit 0-1 encoding is used. For each leaf node, it is 0. For each branch node, if the child nodes are two branch nodes, or the two nodes cannot be connected using an index, then it is , otherwise, according to the index type that can be used, if it is a multi-column index, the value is determined according to the order of the corresponding columns in the index; for example, for a 3-column index where A.id and A.name can be used for index connection of two columns, the value is , for indexes with more than 4 columns, only the first 3 columns are considered; 5) The non-left-deep degree encoding of the query plan is only used on the feature aggregation nodes. This value is 0 for left-deep query plans, and it is incremented by 1 whenever there is a branch node in the query plan and all its child nodes are branch nodes.

[0038] In some embodiments, during the connection operation of the i-th step by the target model, the method may further include: estimating the cost of each first partial query plan corresponding to the (i - 1)-th step connection operation, and setting the cost estimates of the candidate partial query plans corresponding to the first partial query plans of the (i - 1)-th step connection operation to default values.

[0039] In the above embodiments, in order to further make full use of traditional query optimization algorithms to provide more extensive knowledge for the model, the cost estimation algorithm in the traditional query optimization algorithm can be called to estimate the cost of each first partial query plan. During the connection operation of each step, each first partial query plan of all the next partial query plans will be enumerated, so it is necessary to obtain features for all the enumerated candidate partial query plans for evaluation. However, this feature acquisition mode will cause a large number of calls to the traditional cost estimation algorithm, thus greatly increasing the inference time of query optimization. Therefore, in the above embodiments, a method of moving the execution of cost estimation forward is adopted. In this process, only the current partial query plan is estimated for cost, and in all the next partial query plans, the cost estimates of the new steps will be represented by default values. If the number of tables involved in the query statement is denoted as , using this method can reduce the number of calls to the cost estimation algorithm to , while improving query performance and ensuring a relatively low model inference time.

[0040] In some embodiments, as Figure 2 shown, the target model may include a deep learning query optimizer and may use an attention masking mechanism to enable the target model to directly process tree-shaped partial query plans. The target model can be set to adopt a multi-expert method to enhance the model's ability, enabling the target model to process different types of query plans in different ways. Specifically, in the target model used in the embodiments of the present application, all the fully connected layer modules for connecting two attention modules adopt a multi-expert method. Denote the fully connected layer modules (experts) in a certain layer as , and use a gating network to determine the selection of experts. For the input vector of the network, the output of this part of the network is determined according to the following formula.

[0041]

[0042]

[0043] wherein represents the value of the output of the gating network at the d-th dimension, and represents the set of all values obtained by taking the top k values of . When the target model is in the training phase, random exploration of other experts can be achieved by perturbing the values of the gating network, avoiding the target model repeatedly using the same expert for prediction. For the gating network values of the top k experts, in the embodiments of the present application, with probability their values are randomly exchanged with the values of experts not in the top k experts, so as to simulate the use of the ε-greedy algorithm, and the output result of the target model is obtained through a fully connected (FC) layer from the sequence output from the output end of the gating network. The above algorithm can enable the gating network and the expert model to be trained end to end simultaneously, without the need to specifically train the gating network in an additional step.

[0044] In some embodiments, obtaining the first partial query plan for the i-th join operation from all candidate partial query plans corresponding to the first partial query plan of the (i - 1)-th join operation and all candidate partial query plans corresponding to the (i - 1)-th second partial query plan may include the following steps: Step 1, predicting the execution time values of each candidate partial query plan corresponding to the first partial query plan of the (i - 1)-th join operation and each candidate partial query plan corresponding to the (i - 1)-th second partial query plan, to obtain the predicted execution time of each candidate partial query plan, where the predicted execution time is the shortest execution time predicted for the candidate partial query plan; Step 2, based on the predicted execution times of each candidate partial query plan, obtaining the first partial query plan for the i-th join operation.

[0045] In the above embodiments, the target model may preset the execution time values.

[0046] In some embodiments, to reduce the adverse impact of the direct fitting execution time value on the stability of the target model and make the query optimization performance more stable, a multi-task model based on the fitting of the execution time probability distribution can be adopted, and the execution time value and its probability distribution of each candidate partial query plan are predicted simultaneously. The transformer model in the target model can represent the entire candidate partial query plan with a special feature aggregation node. After the output result of the transformer model, the output vector representations of the feature aggregation node are processed by two different multi-layer linear layer models respectively to obtain the predicted execution time and the predicted class probability of the corresponding candidate partial query plan. Among them, the predicted execution time refers to the prediction of the shortest execution time of the candidate partial query plan, and in implementation, the logarithm to the base 10 of the time in milliseconds can be used; the predicted class probability is the prediction of the specific interval range of the execution time. For example, in implementation, it can be assumed that the logarithm to the base 10 of the execution time of most query plans is in the interval , and this interval is divided into 24 categories in an equidistant manner, and each category represents a range of execution time, and the interval length is . In the embodiments of the present application, it can be assumed that the logarithm of the execution time of each query plan follows a normal distribution with a variance of , and the target of predicting the probability of the corresponding interval range is set according to the normal distribution. For example, denoting the probability density function of the standard normal distribution as , for a query with an execution time of 1000 ms, the prediction of the probability that it is in the interval is calculated using the following method.

[0047]

[0048] In some embodiments, can be set, and this value is approximately equal to the mean of the variances of the query execution times corresponding to all simple query templates in the TPC-DS dataset.

[0049] When obtaining the first partial query plan of the i-th join operation from all candidate partial query plans corresponding to each first partial query plan of the (i - 1)-th join operation and all candidate partial query plans corresponding to the (i - 1)-th second partial query plan, the first partial query plan of the i-th join operation is obtained based on the above predicted execution times of each candidate partial query plan. For example, from each candidate partial query plan corresponding to the first partial query plan of the (i - 1)-th join operation and each candidate partial query plan corresponding to the (i - 1)-th second partial query plan, N candidate partial query plans with the shortest predicted execution times are selected as the first partial query plan of the i-th join operation.

[0050] In some embodiments, after repeatedly performing multiple connection operations through a target model to obtain a complete first query plan, the method may further include: selecting, as the query plan corresponding to the above query request, the query plan with the shortest execution time from the first query plan and the second query plan.

[0051] In the above embodiments, after executing the query plan generation process and generating a complete first query plan s, by comparing the generated query plan with the query plan generated by the traditional method, the query plan with more guaranteed performance is selected, thereby further improving the stability of query optimization performance.

[0052] In some embodiments, the generated query plan and the query plan generated by the traditional method can be compared through predicted probability distributions, and the query plan with the shortest execution time is selected from the first query plan and the second query plan as the query plan corresponding to the above query request. In these embodiments, selecting the query plan with the shortest execution time from the first query plan and the second query plan as the query plan corresponding to the above query request may include the following steps: Step 1, through the target model, predict the probabilities of the execution time values of the first query plan and the second query plan in each interval range, to obtain the predicted category probabilities of the first query plan and the predicted category probabilities of the second query plan, where the predicted category probability is the predicted probability of the execution time in each preset interval range; Step 2, based on the predicted category probabilities of the first query plan and the predicted category probabilities of the second query plan, select the query plan with the shortest execution time from the first query plan and the second query plan as the query plan corresponding to the query request.

[0053] In the above embodiments, the generated query plan and the query plan generated by the traditional method are compared through predicted probability distributions, thereby further improving the stability of query optimization performance.

[0054] In some embodiments, selecting the query plan with the shortest execution time from the first query plan and the second query plan as the query plan corresponding to the above query request based on the predicted category probabilities of the first query plan and the predicted category probabilities of the second query plan may include: Step 1, based on the predicted category probabilities of the first query plan and the predicted category probabilities of the second query plan, obtain the probability that the difference between the execution time of the first query plan and the logarithm of the execution time of the second query plan is a first preset value.

[0055] For example, assume that the query optimization method is called to generate a second query plan , and the first query plan generated by the target model , denote the first query plan by the target model The probability predicted to be the th category is , and the difference between the logarithm of the execution time of the first query plan and the execution time of the second query plan is probability can be calculated using the following formula.

[0056]

[0057] Among them, is the interval length, k is an integer greater than 0, is the probability that the execution time of the first query plan predicted by the target model is in the th interval. For example, = 0.3, indicating that the probability of being in the 12th interval (i.e., the logarithm of the execution time is between 3 and 3.25) is 30%, is the probability that the execution time of the second query plan predicted by the target model is in the th interval, indicates that when the first query plan is in the th interval, the second query plan is in the th interval, and the difference in the logarithm of the execution time between the two is , that is, the execution time difference between the two is times of 10 to the

[0058] Among them, the first preset value is , which can be set according to the actual application.

[0059] Step 2: Based on the probability that the difference between the logarithm of the execution time of the first query plan and the execution time of the second query plan is the first preset value, obtain the expected value of the relative execution time of the first query plan relative to the second query plan.

[0060] In this embodiment, after obtaining the above prediction probability, the expected value of the relative execution time of the query plan can be estimated by the following formula :

[0061] Step 3: Based on the magnitude relationship between the expected value of the relative execution time and the second preset value, select the second query plan or the first query plan as the query plan corresponding to the above query request.

[0062] In some embodiments, the second preset value can be a pre-set value. For example, if the second preset value is 1, when the expected value of the relative execution time is greater than 1 as described above, the second query plan is selected as the query plan corresponding to the above query statement. Wherein, when the expected value of the relative execution time is greater than 1, it means that the execution time of the first query plan is more than 1 times that of the second query plan, that is, the execution time of the first query plan is greater than the execution time of the second query plan. Therefore, the second query plan is selected.

[0063] Of course, it is not limited to this. In practical applications, the second preset value can also be set to other values, such as 0.8 or 1.2, etc., which is specifically determined according to the actual application.

[0064] As Figure 2 shown, when each query statement is input, an initial partial query plan is constructed, and the initial partial query plan is characterized by using the query plan feature representation method of the traditional cost estimation algorithm. The input query statement passes through the deep learning query optimizer, attention module, gated network and fully connected (FC) layer of the target model, and outputs the predicted execution time and execution time probability distribution of the first query plan. For example, in Figure 2 , the predicted value of the execution time of the first query plan is 100 ms, and the probabilities of the execution time of the first query plan in each interval are: 0, 0.11, 0.18, 0.61, 0.07, 0.02, 0.01 and 0. The selection between the first query plan obtained by searching in the target model based on the execution time probability distribution and the second query plan generated by the traditional query optimization method is made according to the above method. Among them, during the process of the target model generating the first query plan, the second partial query plan selected during the process of the traditional query optimization algorithm generating the second query plan will be enumerated and characterized during the search process of the target model generating the first query plan. The enumerated partial query plans are continuously evaluated by the multi-task prediction neural network model based on the transformer architecture, and several better partial query plans are selected from them.

[0065] In some embodiments, as Figure 3 shown, before step S110, the method may further include the following steps: Step S102, obtaining a training data set, where the training data set includes multiple first query statement samples; Step S106, performing data augmentation on the multiple first query statement samples in the training data set to obtain multiple second query statement samples, where the second query statement samples are query statement samples outside the training data set; Step S108, training the target model with the training data set including the multiple first query statement samples and the multiple second query statement samples.

[0066] In the related art, the training data set of a query optimizer adopting a deep query optimization algorithm is mainly developed based on several benchmark sets. The number of tables and query templates included in each data set is limited, and there is still a huge gap from being able to improve the general generalization ability of the model. In view of this problem, in the embodiments of the present application, a method for obtaining more training data based on the existing training data set is provided to increase the amount of training data and improve the general generalization ability of the target model.

[0067] In some embodiments, data augmentation for multiple first query statement samples in the training data set may include: for any first query statement sample in the training data set, randomly deleting at least one table node of the first query statement sample to obtain a second query statement sample.

[0068] For example, randomly select one first query statement from the query statement set of the training data set . Denote the number of tables included in as , and randomly select a value from the set with equal probability. On the premise of ensuring the connectivity of the query connection graph, randomly delete common table nodes in the query connection graph of

[0069] and form a new query statement from the remaining table nodes.

[0070] In other embodiments, data augmentation for multiple first query statement samples in the training data set may include: randomly selecting two first query statement samples from the multiple first query statement samples, and combining the two selected first query statement samples to obtain a second query statement sample. , such that and have a common subgraph and the maximum common subgraph is connected. Merge 's query connection graphs, where the nodes in the common subgraph are shared by both. By calling the pruning method, limit the number of tables in the new query connection graph not to exceed .

[0071] It should be noted that in the embodiments of the present application, the first query statement sample can be data-augmented in the manner of any of the above embodiments, or the first query statement sample can be data-augmented in the manner of both of the above embodiments at the same time.

[0072] Such as Figure 5As shown, two sub-methods of random deletion and random combination can be adopted. In a given query statement dataset (q1, q2, q3, ……, qn), by randomly deleting at least one table node of the query statement sample, new query statement samples are generated. For example, at least one table node in q1 is deleted to generate q1'. Two query statement samples are randomly selected from the given query statement dataset (q1, q2, q3, ……, qn), and the two selected query statement samples are combined to obtain a new query statement sample. For example, q1 and q2 are combined to generate q12. The generated new query statement samples are added to the query statement dataset, thereby expanding the query statement dataset.

[0073] To effectively utilize these new query statements, a model with a relatively small number of parameters can be adopted. The synthetic query statements are used for reinforcement learning training in batches, and the decision-making process and corresponding feedback signals during the reinforcement learning training are recorded. The records of these decision-making processes and corresponding feedback signals are put into the offline reinforcement learning experience and participate in the sampling training of the model as part of the samples.

[0074] In some embodiments, the second query statement obtained based on the first query statement can also be used as a new first query statement to generate a new second query statement. To ensure the diversity of the above query statement generation method and ensure that each query can obtain the opportunity to be randomly selected, in the embodiments of the present application, each first query statement in the training dataset is set with a weight , which is used to determine the probability of each first query statement being randomly selected. The second query statement obtained based on the first query statement can also be used as a new first query statement to generate a new second query statement. Denote the set of multiple first query statements as . At the initial stage of execution, the weights of all first query statements are all set to . Whenever a new second query statement is generated with as the original first query statement , a part of the weight of is respectively divided as the initial weight of the new second query statement , and its value is determined by the following formula.

[0075]

[0076]

[0077]

[0078] Among them, is the retention coefficient of the query statement weight, indicating the proportion of the initial weight of each first query statement that cannot be allocated to the new second query statement. The use of this coefficient makes it more likely that queries with a lower number of randomly generated iterations are always selected. is the weight division coefficient. The larger the value of this coefficient, the greater the weight allocated to the new second query statement in each step. The way of weight division makes the weight of each generated second query statement come from a part of the weight of the first query statement used to generate this second query statement. Its advantage is to avoid the vicious cycle effect that the number of queries with certain specific query statements as the original query statements increases continuously, resulting in an increase in the probability of these queries being selected, and then in turn increasing the number of such queries.

[0079] In some embodiments, since there may not be a common subgraph between two first query statements randomly selected in the combination method, the more tables included in the first query statement, the greater the probability that there is a common subgraph between the two first query statements. Therefore, on the premise of simply randomly reselecting two first query statements when the method execution conditions are not met, the combination method will tend to select the first query statement with a larger number of included tables for merging. Therefore, in the combination method, each first query statement 's random sampling weight changes from to , where represents the number of tables in the query statement .

[0080] To further improve the diversity of the generated query statements, in some embodiments, after obtaining the new second query statement based on the first query statement, the single-table selection conditions included in the first query statement in the training data set are used to randomly replace the conditions on the new second query statement. For example, all the single-table selection conditions in the training data set can be collected first and divided into ① equality conditions, ② inequality conditions, ③ string conditions, and ④ other conditions according to the category of operators. When replacing the single-table conditions in the new second query statement, each condition is randomly replaced with a condition of the same category.

[0081] In some embodiments, a smaller model can be used to collect different query plans and their execution times of query statements, so as to provide rich knowledge for the training of the model. In implementation, a model with a smaller number of parameters can be used. Taking 100 query statements as a batch, query optimization of reinforcement learning is performed on the newly generated query statements respectively, and some query plans , as well as the corresponding shortest execution time To provide additional knowledge for training the model using reinforcement learning experience, the reinforcement learning experience can be preprocessed first, and then a certain proportion of partial query plans in the sampled reinforcement learning experience can be selected during each sampling training. After obtaining the shortest execution time of the partial query plan , and the execution time of the query plan obtained by using the traditional query optimization method for the corresponding query , calculate the relative execution time of the partial query plan , and divide it into 5 categories according to the interval where the value is located: .

[0082] In some embodiments, these partial query plans can be added to the offline reinforcement learning experience pool in a sampling manner. Among them, each query can retain the same number of records of partial query plans, and the partial query plans in each interval are equally selected by random sampling without replacement. When the number of partial query plans in a certain interval is small and cannot be equally selected, after ensuring that each partial query plan contains at least 1 record, the remaining part is filled by sampling with replacement, so that each query can use 100 records.

[0083] During the training of the target model, samples can be randomly selected from the offline reinforcement learning experience pool to train the target model. The embodiments of the present application adopt the method of setting a random sampling ratio to balance the offline reinforcement learning experience pool and the experience obtained during the training process. Each time during training, 75% of the samples in the sample set come from the experience obtained during the training process, while 25% come from the offline reinforcement learning experience pool. This training method enables the target model to be continuously adjusted by the samples in the offline reinforcement learning experience pool, while learning generalization knowledge from diverse offline reinforcement learning experiences and avoiding overfitting of the model.

[0084] In some embodiments, training the target model using a training data set including multiple first query statement samples and multiple second query statement samples may include the following steps: Step 1, input the training data set into the target model to obtain the predicted execution time and predicted execution probability of the query plan corresponding to the training data set; Step 2, aiming at making the prediction result of the target model the same as the normal distribution corresponding to the logarithm of the execution time of the query plan, adjust the parameters of the target model, and repeat the iterative training of the target model multiple times, where the training end condition satisfied by the target model after training.

[0085] In the embodiments of the present application, the target model makes predictions for execution time and probability distribution respectively. Therefore, two different loss functions can be used for training. Among them, the prediction of execution time belongs to a regression task, and the mean squared error loss function can be used to learn this task. Denote the true value of the logarithm of the execution time as For each sample The corresponding loss function value is calculated using the following method.

[0086]

[0087] Where represents the predicted value of the execution time under the model parameters .

[0088] Correspondingly, the categorical probability prediction task belongs to a classification task, and the Kullback-Leibler divergence loss function can be used to learn this task. Denote the true values of the categorical probabilities of the execution time as For each sample The corresponding loss function value is calculated using the following method.

[0089]

[0090] Where represents the predicted probability of the th category under the model parameters .

[0091] Taking a complete traversal of all query statements in the training data set as one round. For each query plan generation in each round, in the embodiments of the present application, after the generation is completed, the query plan and the partial query plans of all its predecessors can be recorded in the experience replay cache, and its corresponding minimum execution time can be updated. Thereafter, samples are sampled from the experience replay cache for training. Denote the set of all sampled samples as The following loss function is used for training in each iteration.

[0092]

[0093] During the training process, the number of sampling training times after each generation is continuously adjusted as the number of training rounds increases, so as to improve the sample utilization efficiency. For example, 1 sampling training can be performed after each generation in the first 8 rounds; 2 training times can be performed after each generation in the 9th to 16th rounds; and 3 training times can be performed after each generation thereafter. This configuration helps to more effectively utilize the samples in the experience replay cache. The training process can adopt optimization methods such as Adaptive Moment Estimation (Adam), use learning rate, and adjust it to , after the 32nd round, it is adjusted to .

[0094] After training reaches a certain number of rounds, the target model adjusts its parameters through the Low-rank Adaptation (LoRA) method. The low-rank adaptation method is a fine-tuning method commonly used for models with a large number of parameters, which can fit the training tasks while better retaining the original capabilities of the model. For the specified parameter matrix in the target model, let its size be , the low-rank adaptation method uses two low-dimensional matrices and with sizes of to adjust the parameter , obtaining a new parameter . At this time, the parameter is called the rank of the low-rank adaptation method.

[0095]

[0096] When is fixed during the training process and only is adjusted, this adjustment method can achieve better performance with a relatively small number of parameters without changing the model network structure. For example, in the embodiments of the present application, the low-rank adaptation method training for the graph transformer sub-model can start from the 8th round with a rank of 32, the rank can be modified to 8 starting from the 16th round, and the low-rank adaptation method training is performed on both the graph transformer and the transformer in the target model starting from the 32nd round to achieve a more refined training effect.

[0097] In the embodiments of the present application, through the prior assumption that the logarithm of the query plan execution time follows a normal distribution, the target model (i.e., the deep query optimization model) is trained for multiple tasks, so that the sensitivity to extreme values during the reinforcement learning exploration process can be reduced during the training phase, and at the same time, a prediction of the execution time probability distribution can be generated.

[0098] In some embodiments, after the target model is fully trained on the given training set according to the above process, the target model can be further adapted to a new query data set in a fine-tuning manner. For example, the target model can be trained according to the above process using data set A and fine-tuned on data set B. In order to retain the knowledge obtained by the target model during pre-training, the values of other parameters can be fixed during the fine-tuning process, and the low-rank adaptation method is used to fine-tune the learnable vector representation of the table, the graph transformer parameters, the transformer parameters, and the linear layer of the model tail prediction value. In specific applications, the low-rank adaptation method with a rank of 64 can be used, and learning rate, and reduce it to of the original learning rate every 8 rounds during the fine-tuning process, and finally reduce it to . Data augmentation is not used during the model fine-tuning process, and other parts are kept consistent with the pre-training stage.

[0099] Figure 5 FIG. shows a block diagram of an electronic device 500 according to an exemplary embodiment of the present application. The electronic device can be implemented as the cloud terminal management platform in the above solution of the present application or a cloud server with cloud terminals installed. The electronic device 500 includes a central processing unit (CPU) 501, a system memory 504 including a random access memory (RAM) 502 and a read-only memory (ROM) 503, and a system bus 505 connecting the system memory 504 and the central processing unit 501. The electronic device 500 further includes a mass storage device 506 for storing an operating system 509, a client 510, and other program modules 511.

[0100] Without loss of generality, the computer-readable medium may include a computer storage medium and a communication medium. The computer storage medium includes volatile and non-volatile, removable and non-removable media implemented by any method or technology for storing information such as computer-readable instructions, data structures, program modules, or other data. The computer storage medium includes RAM, ROM, erasable programmable read-only registers (EPROM), electrically erasable programmable read-only memories (EEPROM), flash memory or other solid-state storage technologies, CD-ROM, digital versatile discs (DVD) or other optical storage, magnetic tape cartridges, tapes, disk storage or other magnetic storage devices. Of course, those skilled in the art will appreciate that the computer storage medium is not limited to the above several. The above system memory 504 and mass storage device 505 can be collectively referred to as memory.

[0101] According to various embodiments of the present disclosure, the electronic device 500 can also run on a remote computer connected to the network via a network such as the Internet. That is, the electronic device 500 can be connected to the network 508 through a network interface unit 507 connected to the system bus 505, or in other words, the network interface unit 507 can also be used to connect to other types of networks or remote computer systems (not shown).

[0102] The memory further includes at least one instruction, at least one program, a code set or an instruction set, which are stored in the memory, and the central processing unit 501 implements all or part of the steps in the query plan acquisition method shown in the above various embodiments by executing the at least one instruction, at least one program, the code set or the instruction set.

[0103] Those skilled in the art can understand that Figure 5 the structure shown in does not constitute a limitation on the electronic device 500, and it may include more or fewer components than shown in the figure, or combine certain components, or adopt a different component layout.

[0104] In an exemplary embodiment, a readable storage medium is further provided, on which a program or an instruction is stored, and when the program or the instruction is executed by a processor, all or part of the steps in the above query plan acquisition method are implemented. For example, the readable storage medium may be a read-only memory (ROM), a random access memory (RAM), a compact disc read-only memory (CD-ROM), a magnetic tape, a floppy disk, an optical data storage device, etc.

[0105] In an exemplary embodiment, a computer program product is further provided, which includes a computer program stored on a non-transitory computer-readable storage medium, and the computer program includes program instructions. When the program instructions are executed by a computer, the computer is enabled to execute all or part of the steps in the above query plan acquisition method.

[0106] After considering the specification and practicing the invention disclosed herein, those skilled in the art will readily conceive of other embodiments of the present application. The present application is intended to cover any variations, uses, or adaptations of the present application, which follow the general principles of the present application and include known common general knowledge or conventional technical means in the technical field not disclosed in the present application. The specification and the embodiments are only regarded as exemplary, and the true scope and spirit of the present application are pointed out by the claims.

[0107] It should be understood that the present application is not limited to the exact structure already described and shown in the drawings, and various modifications and changes can be made without departing from its scope. The scope of the present application is only limited by the appended claims.

Claims

1. A query plan acquisition method, characterized in that Including: In response to the received query statement, at least one first partial query plan is generated based on all the tables in the query statement, and the at least one first partial query plan is used as the first partial query plan corresponding to the first step of the join operation; Through a query optimization method, a complete second query plan corresponding to the query statement is obtained, where the selection process of the second query plan corresponds to multiple second partial query plans; Through the target model, multiple steps of join operations are cyclically executed to obtain a complete first query plan. Among them, during the execution of the i-th step of the join operation by the target model, from all the candidate partial query plans corresponding to each first partial query plan obtained in the (i - 1)-th step of the join operation and all the candidate partial query plans corresponding to the (i - 1)-th second partial query plan, the first partial query plan corresponding to the i-th step of the join operation is obtained, where i = 2, 3, …, N, and N is the total number of steps of the join operation corresponding to the query statement.

2. The method according to claim 1, wherein, During the execution of the i-th step of the join operation by the target model, the method further includes: Performing cost estimation on each first partial query plan corresponding to the (i - 1)-th step of the join operation, and setting the cost estimation of each candidate partial query plan corresponding to each first partial query plan of the (i - 1)-th step of the join operation to a default value.

3. The method according to claim 1, wherein Obtaining the first partial query plan for the i-th step of the join operation from all the candidate partial query plans corresponding to each first partial query plan of the (i - 1)-th step of the join operation and all the candidate partial query plans corresponding to the (i - 1)-th second partial query plan includes: Predicting the execution time values of each candidate partial query plan corresponding to the first partial query plan of the (i - 1)-th step of the join operation and each candidate partial query plan corresponding to the (i - 1)-th second partial query plan to obtain the predicted execution time of each candidate partial query plan, where the predicted execution time is the shortest predicted execution time of the candidate partial query plan; Based on the predicted execution time of each candidate partial query plan, the first partial query plan for the i-th step of the join operation is obtained.

4. The method according to claim 1, wherein After obtaining the complete first query plan by cyclically executing multiple steps of join operations through the target model, the method further includes: Selecting the query plan with the shortest execution time from the first query plan and the second query plan as the query plan corresponding to the query request.

5. The method according to claim 4, characterized in that The step of selecting the query plan with the shortest execution time from the first query plan and the second query plan as the query plan corresponding to the query request includes: Through the target model, predicting the probabilities of the execution time values of the first query plan and the second query plan in each interval range to obtain the predicted class probability of the first query plan and the predicted class probability of the second query plan, where the predicted class probability is the predicted probability of the execution time in each preset interval range; Based on the predicted class probabilities of the first query plan and the predicted class probabilities of the second query plan, select the query plan with the shortest execution time from the first query plan and the second query plan as the query plan corresponding to the query request.

6. The method according to claim 5, wherein The step of selecting the query plan with the shortest execution time from the first query plan and the second query plan as the query plan corresponding to the query request based on the predicted class probabilities of the first query plan and the predicted class probabilities of the second query plan includes: Based on the predicted class probabilities of the first query plan and the predicted class probabilities of the second query plan, obtain the probability that the difference between the execution time of the first query plan and the logarithm of the execution time of the second query plan is a first preset value; Based on the probability that the difference between the execution time of the first query plan and the logarithm of the execution time of the second query plan is a first preset value, obtain the expected value of the relative execution time of the first query plan with respect to the second query plan; Based on the magnitude relationship between the expected value of the relative execution time and a second preset value, select the second query plan or the first query plan as the query plan corresponding to the query request.

7. The method according to any one of claims 1 to 6, characterized in that, Before responding to the received query statement, the method further includes: Obtain a training data set, where the training data set includes multiple first query statement samples; Perform data augmentation on the multiple first query statement samples in the training data set to obtain multiple second query statement samples, where the second query statement samples are query statement samples outside the training data set; Train the target model using the training data set including the multiple first query statement samples and the multiple second query statement samples.

8. The method according to claim 7, wherein The step of performing data augmentation on the multiple first query statement samples in the training data set includes: For any one of the multiple first query statement samples, randomly delete at least one table node of the first query statement sample to obtain one second query statement sample; and / or, Randomly select two of the multiple first query statement samples, and combine the two selected first query statement samples to obtain one second query statement sample.

9. The method according to claim 7, wherein Training the target model using the training data set including the multiple first query statement samples and the multiple second query statement samples includes: Input the training data set into the target model to obtain the predicted execution time and predicted execution probability of the query plan corresponding to the training data set; Taking the same normal distribution as the target between the prediction result of the target model and the logarithm of the execution time of the query plan, adjust the parameters of the target model, and repeat the iterative training of the target model multiple times, where the training end condition is satisfied after training.

10. An electronic device, characterized in that, The electronic device includes a processor and a memory. The memory stores programs or instructions that can run on the processor. When the programs or instructions are executed by the processor, the steps of the query plan acquisition method described in any one of claims 1 to 9 are implemented.

11. A readable storage medium, characterized in that, Programs or instructions are stored on the readable storage medium. When the programs or instructions are executed by a processor, the steps of the query plan acquisition method described in any one of claims 1 to 9 are implemented.

12. A computer program product, the computer program product includes a computer program stored on a non-transitory computer-readable storage medium. The computer program includes program instructions. When the program instructions are executed by a computer, the computer is caused to execute the steps of the query plan acquisition method described in any one of claims 1 to 9.

Images