Database query task execution method and device, equipment and storage medium

Abstract

The invention discloses a database query task execution method and device, equipment and a storage medium, and relates to the technical field of databases, and the method comprises the following steps: receiving a database query statement corresponding to a current database query task; querying a target execution plan corresponding to the database query statement by using a target execution plan query model obtained based on exploration utilization balance reinforcement learning; wherein the target execution plan is an execution plan in an execution plan set generated according to the target prompt set, and the target prompt set is a prompt set in prompt sets constructed according to preset query prompt set parameters; and executing the current database query task according to the target execution plan. According to the method, the execution time consumption of the database query task is greatly reduced, and the applicability to the database query task is greatly improved.

Description

Technical Field

[0001] The present application relates to the field of database technology, and in particular to a method, device, equipment and computer-readable storage medium for executing a database query task. Background Art

[0002] Database query task execution is the core link in the database management system, and its efficiency directly affects the performance of the entire system and user experience.

[0003] Traditional query optimizers rely on cardinality estimators, plan enumerators, and cost models to generate candidate execution plans and then select the final execution plan. There are currently two commonly used methods for optimizing database query task execution. One is to learn the entire query optimizer, and the output of the model is an execution plan; the other is to learn some components of the query optimizer or parameter tuning methods. However, both methods have their own shortcomings. First, the method of learning the entire query optimizer takes too long to train due to the inherent complexity and variability of query optimization problems; second, learning some components of the query optimizer or parameter tuning methods has obvious limitations in adaptability.

[0004] In summary, how to effectively solve the problems of the current database query task execution optimization method, long training time, limited adaptability, etc., is an urgent problem that technical personnel in this field need to solve. Summary of the invention

[0005] The purpose of this application is to provide a method for executing a database query task, which greatly reduces the time consumption of executing a database query task and greatly improves the applicability of the database query task; another purpose of this application is to provide a database query task execution device, equipment and computer-readable storage medium.

[0006] In order to solve the above technical problems, this application provides the following technical solutions:

[0007] A method for executing a database query task, comprising:

[0008] Receive the database query statement corresponding to the current database query task;

[0009] The target execution plan query model obtained by exploring and utilizing balanced reinforcement learning is used to query the target execution plan corresponding to the database query statement; wherein the target execution plan is an execution plan in an execution plan set generated according to a target prompt set, and the target prompt set is a prompt set in each prompt set constructed according to preset query prompt set parameters;

[0010] Execute the current database query task according to the target execution plan.

[0011] In a specific implementation of the present application, a training process of the target execution plan query model is also included, and the training process of the target execution plan query model includes:

[0012] Collect a sample set of database query statements;

[0013] Get each preset query prompt set parameter;

[0014] Construct each prompt set according to each preset query prompt set parameter;

[0015] Randomly select a current database query statement sample from the database query statement sample set;

[0016] Using a preset execution plan query model to explore and utilize balanced reinforcement learning based on a multi-armed bandit algorithm, a current prompt set corresponding to the current database query statement sample is obtained from each prompt set;

[0017] Generate an execution plan set corresponding to the current prompt set, and determine the current reward value corresponding to each execution plan in the execution plan set;

[0018] Obtaining each current network weight of the preset execution plan query model;

[0019] Update the current network weights according to the current reward values ​​to obtain the updated network weights;

[0020] Determine whether each current network weight reaches a preset convergence condition according to each current network weight and each updated network weight;

[0021] If yes, then determining the preset execution plan query model corresponding to each current network weight as the target execution plan query model;

[0022] If not, then each current network weight in the preset execution plan query model is replaced with each updated network weight to obtain a new preset execution plan query model, and the step of randomly selecting a current database query statement sample from the database query statement sample set is repeated.

[0023] In a specific implementation of the present application, generating an execution plan set corresponding to the current prompt set includes:

[0024] Parsing the current database query statement sample into parsing trees reflecting the grammatical structure of the current database query statement sample according to the current prompt set;

[0025] Convert each parse tree into each query tree; wherein each query tree decomposes the query into each logical operation, and maps each logical operation to the data structure of the database;

[0026] Convert each query tree into each execution plan tree; wherein each node in each execution plan tree is a logical operation step, and the connection relationship between each node is used to define the execution order and data flow of each logical operation step;

[0027] The execution plan set is generated according to each execution plan tree.

[0028] In a specific implementation of the present application, after converting each query tree into each execution plan tree, it also includes:

[0029] Each execution plan tree is converted into a binary tree; wherein each node of each binary tree uses one-hot encoding to record node information.

[0030] In a specific implementation of the present application, determining the current reward value corresponding to each execution plan in the execution plan set includes:

[0031] A tree convolutional neural network is used to calculate the current reward value corresponding to each execution plan in the execution plan set.

[0032] In a specific implementation of the present application, querying the target execution plan corresponding to the database query statement using the target execution plan query model obtained by exploring and utilizing balanced reinforcement learning includes:

[0033] The target execution plan query model is used to query the target execution plan corresponding to the database query statement determined by the accumulated reward value.

[0034] In a specific implementation of the present application, a process of determining a target execution plan corresponding to the database query statement determined by the accumulated reward value is also included, and the process of determining a target execution plan corresponding to the database query statement determined by the accumulated reward value includes:

[0035] When each current network weight reaches the preset convergence condition, obtaining a target prompt set corresponding to the database query statement;

[0036] Obtain the cumulative reward values ​​corresponding to each execution plan corresponding to the target prompt set;

[0037] Calculate the selection probability of each execution plan according to each accumulated reward value;

[0038] The execution plan corresponding to the maximum value among the selected probabilities is determined as the target execution plan.

[0039] A database query task execution device, comprising:

[0040] A query statement receiving module is used to receive a database query statement corresponding to the current database query task;

[0041] A target execution plan query module is used to query the target execution plan corresponding to the database query statement using a target execution plan query model obtained based on exploration and balanced reinforcement learning; wherein the target execution plan is an execution plan in an execution plan set generated according to a target prompt set, and the target prompt set is a prompt set in each prompt set constructed according to preset query prompt set parameters;

[0042] The database query task execution module is used to execute the current database query task according to the target execution plan.

[0043] A database query task execution device, comprising:

[0044] Memory for storing computer programs;

[0045] The processor is used to implement the steps of the database query task execution method as described above when executing the computer program.

[0046] A computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the steps of the database query task execution method described above are implemented.

[0047] The database query task execution method provided in the present application receives a database query statement corresponding to the current database query task; uses a target execution plan query model obtained by exploring and utilizing balanced reinforcement learning to query a target execution plan corresponding to the database query statement; wherein the target execution plan is an execution plan in an execution plan set generated according to a target prompt set, and the target prompt set is a prompt set in each prompt set constructed according to preset query prompt set parameters; and executes the current database query task according to the target execution plan.

[0048] It can be seen from the above technical solution that the target execution plan query model obtained based on exploration-utilization balance reinforcement learning achieves a balance between exploration and utilization. When receiving the database query statement corresponding to the current database query task, the target execution plan query model can query the target prompt set from each prompt set constructed according to the preset query prompt set parameters, and find the target execution plan from each execution plan generated according to the target prompt set. The quality of prompt set selection is improved, the rational use of prompt sets is achieved, the accuracy of execution plan selection is improved, and the execution time of database query tasks is greatly reduced. The target execution plan query model is not restricted to database query statements, which greatly improves its applicability to database query tasks.

[0049] Correspondingly, the present application also provides a database query task execution device, equipment and computer-readable storage medium corresponding to the above-mentioned database query task execution method, which has the above-mentioned technical effects and will not be repeated here. BRIEF DESCRIPTION OF THE DRAWINGS

[0050] In order to more clearly illustrate the technical solutions in the embodiments of the present application or the related technologies, the drawings required for use in the embodiments or the related technical descriptions are briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0051] Figure 1 This is an implementation flow chart of the database query task execution method in the embodiment of the present application;

[0052] Figure 2 Another implementation flow chart of the method for executing a database query task in an embodiment of the present application;

[0053] Figure 3 This is a schematic diagram of the structure of an execution plan tree in an embodiment of the present application;

[0054] Figure 4 This is a schematic diagram of the structure of a binary tree in an embodiment of the present application;

[0055] Figure 5 This is a schematic diagram of the structure of a query tree with different connections in an embodiment of the present application;

[0056] Figure 6 This is a schematic diagram of the structure of a feature vector tree corresponding to query trees with different connections in an embodiment of the present application;

[0057] Figure 7 Schematic diagram of the structure of a tree convolution filter in an embodiment of the present application;

[0058] Figure 8 A schematic diagram of the root node output results of a query tree with different connections in an embodiment of the present application;

[0059] Fig. 9 It is an overall technical roadmap in the embodiments of this application;

[0060] Fig.10 This is a structural block diagram of a database query task execution device in an embodiment of the present application;

[0061] Fig.11 This is a structural block diagram of a database query task execution device in an embodiment of the present application;

[0062] Fig.12A schematic diagram of the specific structure of a database query task execution device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0063] In order to enable those skilled in the art to better understand the present application, the present application is further described in detail below in conjunction with the accompanying drawings and specific implementation methods. Obviously, the described embodiments are only part of the embodiments of the present application, rather than all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in the field without making creative work are within the scope of protection of the present application.

[0064] See also Figure 1 , Figure 1 This is an implementation flow chart of a method for executing a database query task in an embodiment of the present application. The method may include the following steps:

[0065] S101: Receive a database query statement corresponding to a current database query task.

[0066] After the database query task is generated, a database query statement corresponding to the current database query task is sent to a database query task execution center, and the database query task execution center receives the database query statement corresponding to the current database query task.

[0067] S102: Query the target execution plan corresponding to the database query statement using the target execution plan query model obtained based on exploration and balanced reinforcement learning.

[0068] The target execution plan is an execution plan in an execution plan set generated according to the target prompt set, and the target prompt set is a prompt set in prompt sets constructed according to preset query prompt set parameters.

[0069] The target execution plan query model is obtained in advance based on exploration and balanced reinforcement learning. The target execution plan query model can query the target execution plan corresponding to any database query statement. After receiving the database query statement corresponding to the current database query task, the database query task execution center uses the target execution plan query model obtained based on exploration and balanced reinforcement learning to query the target execution plan corresponding to the database query statement.

[0070] The database query task execution center may be a processor, server, etc. deployed with a target execution plan query model obtained by exploring and utilizing balanced reinforcement learning.

[0071] S103: Execute the current database query task according to the target execution plan.

[0072] The found target execution plan may include the steps for executing the current database query task and the sequential connection relationship between the steps. After the target execution plan corresponding to the database query statement is found, the current database query task is executed according to the target execution plan.

[0073] It can be seen from the above technical solution that the target execution plan query model obtained based on exploration-utilization balance reinforcement learning achieves a balance between exploration and utilization. When receiving the database query statement corresponding to the current database query task, the target execution plan query model can query the target prompt set from each prompt set constructed according to the preset query prompt set parameters, and find the target execution plan from each execution plan generated according to the target prompt set. The quality of prompt set selection is improved, the rational use of prompt sets is achieved, the accuracy of execution plan selection is improved, and the execution time of database query tasks is greatly reduced. The target execution plan query model is not restricted to database query statements, which greatly improves its applicability to database query tasks.

[0074] It should be noted that, based on the above embodiments, the embodiments of the present application also provide corresponding improved solutions. In the subsequent embodiments, the same steps or corresponding steps as those in the above embodiments can be referenced to each other, and the corresponding beneficial effects can also be referenced to each other, which will not be repeated one by one in the following improved embodiments.

[0075] See also Figure 2 , Figure 2 This is another implementation flow chart of the method for executing a database query task in an embodiment of the present application. The method may include the following steps:

[0076] S201: Collect a sample set of database query statements.

[0077] Before model training, a database query statement sample set is collected in advance, and the database query statement sample set includes multiple database query statement samples for performing different database queries.

[0078] S202: Obtain parameters of each preset query prompt set.

[0079] Preset various query hint set parameters, which may include enabling sequential scan (enable_seqscan), enabling index-based scan (enable_indexscan), enabling index-only scan (enable_indexonlyscan), enabling hash join (enable_hashjoin), enabling merge join (enable_mergejoin), and enabling nested loop join (enable_nestloop). Get various preset query hint set parameters.

[0080] Controlling the use of sequential scans is a sequential scan that traverses the entire table to find matching rows. For small tables or queries with few filter conditions, this may be the fastest method. However, when processing large data sets, if sequential scans are used inappropriately, it may cause performance problems.

[0081] Controlling index-based scans is when the query conditions match the index columns, index scans enable the database to quickly locate the required rows directly, usually more efficiently than sequential scans. This approach is particularly suitable for point queries or range queries on large tables.

[0082] Enabling index-only scan means that when a query only needs to access columns already in the index, this scan can be used to avoid accessing table data and further improve query efficiency.

[0083] Controlling the use of hash joins is suitable for situations where the join columns do not have indexes, and the join operation is accelerated by building a hash table. This method performs well when handling large-scale joins, especially when the size of the join result set is moderate.

[0084] A merge join is an efficient join method when both tables are sorted by the join key. It traverses both tables at the same time to find matching rows, and is very efficient for large, pre-sorted data sets.

[0085] The use of a controlled nested loop join is the simplest join algorithm and is suitable for situations where at least one of the tables participating in the join is very small. Although it is usually not the best choice when dealing with large data sets, it may still be effective in some specific scenarios.

[0086] S203: Constructing each prompt set according to each preset query prompt set parameter.

[0087] After obtaining the parameters of each preset query hint set (HS), each hint set is constructed according to the parameters of each preset query hint set. For example, each query hint set parameter can be binary-encoded. For example, when the number of preset query hint set parameters is 6, the number of hint sets constructed is 2. 6 , 64 in total.

[0088] A query hint set refers to a set of instructions or comments that can be used in a Structured Query Language (SQL) query to provide suggestions or mandatory commands to the database query optimizer on how to execute the query. These hints do not change the results returned by the query, but guide the query optimizer to take specific actions to influence the selection of the execution plan. These actions may include choosing to use a specific index, changing the connection strategy, forcing a specific query path, etc. The query hint set in different database management systems (DBMS) may not be exactly the same. Hint sets are a common tool for database administrators (DBAs) to control and guide the database query optimization process. By configuring the corresponding hint set, the database administrator can exert a direct influence on the behavior of the query optimizer, thereby improving the selection of query execution plans, execution paths, performance, etc. This method allows database administrators to manually intervene when they know that the query optimizer may not generate the optimal execution plan, ensuring that key query tasks can be executed in the most efficient way.

[0089] S204: Randomly select a current database query statement sample from the database query statement sample set.

[0090] After the database query statement sample set is collected, a current database query statement sample is randomly selected from the database query statement sample set.

[0091] S205: Using a preset execution plan query model to explore and utilize balanced reinforcement learning based on a multi-armed bandit algorithm, a current prompt set corresponding to the current database query statement sample is obtained from each prompt set.

[0092] After constructing each prompt set, the preset execution plan query model is used to explore and utilize balanced reinforcement learning based on the multi-armed bandit algorithm to filter out the current prompt set corresponding to the current database query statement sample from each prompt set.

[0093] The multi-armed bandit algorithm can include algorithm, UCB algorithm, Thompson sampling algorithm, etc. When selecting the multi-armed bandit algorithm, you can select the algorithm based on the advantages and disadvantages of each algorithm in different scenarios. For example, when the system load is light, use more exploratory algorithms (such as Thompson sampling algorithm or In database query optimization, different prompt set selection algorithms are used in combination through multi-strategy fusion methods to find a better balance between exploration and utilization, thus improving the robustness of prompt set selection.

[0094] In the early stage of model training, an exploration strategy is adopted to randomly select different query prompt sets to collect performance data of various prompt sets in actual environments, and the performance data is used as historical performance data to guide the selection of prompt sets.

[0095] S206: Generate an execution plan set corresponding to the current prompt set, and determine the current reward value corresponding to each execution plan in the execution plan set.

[0096] After exploring the use of balanced reinforcement learning based on the multi-armed bandit algorithm using the preset execution plan query model and obtaining the current prompt set corresponding to the current database query statement sample from each prompt set, different permutations and combinations of the steps in the current prompt set can generate different execution plans, generate an execution plan set corresponding to the current prompt set, and determine the current reward value corresponding to each execution plan in the execution plan set.

[0097] In a specific implementation of the present application, generating an execution plan set corresponding to the current prompt set may include the following steps:

[0098] Step 1: parsing the current database query statement sample into parsing trees for reflecting the grammatical structure of the current database query statement sample according to the current prompt set;

[0099] Step 2: Convert each parse tree into each query tree; wherein each query tree decomposes the query into each logical operation, and maps each logical operation to the data structure of the database;

[0100] Step 3: Convert each query tree into each execution plan tree; wherein each node in each execution plan tree is a logical operation step, and the connection relationship between each node is used to define the execution order and data flow of each logical operation step;

[0101] Step 4: Generate an execution plan set based on each execution plan tree.

[0102] For the convenience of description, the above four steps can be combined for explanation.

[0103] After the current prompt set corresponding to the current database query statement sample is obtained by screening from each prompt set, the current database query statement sample is first parsed into each parse tree used to reflect the grammatical structure of the current database query statement sample according to the current prompt set. The parse tree structure directly reflects the grammatical structure of the structured query language statement, which is convenient for the database system to understand and further process. Then, each parse tree is converted into each query tree. Each query tree decomposes the query into each logical operation and maps each logical operation to the data structure of the database. The query tree is a more abstract representation. It abandons the specific details of the structured query language syntax and focuses on the logical operations of the query, such as selection, projection and connection. The generation of the query tree is to optimize the query processing process. It decomposes the query into a series of logical operations, which are mapped to the data structure of the database. Then, each query tree is converted into each execution plan tree. Each node in each execution plan tree is a logical operation step, and the connection relationship between each node is used to define the execution order and data flow of each logical operation step. An execution plan set is generated according to each execution plan tree, so as to implement these operations in the actual database management system.

[0104] See also Figure 3 , Figure 3 This is a schematic diagram of the structure of an execution plan tree in an embodiment of the present application. The execution plan tree not only shows how database operations are combined together, but also shows the order and hierarchical structure between these operations in the form of a tree. This structure enables the database executor to effectively traverse the tree and execute each operation while ensuring the correct flow and processing of data. An optimized execution plan tree is crucial to improving query efficiency because it directly affects the execution order of operations and the selected data access method, thereby affecting the overall execution time and resource consumption of the query.

[0105] By first parsing the current database query statement sample into parsing trees that can reflect the grammatical structure, then converting each parsing tree into a query tree containing each logical operation, and then converting each query tree into each execution plan tree containing the node connection relationship for determining the execution order and data flow of each logical operation step, it ensures that each step is carried out in order, guarantees the accuracy of the query result, and improves the execution efficiency of the database query task.

[0106] In a specific implementation of the present application, after converting each query tree into each execution plan tree, the method may further include the following steps:

[0107] Each execution plan tree is converted into a binary tree; wherein each node of each binary tree uses one-hot encoding to record node information.

[0108] See also Figure 4 , Figure 4This is a schematic diagram of the structure of a binary tree in an embodiment of the present application. After converting each query tree into each execution plan tree, each execution plan tree is converted into each binary tree. Each node of each binary tree uses one-hot encoding to record node information, including the table involved in each node, the prompt set, whether it is an empty node, and the cardinality estimate and cost given by the native query optimizer. By converting each execution plan tree into each binary tree, it is convenient to calculate the reward value of each execution plan through the tree convolutional neural network.

[0109] In a specific implementation of the present application, determining the current reward value corresponding to each execution plan in the execution plan set may include the following steps:

[0110] The tree convolutional neural network is used to calculate the current reward value corresponding to each execution plan in the execution plan set.

[0111] After generating the execution plan set corresponding to the current prompt set, a Tree Convolutional Neural Network (TCNN) is used to calculate the current reward value corresponding to each execution plan in the execution plan set.

[0112] See also Figure 5 , Figure 5 The diagram is a schematic diagram of a query tree with different connections in an embodiment of the present application. The two query trees in the figure differ only in the connection operation of the root node, one is a merge connection and the other is a hash connection. Figure 5 The leaf node c represents the relation or table involved in a binary relation operation.

[0113] See also Figure 6 , Figure 6 This is a schematic diagram of the structure of a feature vector tree corresponding to query trees with different connections in an embodiment of the present application. The connection type is encoded in the first two items of the feature vector, and the feature vector tree is used as the input of the tree convolutional neural network.

[0114] See also Figure 7 , Figure 7This is a schematic diagram of the structure of a tree convolution filter in an embodiment of the present application. Similar to the convolution transformation performed on an image, tree convolution works by moving a set of shared filters over various parts of the execution plan tree. These filters are intuitively able to identify local relationships between various parent-child nodes. For example, the filter can identify a hash connection above a merge connection, or identify the connection between two relationships in the presence of a specific predicate. The outputs of these filters provide usable signals for the last few layers of the value network. These outputs may identify whether the child nodes of a join operation are sorted (which suggests the possibility of a merge connection), or the filter may evaluate whether the right-hand side relationship of a connection has a lower cardinality (which indicates that index usage may be effective). Since each node of the query tree has exactly two child nodes, each filter contains three weight vectors, , , Each filter is applied to the vector consisting of nodes and left and right child nodes and A new tree node vector is generated on each local triangle (ignoring the node if it is a leaf node) , the calculation formula is as follows:

[0115] ;

[0116] in, represents the activation function, represents the dot product operation, is a vector of new tree nodes output by the filter. Since the output of a tree convolution can be another tree, multiple tree convolution filter layers can be stacked. The first layer of filters accesses the feature execution plan tree, and the second layer of filters will be applied to the output of the first layer, so each layer of tree convolution can receive more information than the previous layer. This structure causes the first layer of tree convolution layers to learn simple features (e.g., identifying merge connections above merge connections), while the last layer of tree convolution layers learns complex features (e.g., identifying left deep merge connection chains).

[0117] See also Figure 8 , Figure 8This is a schematic diagram of the root node output results of a query tree with different connections in an embodiment of the present application. The filter is composed of three weight vectors as a detector for detecting two consecutive merge connections. It can be seen from the output results that the value of the execution plan with the root node being a merge connection after passing through the filter is 2, indicating that the filter successfully detects that the node is a merge connection and may meet the rules of continuous merge connections, for example, the weight vector matches the characteristics of the connection type. The value of the execution plan with the root node being a hash connection after passing through the filter output is 0, indicating that the filter did not detect the target pattern, that is, the hash connection does not meet the rules of continuous merge connections. Subsequent tree convolutional layers can use this information to build more complex detectors.

[0118] S207: Obtain each current network weight of the preset execution plan query model.

[0119] The preset execution plan query model will continuously update the network weights during the training process to obtain the current network weights of the preset execution plan query model.

[0120] S208: Update each current network weight according to each current reward value to obtain each updated network weight.

[0121] After determining the current reward value corresponding to each execution plan in the execution plan set and obtaining each current network weight of the preset execution plan query model, each current network weight is updated according to each current reward value to obtain each updated network weight.

[0122] Assume there is a set of prompts , where each prompt set For a given query Generate execution plan , and has a corresponding reward value. The weight of the model network , m is the number of network weights, and the current network weights can be updated by the following formula:

[0123] ;

[0124] in, represents the current weight of the i-th network parameter, Is the execution plan In time The reward value, are all execution plans at time The average reward value, is the learning rate.

[0125] S209: Determine whether each current network weight reaches a preset convergence condition based on each current network weight and each updated network weight. If so, execute step S210; if not, execute step S211.

[0126] After obtaining the updated network weights through weight updating, determine whether the current network weights meet the preset convergence conditions based on the current network weights and the updated network weights, such as determining whether the change rates of various network weights are less than preset values. If so, it means that the execution plan query model trained at the current moment can already perform relatively accurate queries on various database query statements, and execute step S210. If not, it means that the execution plan query model trained at the current moment cannot perform relatively accurate queries on various database query statements, and execute step S211.

[0127] S210: Determine the preset execution plan query model corresponding to each current network weight as the target execution plan query model.

[0128] When it is determined based on the current network weights and the updated network weights that each current network weight reaches the preset convergence condition, it means that the execution plan query model trained at the current moment is able to perform relatively accurate queries on various database query statements, and the preset execution plan query model corresponding to each current network weight is determined as the target execution plan query model.

[0129] S211: Replace each current network weight in the preset execution plan query model with each updated network weight to obtain a new preset execution plan query model, and return to execute step S204.

[0130] When it is determined based on the current network weights and the updated network weights that the current network weights have not reached the preset convergence conditions, it means that the execution plan query model trained at the current moment cannot perform relatively accurate queries on various database query statements. The current network weights in the preset execution plan query model are replaced with the updated network weights to obtain a new preset execution plan query model, and the execution returns to step S204, thereby continuing to select database query statement samples for model training until the network weights converge.

[0131] S212: Receive a database query statement corresponding to the current database query task.

[0132] S213: Query the target execution plan corresponding to the database query statement using the target execution plan query model obtained based on exploration and balanced reinforcement learning.

[0133] The target execution plan is an execution plan in an execution plan set generated according to the target prompt set, and the target prompt set is a prompt set in prompt sets constructed according to preset query prompt set parameters.

[0134] In a specific implementation of the present application, querying the target execution plan corresponding to the database query statement using the target execution plan query model obtained by exploring and utilizing balanced reinforcement learning includes the following steps:

[0135] The target execution plan query model is used to query the target execution plan corresponding to the database query statement determined by the accumulated reward value.

[0136] A prompt set may correspond to multiple execution plans. During the model training process, the cumulative reward value is calculated for each execution plan. When querying the execution plan, the target execution plan query model is used to query the target execution plan corresponding to the database query statement determined by the cumulative reward value. The target execution plan corresponding to the database query statement determined by the cumulative reward value greatly improves the accuracy of the queried execution plan and improves the execution efficiency of the database query task.

[0137] In a specific implementation of the present application, the method may further include a process of determining a target execution plan corresponding to a database query statement determined by a cumulative reward value. The process of determining a target execution plan corresponding to a database query statement determined by a cumulative reward value may include the following steps:

[0138] Step 1: When each current network weight reaches a preset convergence condition, a target prompt set corresponding to a database query statement is obtained;

[0139] Step 2: Obtain the cumulative reward values ​​corresponding to each execution plan corresponding to the target prompt set;

[0140] Step 3: Calculate the selection probability of each execution plan according to the accumulated reward value;

[0141] Step 4: Determine the execution plan corresponding to the maximum value among the selected probabilities as the target execution plan.

[0142] For the convenience of description, the above four steps can be combined for explanation.

[0143] When each current network weight reaches the preset convergence condition, the target prompt set corresponding to the database query statement is obtained, and the cumulative reward values ​​corresponding to each execution plan corresponding to the target prompt set are obtained. The selection probability corresponding to each execution plan is calculated according to each cumulative reward value, and the execution plan corresponding to the maximum value of each selection probability is determined as the target execution plan.

[0144] When calculating the selection probability corresponding to each execution plan, the softmax function can be used to determine the selection of different execution plans. Is the execution plan Probability of being selected:

[0145] ;

[0146] in, is a parameter between 0 and 1. and Execution Plan and The cumulative reward value of , h is the number of execution plans corresponding to the target prompt set.

[0147] S214: Execute the current database query task according to the target execution plan.

[0148] See also Fig. 9 , Fig. 9 This is an overall technical roadmap in the embodiment of the present application. The database query statement is first input into the parser, and the parser then inputs the parsed result obtained by parsing into the query optimizer. The query optimizer selects the current prompt set from multiple prompt sets through strategy selection, and each prompt set corresponds to multiple execution plans. The corresponding execution plan is further selected from multiple execution plans, and the screened execution plan is input into the encoder. The encoder inputs the encoded feature tree vector into the tree convolutional neural network, and the reward value is calculated by the tree convolutional neural network. The reward value is sent to the executor, and the executor determines whether the network weight of the current model converges according to the experience pool. If not, the reward value calculation is continued using the tree convolutional neural network, thereby updating the network weight until the network weight converges.

[0149] In an actual application scenario of the present application, any one of the Internet Movie Database (IMDB) data set or the Join Order Benchmark (JOB) data set and the Stack data set are used to verify the performance improvement effect of the hint set in database query optimization.

[0150] As shown in Table 1, Table 1 is a default prompt set parameter configuration table of the PostgreSQL database.

[0151] Table 1

[0152]

[0153] As shown in Table 2, Table 2 compares the total query time consumption using the default optimizer, the best hint set, and the worst hint set on the Internet Movie Database dataset and the Stack dataset in PostgreSQL 12.8 and 16.2 versions of the database.

[0154] Table 2

[0155]

[0156] Table 2 records the performance of each query under different hint set configurations in detail. The data shows that the proper use of hint sets can significantly optimize query execution time, but inappropriate hint sets may lead to serious performance degradation, especially in a big data environment. For example, improperly disabling index scans may lead to full table scans, and the performance loss is unacceptable.

[0157] As shown in Table 3, Table 3 shows the situation of database queries using the best hint set in PostgreSQL 12.8 and PostgreSQL 16.2.

[0158] Table 3

[0159]

[0160] From the results, we can see that the optimal prompt sets corresponding to different versions of databases are completely different.

[0161] After experimental testing on two recognized open source query datasets, IMDB / JOB and Stack, the embodiments of the present application systematically confirmed the applicability of the query hint set in real database query optimization scenarios.

[0162] Corresponding to the above method embodiment, the present application also provides a database query task execution device. The database query task execution device described below and the database query task execution method described above can refer to each other.

[0163] See also Fig.10 , Fig.10 This is a structural block diagram of a database query task execution device in an embodiment of the present application, and the device may include:

[0164] A query statement receiving module 11 is used to receive a database query statement corresponding to the current database query task;

[0165] A target execution plan query module 12 is used to query the target execution plan corresponding to the database query statement using the target execution plan query model obtained based on exploration and balanced reinforcement learning; wherein the target execution plan is an execution plan in an execution plan set generated according to a target prompt set, and the target prompt set is a prompt set in each prompt set constructed according to preset query prompt set parameters;

[0166] The database query task execution module 13 is used to execute the current database query task according to the target execution plan.

[0167] It can be seen from the above technical solution that the target execution plan query model obtained based on exploration-utilization balance reinforcement learning achieves a balance between exploration and utilization. When receiving the database query statement corresponding to the current database query task, the target execution plan query model can query the target prompt set from each prompt set constructed according to the preset query prompt set parameters, and find the target execution plan from each execution plan generated according to the target prompt set. The quality of prompt set selection is improved, the rational use of prompt sets is achieved, the accuracy of execution plan selection is improved, and the execution time of database query tasks is greatly reduced. The target execution plan query model is not restricted to database query statements, which greatly improves its applicability to database query tasks.

[0168] In a specific embodiment of the present application, the device may further include a model training module, the model training module including:

[0169] A query statement sample set collection submodule is used to collect a database query statement sample set;

[0170] A prompt set parameter acquisition submodule is used to obtain each preset query prompt set parameter;

[0171] A prompt set construction submodule, used for constructing each prompt set according to each preset query prompt set parameter;

[0172] A query statement sample selection submodule is used to randomly select a current database query statement sample from a database query statement sample set;

[0173] The current prompt set acquisition submodule is used to use the preset execution plan query model to explore and utilize balanced reinforcement learning based on the multi-armed bandit algorithm to filter out the current prompt set corresponding to the current database query statement sample from each prompt set;

[0174] The current reward value determination submodule is used to generate an execution plan set corresponding to the current prompt set, and determine the current reward value corresponding to each execution plan in the execution plan set;

[0175] The current network weight acquisition submodule is used to obtain the current network weights of the preset execution plan query model;

[0176] The updated network weight acquisition submodule is used to update each current network weight according to each current reward value to obtain each updated network weight;

[0177] A judgment submodule is used to judge whether each current network weight reaches a preset convergence condition according to each current network weight and each updated network weight;

[0178] A target execution plan query model determination submodule is used to determine the preset execution plan query model corresponding to each current network weight as the target execution plan query model when it is determined based on each current network weight and each updated network weight that each current network weight reaches a preset convergence condition;

[0179] The repeated execution submodule is used to replace the current network weights in the preset execution plan query model with the updated network weights when it is determined based on the current network weights and the updated network weights that the current network weights do not meet the preset convergence conditions, to obtain a new preset execution plan query model, and repeatedly execute the step of randomly selecting a current database query statement sample from the database query statement sample set.

[0180] In a specific implementation of the present application, the current reward value determination submodule includes:

[0181] A parse tree obtaining unit, used to parse the current database query statement sample into parse trees reflecting the grammatical structure of the current database query statement sample according to the current prompt set;

[0182] A query tree conversion unit, used to convert each parse tree into each query tree; wherein each query tree decomposes the query into each logical operation, and maps each logical operation to a data structure of the database;

[0183] An execution plan tree obtaining unit, used to convert each query tree into each execution plan tree; wherein each node in each execution plan tree is a logical operation step, and the connection relationship between each node is used to define the execution order and data flow of each logical operation step;

[0184] The execution plan set generating unit is used to generate an execution plan set according to each execution plan tree.

[0185] In a specific embodiment of the present application, the device may further include:

[0186] The binary tree acquisition module is used to convert each execution plan tree into each binary tree after converting each query tree into each execution plan tree; wherein each node of each binary tree uses one-hot encoding to record node information.

[0187] In a specific implementation of the present application, the current reward value determination submodule is specifically a module that uses a tree convolutional neural network to calculate the current reward value corresponding to each execution plan in the execution plan set.

[0188] In a specific implementation of the present application, the target execution plan query module is specifically a module that uses a target execution plan query model to query a target execution plan corresponding to a database query statement determined by a cumulative reward value.

[0189] In a specific implementation of the present application, the device may further include an execution plan determination module, and the execution plan determination module includes:

[0190] The target prompt set acquisition submodule is used to obtain the target prompt set corresponding to the database query statement when each current network weight reaches the preset convergence condition;

[0191] The cumulative reward value acquisition submodule is used to obtain the cumulative reward values ​​corresponding to each execution plan corresponding to the target prompt set;

[0192] The selection probability calculation submodule is used to calculate the selection probability corresponding to each execution plan according to each accumulated reward value;

[0193] The execution plan determination submodule is used to determine the execution plan corresponding to the maximum value among the selected probabilities as the target execution plan.

[0194] Corresponding to the above method embodiment, see Fig.11 , Fig.11 This is a schematic diagram of a database query task execution device provided by the present application, which may include:

[0195] A memory 332, for storing computer programs;

[0196] The processor 322 is used to implement the steps of the database query task execution method of the above method embodiment when executing the computer program.

[0197] For details, please refer to Fig.12 , Fig.12 A specific structural diagram of a database query task execution device provided for this embodiment, the database query task execution device may have relatively large differences due to different configurations or performances, and may include a processor (central processing units, CPU) 322 (for example, one or more processors) and a memory 332, and the memory 332 stores one or more computer programs 342 or data 344. Among them, the memory 332 can be a temporary storage or a permanent storage. The program stored in the memory 332 may include one or more modules (not shown in the figure), and each module may include a series of instruction operations in the data processing device. Furthermore, the processor 322 can be configured to communicate with the memory 332 to execute a series of instruction operations in the memory 332 on the database query task execution device 301.

[0198] The database query task execution device 301 may further include one or more power supplies 326 , one or more wired or wireless network interfaces 350 , one or more input and output interfaces 358 , and / or one or more operating systems 341 .

[0199] The steps in the database query task execution method described above can be implemented by the structure of the database query task execution device.

[0200] Corresponding to the above method embodiment, the present application further provides a computer-readable storage medium, on which a computer program is stored. When the computer program is executed by a processor, the following steps can be implemented:

[0201] Receive a database query statement corresponding to the current database query task; use a target execution plan query model obtained based on exploration and balanced reinforcement learning to query a target execution plan corresponding to the database query statement; wherein the target execution plan is an execution plan in an execution plan set generated according to a target prompt set, and the target prompt set is a prompt set in each prompt set constructed according to preset query prompt set parameters; execute the current database query task according to the target execution plan.

[0202] The computer-readable storage medium may include: a USB flash drive, a mobile hard disk, a read-only memory (ROM), a random access memory (RAM), a magnetic disk or an optical disk, and other media that can store program codes.

[0203] For an introduction to the computer-readable storage medium provided in this application, please refer to the above method embodiment, and this application will not go into details here.

[0204] In this specification, each embodiment is described in a progressive manner, and each embodiment focuses on the differences from other embodiments. The same or similar parts between the embodiments can be referred to each other. For the devices, equipment and computer-readable storage media disclosed in the embodiments, since they correspond to the methods disclosed in the embodiments, the description is relatively simple, and the relevant parts can be referred to the method part description.

[0205] Specific examples are used herein to illustrate the principles and implementation methods of the present application, and the description of the above embodiments is only used to help understand the technical solution and core ideas of the present application. It should be pointed out that for ordinary technicians in this technical field, without departing from the principles of the present application, several improvements and modifications can be made to the present application, and these improvements and modifications also fall within the scope of protection of the present application.

Claims

1. A method for executing a database query task, characterized in that: include: Receive the database query statement corresponding to the current database query task; The target execution plan query model obtained by exploring and utilizing balanced reinforcement learning is used to query the target execution plan corresponding to the database query statement; wherein the target execution plan is an execution plan in an execution plan set generated according to a target prompt set, and the target prompt set is a prompt set in each prompt set constructed according to preset query prompt set parameters; Execute the current database query task according to the target execution plan.

2. The database query task execution method according to claim 1, characterized in that: The method also includes a training process of the target execution plan query model, and the training process of the target execution plan query model includes: Collect a sample set of database query statements; Get each preset query prompt set parameter; Construct each prompt set according to each preset query prompt set parameter; Randomly select a current database query statement sample from the database query statement sample set; Using a preset execution plan query model to explore and utilize balanced reinforcement learning based on a multi-armed bandit algorithm, a current prompt set corresponding to the current database query statement sample is obtained from each prompt set; Generate an execution plan set corresponding to the current prompt set, and determine the current reward value corresponding to each execution plan in the execution plan set; Obtaining each current network weight of the preset execution plan query model; Update the current network weights according to the current reward values ​​to obtain the updated network weights; Determine whether each current network weight reaches a preset convergence condition according to each current network weight and each updated network weight; If yes, then determining the preset execution plan query model corresponding to each current network weight as the target execution plan query model; If not, then each current network weight in the preset execution plan query model is replaced with each updated network weight to obtain a new preset execution plan query model, and the step of randomly selecting a current database query statement sample from the database query statement sample set is repeated.

3. The database query task execution method according to claim 2, characterized in that: Generate an execution plan set corresponding to the current prompt set, including: Parsing the current database query statement sample into parsing trees reflecting the grammatical structure of the current database query statement sample according to the current prompt set; Convert each parse tree into each query tree; wherein each query tree decomposes the query into each logical operation, and maps each logical operation to the data structure of the database; Convert each query tree into each execution plan tree; wherein each node in each execution plan tree is a logical operation step, and the connection relationship between each node is used to define the execution order and data flow of each logical operation step; The execution plan set is generated according to each execution plan tree.

4. The database query task execution method according to claim 3, characterized in that: After converting each query tree into each execution plan tree, it also includes: Each execution plan tree is converted into a binary tree; wherein each node of each binary tree uses one-hot encoding to record node information.

5. The database query task execution method according to claim 2, characterized in that: Determining the current reward value corresponding to each execution plan in the execution plan set includes: A tree convolutional neural network is used to calculate the current reward value corresponding to each execution plan in the execution plan set.

6. The method for executing a database query task according to any one of claims 2 to 5, characterized in that: The target execution plan query model obtained based on exploration and balanced reinforcement learning is used to query the target execution plan corresponding to the database query statement, including: The target execution plan query model is used to query the target execution plan corresponding to the database query statement determined by the accumulated reward value.

7. The database query task execution method according to claim 6, characterized in that: The method further includes a process of determining a target execution plan corresponding to the database query statement determined by the accumulated reward value, wherein the process of determining the target execution plan corresponding to the database query statement determined by the accumulated reward value includes: When each current network weight reaches the preset convergence condition, obtaining a target prompt set corresponding to the database query statement; Obtain the cumulative reward values ​​corresponding to each execution plan corresponding to the target prompt set; Calculate the selection probability of each execution plan according to each accumulated reward value; The execution plan corresponding to the maximum value among the selected probabilities is determined as the target execution plan.

8. A database query task execution device, characterized in that: include: A query statement receiving module is used to receive a database query statement corresponding to the current database query task; A target execution plan query module is used to query the target execution plan corresponding to the database query statement using a target execution plan query model obtained based on exploration and balanced reinforcement learning; wherein the target execution plan is an execution plan in an execution plan set generated according to a target prompt set, and the target prompt set is a prompt set in each prompt set constructed according to preset query prompt set parameters; The database query task execution module is used to execute the current database query task according to the target execution plan.

9. A database query task execution device, characterized in that: include: Memory for storing computer programs; A processor, configured to implement the steps of the database query task execution method as claimed in any one of claims 1 to 7 when executing the computer program.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the steps of the database query task execution method according to any one of claims 1 to 7 are implemented.

Images