A query plan optimization method, system, device and medium based on deep reinforcement learning

Abstract

The application relates to the technical field of computer information processing, and discloses a query plan optimization method, system, device and medium based on deep reinforcement learning, which is realized through a query plan optimization framework based on deep reinforcement learning. The query plan optimization framework comprises a plan optimizer and a plan comparator. The method comprises the following steps: identifying suboptimal nodes of an original query plan through the plan optimizer, generating optimization actions, optimizing the original query plan, and obtaining a plurality of candidate plans; and evaluating the advantage values between each two candidate plans in the plurality of candidate plans through the plan comparator, and obtaining an optimal query plan. According to the application, the plan optimizer can autonomously generate candidate plans without relying on expert knowledge, the performance of the candidate plans is evaluated through the plan comparator, the optimal query plan with good performance is efficiently and low-costly obtained, efficient self-interaction is realized through simulation training, and the optimization capability of the query plan optimization framework is enhanced by using a large amount of high-quality simulation experience.

Description

Technical Field

[0001] This invention relates to the field of computer information processing technology, and in particular to a query plan optimization method, system, device and medium based on deep reinforcement learning. Background Technology

[0002] The query optimizer plays a crucial role in database management systems. Query optimization is a key step in reducing the execution cost of database system query plans.

[0003] Traditional query optimizers take the relational algebra expression corresponding to the query as input and output an optimized physical execution plan. Most traditional query optimizers follow the Selinger design and consist of a cardinality estimator, a cost estimator, and a query plan generator. These components work together to explore the plan space and find the possible optimal query plan. However, for traditional query optimizers, finding the optimal plan in a vast plan space is an NP-hard (Non-deterministic Polynomial) problem. Even considering only the join order of the plan in a left-deep tree structure, the plan space still grows exponentially with the number of tables involved in the query. When other factors such as dense trees, join methods, and access paths are considered, the plan search space expands further. Meanwhile, errors in the traditional cardinality and cost estimators often lead to poorly performing plans. Furthermore, traditional query optimizers cannot learn from past experience and often repeatedly generate poorly performing plans for similar queries.

[0004] Therefore, there is an urgent need for a query plan optimization method that can efficiently and cost-effectively optimize query plans through a deep reinforcement learning-based query plan optimization framework. Summary of the Invention

[0005] This invention provides a query plan optimization method, system, device, and medium based on deep reinforcement learning, which addresses the shortcomings of existing query optimizers in obtaining high-performance query plans efficiently and at low cost.

[0006] This invention provides a query plan optimization method based on deep reinforcement learning, implemented through a query plan optimization framework based on deep reinforcement learning. The query plan optimization framework includes a plan optimizer and a plan comparator. The deep reinforcement learning-based query plan optimization method includes:

[0007] Obtain the original query plan, wherein the original query plan is generated by a query optimizer outside the query plan optimization framework (hereinafter referred to as the external plan optimizer) based on the user query;

[0008] The plan optimizer identifies suboptimal nodes in the original query plan and generates optimization actions to optimize the original query plan, resulting in multiple candidate plans.

[0009] The optimal query plan for the user query is obtained by evaluating the advantage value between each pair of candidate plans among multiple candidate plans using a plan comparison tool.

[0010] In one implementation, the plan optimizer employs a deep reinforcement learning framework, which includes components such as agent, environment, state, action, and reward. Specifically, the plan optimizer includes a first state representation network. and the action selector network π, where,

[0011] The process involves identifying suboptimal nodes in the original query plan using a plan optimizer and generating optimization actions to optimize the original query plan, resulting in multiple candidate plans, including:

[0012] Based on the original query plan, the plan characteristics and step status characteristics are obtained. The original query plan is denoted as CP. t t represents the number of steps, and the step state feature is denoted as Step(t) = t / maxsteps, where maxsteps represents the maximum number of steps in a single optimization.

[0013] The plan features are encoded to obtain a feature vector, which is denoted as PlanEncoding(CP). t );

[0014] The feature vector and the step state features are concatenated to obtain the initial state, which is denoted as s. t =State(t)=PlanEncoding(CP) t Step(t);

[0015] Based on the initial state, the network is represented by the first state. The state representation vector is obtained, and the state representation vector is denoted as statevec;

[0016] Based on the state representation vector, the action encoding map is obtained through the action selector network π, where the action encoding map is denoted as a. t ;

[0017] The action code mapping is compared with the preset action mapping table to obtain the optimized action, where the optimized action is denoted as...

[0018] Perform optimization actions to obtain candidate plans (CP). t+1 ;

[0019] With candidate program CPt+1 As a new original query plan, the above steps are repeated until the maximum number of steps in a single optimization is reached, in order to obtain multiple candidate plans.

[0020] In one implementation, the plan features include node feature information, node spatial information, and plan structure information. The node feature information includes any one of the following or any combination thereof: operator features, predicate features, connection features, and table features. The node spatial information includes any one of the following node or any combination thereof: left node, right node, root node, and no sibling nodes. The plan structure information includes the height features of nodes and / or the reachability relationship features between any two nodes.

[0021] In one implementation, the optimization actions include optimization actions such as swapping table positions and / or rewriting node connection methods.

[0022] In one implementation, the plan comparator includes a second state representation network. And the location-aware output layer δ, wherein the optimal query plan corresponding to the user query is obtained by evaluating the advantage value between every two candidate plans among multiple candidate plans through a plan comparator, including:

[0023] Based on the new state of one of the two candidate plans, the network is represented by the second state. The node feature information and plan structure information of the candidate plan are input into the embedding layer to obtain the node representation N of each node i in the candidate plan. i And structure representation Z i ;

[0024] Represent the node as N i And structure representation Z i The candidate plans are concatenated and passed through a multi-head attention network to obtain the query plan representation of the candidate plans;

[0025] The query plan representation of the candidate plan is merged with the step state features of the candidate plan, and then passed through a linear layer network to obtain the final state representation vector of the candidate plan.

[0026] Based on the final state representation vector of each pair of candidate plans, the position-aware output layer δ is used to obtain the degree to which one candidate plan is superior to the other, so as to obtain the advantage value between each pair of candidate plans.

[0027] The optimal query plan for the user query is obtained by considering the advantage value between any two candidate plans among multiple candidate plans.

[0028] In one implementation, the step of obtaining the degree to which one candidate plan is superior to the other candidate plan through the position-aware output layer δ, based on the final state representation vectors of each pair of candidate plans, to obtain the advantage value between each pair of candidate plans, includes:

[0029] Based on the final state representation vectors of every two candidate plans, the position-aware output layer δ is used to obtain the degree to which one candidate plan is superior to the other through the advantage function, thus obtaining the advantage value between every two candidate plans. The expression for the advantage function is:

[0030] Adv(CP l CP r )=k-1 if Adv init (CP l CP r )∈D k ,

[0031]

[0032]

[0033] In the expression of the advantage function, Adv init () represents the initial dominance function, Adv() represents the final dominance function, and CP l Indicates the candidate plan in the left position, CP r U(CP) represents the candidate plan at the right-hand position. l U(CP) represents the performance of the candidate plan at the left position. r ) represents the performance of the candidate plan at the right position. The initial advantage function discretizes the initial advantage into several intervals, taking a finite ordered set of points {d} with l elements. i |i∈{1,2,...,l},0 <d1<…<d l <1} and use it to divide the interval (-∞, 1] into l+1 subintervals, obtaining an ordered set of subintervals D, D={(d i d i+1 ]|i∈{0, 1, ..., l}, d0→-∞, d l+1 =1}, D k Let represent the k-th element in D.

[0034] In one implementation, the deep reinforcement learning-based query plan optimization method further includes:

[0035] An external query optimizer and a query plan comparator are used to create a simulation environment. The external query optimizer generates a simulated original query plan based on historical user queries. The query plan optimizer and the query plan comparator optimize the simulated original query plan. Simulation experience is collected during the optimization process and used to update the query optimizer.

[0036] In one implementation, the process involves using an external query optimizer and a query plan comparator to create a simulation environment. The external query optimizer generates a simulated original query plan based on historical user queries. The query plan optimizer and the query plan comparator then optimize the simulated original query plan. Simulation experience is collected during the optimization process to update the query optimizer. This includes:

[0037] During the simulated query plan optimization process, when the plan comparator evaluates the advantage value between every two candidate plans among multiple candidate plans, the plan comparator evaluates the reward feedback for each candidate plan, where the reward feedback includes positive feedback and penalty feedback.

[0038] In one implementation, during the simulated query plan optimization process, when the plan comparator evaluates the advantage value between every two candidate plans among multiple candidate plans, the reward feedback for each candidate plan is evaluated by the plan comparator, including:

[0039] Based on the advantage value between every two candidate plans among multiple candidate plans, and according to the expressions for positive feedback and penalty feedback, the positive feedback and penalty feedback for each candidate plan are obtained;

[0040] The sum of positive and negative feedback for each candidate plan is obtained and used as the reward feedback for each candidate plan.

[0041] The expression for positive feedback is:

[0042]

[0043] In the expression for positive feedback, This indicates positive feedback. This represents the single-step reward, maxsteps represents the maximum number of steps in a single optimization, t represents the number of steps, and eb represents the single-step reward. e η represents the round reward, and η represents the weight of the round reward relative to the single-step reward.

[0044] The expression for the punishment feedback is:

[0045]

[0046] In the expression for punishment feedback, This represents punitive feedback, where γ represents the punishment coefficient. Indicates the origin of the incomplete plan up to the current incomplete plan The minimum number of steps required, incomplete plan is a tree structure containing only the connection order and connection method, where t represents the number of steps.

[0047] In one implementation, the simulation experience includes data of any one of the following or any combination thereof: initial state s t Action coding mapping a t Rewards and feedback t New state s t+1 .

[0048] This invention also provides a query plan optimization system based on deep reinforcement learning, including an original query plan acquisition module, a plan optimizer, a plan comparator, and an update module, wherein...

[0049] The original query plan acquisition module is used to: acquire the original query plan, wherein the original query plan is generated by an external plan optimizer based on the user query;

[0050] The plan optimizer is used to: identify the suboptimal nodes of the original query plan and generate optimization actions to optimize the original query plan, resulting in multiple candidate plans;

[0051] The plan comparator is used to evaluate the advantage value between every two candidate plans among multiple candidate plans, and to obtain the optimal query plan for the user query.

[0052] The update module is used to: create a simulation environment using an external plan optimizer and a plan comparator; generate a simulated original query plan based on historical user queries using the external plan optimizer; optimize the simulated query plan based on the simulated original query plan using the plan optimizer and the plan comparator; evaluate the reward feedback for each candidate plan when the plan comparator evaluates the advantage value between each pair of candidate plans in the process of optimizing the simulated query plan; and collect simulation experience in the process of optimizing the simulated query plan to update the plan optimizer.

[0053] The present invention also provides an electronic device, including a processor and a memory storing a computer program, wherein the processor executes the computer program to implement any of the above-described deep reinforcement learning-based query plan optimization methods.

[0054] The present invention also provides a non-transitory computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements any of the above-described deep reinforcement learning-based query plan optimization methods.

[0055] The present invention also provides a computer program product, the computer program product comprising a computer program that can be stored on a non-transitory computer-readable storage medium, and when the computer program is executed by a processor, the computer is able to execute any of the above-described deep reinforcement learning-based query plan optimization methods.

[0056] This invention provides a query plan optimization method, system, device, and medium based on deep reinforcement learning. It employs a finer-grained (node-level) optimization approach and uses a query plan optimization framework that does not require learning from scratch. It focuses on optimizing the suboptimal aspects of the original query plan generated by an external plan optimizer, with optimization capabilities originating from and exceeding those of the external plan optimizer. This invention autonomously generates candidate plans by training the plan optimizer, eliminating the need for pre-injected expert knowledge to design a hint set. The performance of the candidate plans is then evaluated using a plan comparator, resulting in a more efficient and cost-effective optimal query plan. Furthermore, the invention leverages a simulation trainer for efficient self-interaction, generating a large amount of high-quality simulation experience, significantly enhancing the optimization capabilities of the query plan optimization framework. Attached Figure Description

[0057] To more clearly illustrate the technical solutions in this invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of this invention. For those skilled in the art, other drawings can be obtained from these drawings without creative effort.

[0058] Figure 1 This is one of the flowcharts illustrating a query plan optimization method based on deep reinforcement learning provided by the present invention.

[0059] Figure 2 This is the second flowchart illustrating a query plan optimization method based on deep reinforcement learning provided by the present invention.

[0060] Figure 3 This is a sample diagram of the plan optimizer in operation.

[0061] Figure 4 This is one of the operational example diagrams of a query plan optimization method based on deep reinforcement learning provided by the present invention.

[0062] Figure 5 The second example diagram illustrates the operation of a query plan optimization method based on deep reinforcement learning provided by this invention.

[0063] Figure 6 This is a flowchart of the training process for the plan optimizer.

[0064] Figure 7This is a schematic diagram of the structure of a query plan optimization system based on deep reinforcement learning provided by the present invention.

[0065] Figure 8 This is a schematic diagram of the structure of the electronic device provided by the present invention. Detailed Implementation

[0066] To make the objectives, technical solutions, and advantages of this invention clearer, the technical solutions of this invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some, not all, embodiments of this invention, and should not be construed as limiting the invention. All other embodiments obtained by those skilled in the art based on the embodiments of this invention without creative effort are within the scope of protection of this invention. In the description of this invention, it should be understood that the terminology used is for descriptive purposes only and should not be construed as indicating or implying relative importance.

[0067] The following is combined Figures 1-8 This invention describes the query plan optimization method, system, device, and medium based on deep reinforcement learning provided by the present invention.

[0068] Figures 1-2 This is a flowchart illustrating the query plan optimization method based on deep reinforcement learning provided by this invention. (Refer to...) Figure 1 This invention provides a query plan optimization method based on deep reinforcement learning, implemented through a query plan optimization framework based on deep reinforcement learning. The query plan optimization framework includes a plan optimizer and a plan comparator. The query plan optimization method based on deep reinforcement learning includes:

[0069] Step S110: Obtain the original query plan, wherein the original query plan is generated by the external plan optimizer based on the user query. The external plan optimizer can be a traditional plan optimizer in the existing database management system, or it can be called an expert optimizer.

[0070] Step S120: Identify the suboptimal nodes of the original query plan through the plan optimizer, and generate optimization actions to optimize the original query plan to obtain multiple candidate plans;

[0071] Step S130: Evaluate the advantage value between each pair of candidate plans among multiple candidate plans using the plan comparison tool to obtain the optimal query plan.

[0072] See Figure 2For a given user query, this embodiment will output the execution plan in the order indicated by the numerical labels. The plan optimizer iteratively modifies the original query plan generated by the external plan optimizer using past experience, resulting in a set of candidate plans. Then, the plan comparator evaluates specific candidate plan pairs in chronological order (i.e., the order in which the candidate plans were generated), and selects the estimated optimal query plan based on the output score of the plan comparator, which is the optimal query plan corresponding to the user query.

[0073] by Figure 5 Taking the example shown, this embodiment illustrates the reasoning process when a query request is received. Figure 5 The left side of the diagram shows an example query from the Join Order Benchmark, while the right side shows an example diagram of the optimization plan output process in this embodiment. When a user query is input, this embodiment first uses the external plan optimizer of the database management system (such as PostgreSQL) to generate an original query plan as shown in a. The execution time of this plan is 90.78ms. After the external plan optimizer generates the original query plan, it can be obtained and transmitted to the plan optimizer. After obtaining the original query plan, the plan optimizer generates optimization actions (here it changes the join operator of the deepest non-leaf node from a hash join to a nested loop join), and measures the execution time of the new plan (candidate plan) b as 397.96ms. The plan optimizer continues to generate new optimization actions to optimize the new plan b (here it swaps the positions of table it and table mi_idx) to obtain a new plan (candidate plan) c with an execution time of 0.42ms. This round of optimization ends, and the final optimized query plan c is output. This invention aims to output an optimized query plan that is as superior as the new plan c based on the original query plan. In a real production environment, plans a and b do not need to be actually executed. Only the estimated optimal optimized query plan c needs to be output based on the evaluation of the plan comparator. The optimized query plan c will be executed by the plan executor of the database management system and the query results will be returned. The query results can be used to update the plan comparator.

[0074] In one embodiment, step S120 may include:

[0075] Based on the original query plan, the plan characteristics and step status characteristics are obtained. The original query plan is denoted as CP. tt represents the number of steps. The step state feature is denoted as Step(t) = t / maxsteps, where maxsteps represents the maximum number of steps in a single optimization. Specifically, the plan features include node feature information, node spatial information, and plan structure information. Among them, the node feature information includes any one of the following or any combination thereof: operator features, predicate features, connection features, and table features. The node spatial information includes any one of the following node or any combination thereof: left node, right node, root node, and no sibling nodes. The plan structure information includes the height features of nodes and / or the reachability relationship features between any two nodes.

[0076] The plan features are encoded to obtain a feature vector, which is denoted as PlanEncoding(CP). t );

[0077] The feature vector and the step state features are concatenated to obtain the initial state, which is denoted as s. t =State(t)=PlanEncoding(CP) t Step(t);

[0078] Based on the initial state, the network is represented by the first state. The state representation vector is obtained, and the state representation vector is denoted as statevec;

[0079] Based on the state representation vector, the action encoding map is obtained through the action selector network π, where the action encoding map is denoted as a. t ;

[0080] The action code mapping is compared with the preset action mapping table to obtain the optimized action, where the optimized action is denoted as... Optimization actions include actions that swap table positions and / or actions that rewrite node join methods;

[0081] Perform optimization actions to obtain candidate plans (CP). t+1 ;

[0082] With candidate program CP t+1 As a new original query plan, the above steps are repeated until the maximum number of steps in a single optimization is reached, in order to obtain multiple candidate plans.

[0083] In one embodiment, step S130 may include:

[0084] Based on the new state of one of the two candidate plans, the network is represented by the second state. The node feature information and plan structure information of the candidate plan are input into the embedding layer to obtain the node representation N of each node i in the candidate plan.i And structure representation Z i ;

[0085] Represent the node as N i And structure representation Z i The candidate plans are concatenated and passed through a multi-head attention network to obtain the query plan representation of the candidate plans;

[0086] The query plan representation of the candidate plan is merged with the step state features of the candidate plan, and then passed through a linear layer network to obtain the final state representation vector of the candidate plan.

[0087] Based on the final state representation vector of each pair of candidate plans, the position-aware output layer δ is used to obtain the degree to which one candidate plan is superior to the other, so as to obtain the advantage value between each pair of candidate plans.

[0088] The optimal query plan for the user query is obtained by considering the advantage value between any two candidate plans among multiple candidate plans.

[0089] In one embodiment, the second state representation network is a Transformer-based network architecture model, which can effectively capture the structure and node information of the query plan. After obtaining the state input code s, the second state representation network... The four node features from the node feature information of the state vector are input into a specific embedding layer to obtain a vector representation of each feature. Then, for each node i in the plan, Concatenate the four feature vectors to obtain the node representation of the i-th node, denoted as N. i Regarding the plan structure information, A specific embedding layer will be used to represent height. i Reachability relation feature ns i .at last, N i height i and ns i The data are connected and fed together into a multi-head attention network, a process that ultimately produces a representation of the query plan. This plan is then combined with step encodings and passed through a linear layer network to generate the final state representation vector, statevec.

[0090] In this embodiment, the second state represents the network's response to the two input plans (CPs) that need to be compared. left and CP right The representation yields two state representations, statevec. left and statevec rightThe position-aware output layer δ represents statevec in these two states respectively. left and statevec right As input, output a statevec indicator. right Superior to statevec left The degree is expressed as an integer score, which also indicates the degree to which the plan on the right is better than the plan on the left. The score setting principle is determined according to the model load. In this embodiment, the score setting rule σ of the advantage value is: if the score is 0, it means that the plan on the left is better than the plan on the right; if it is 1, it means that the plan on the right is better than the plan on the left by 5%; if it is 2, it means that the plan on the right is better than the plan on the left by 50%.

[0091] In one embodiment, step S140 is further included:

[0092] A simulation environment is formed using an external query optimizer and a query plan comparator. The external query optimizer generates a simulated original query plan based on historical user queries. The query plan optimizer and the query plan comparator optimize the simulated original query plan. Simulation experience is collected during the optimization process to update the query optimizer. Specifically, in step S140, during the optimization process, when the query plan comparator evaluates the advantage value between every two candidate plans, the reward feedback for each candidate plan is evaluated by the query plan comparator. The reward feedback includes positive feedback and penalty feedback. In the optimization process, the initial state s can be collected. t Action coding mapping a t Rewards and feedback t New state s t+1 As simulation experience, it is used for updating and optimizing the plan optimizer.

[0093] In one embodiment, such as Figure 4As shown, this embodiment can simultaneously perform plan optimizer training, plan comparator training, and plan execution. First, the plan optimizer is randomly initialized and then used to generate candidate plans for user queries sampled from the workload. After executing the candidate plans, this embodiment collects the execution results of these plans into the plan optimizer's training data pool and updates the plan comparator. Then, the plan optimizer interacts with the simulation environment for iterative updates. Simultaneously, this embodiment also collects plans with higher estimated scores for execution based on feedback to the plan comparator during plan optimizer training. In addition, this embodiment periodically samples queries randomly from the workload and collects candidate plans to be executed. The embodiment utilizes a listener process to trigger plan execution operations when a new plan is received, and integrates the execution time results into the execution buffer. After accumulating a sufficient number of execution plans, this embodiment reorganizes the training data in the execution buffer to retrain the plan comparator. During plan execution, the embodiment implements a dynamic timeout mechanism to prevent poorly performing plans from hindering training progress. The timeout is set to 1.5 times the execution time of the original query plan. If the execution time of a newly generated plan exceeds this threshold, it is terminated and marked as timed out. When organizing the training data pool for the plan comparator, the example filters out plan pairs where both plans time out. This strategy significantly increases the efficiency of training sample collection.

[0094] In one embodiment, given a schema containing n tables, when a user query Q is received, the database management system's external plan optimizer first generates a raw query plan CP. The plan optimizer extracts the join method and join order (or other factors that significantly impact plan performance) from the raw query plan CP. Figure 3 As shown in the upper right corner, this embodiment refers to the tree structure containing only the join order and join method as the incomplete plan ICP. The main process of the plan optimizer is to generate optimization actions step by step to iteratively modify the ICP. Each time a new ICP is obtained, guided by the new ICP, this embodiment can use a database management system tool (such as pg_hint_plan in PostgreSQL) to generate a new complete plan Cp (i.e., a candidate plan). By using such a tool, the table scan operators and other nodes in the plan are supplemented by the external plan optimizer using its own expertise, thereby generating an executable query plan Cp. When the maximum number of steps, maxsteps, is reached, the plan optimization process stops. Specifically, the plan optimizer adopts a deep reinforcement learning framework, which includes components such as agent, environment, state, action, and reward.

[0095] State: In a deep reinforcement learning framework, it is the output of the environment and the input of the agent. In the plan optimizer, it mainly consists of the complete plan Cp. To facilitate subsequent processing by the agent, this embodiment extracts key features (such as plan features) from CP and encodes them into numerical vectors. Plan Encoding (CP) t The plan structure includes node feature information, node spatial information, and plan structure information. Node feature information includes operator features, predicate features, connection features, and table features; node spatial information refers to the orientation features of nodes, including the orientation features of left nodes, right nodes, root nodes, or nodes without sibling nodes; plan structure information mainly includes the height features of each node and the reachability relationship features between any two nodes. Furthermore, considering the temporal characteristics of the plan optimizer generating the plan, this embodiment adds a step state feature Step(t) = t / maxsteps, where the hyperparameter maxsteps refers to the maximum number of steps in a single optimization. Concatenating the plan feature vector and the step state feature yields the state code.

[0096] s t =State(t)=PlanEncoding(CP) t Step(t)

[0097] t represents the number of steps.

[0098] Action: This embodiment marks the node order of ICPs in a bottom-up manner, using two types of labels: T k Represents leaf nodes (i.e., tables), O k This represents non-leaf nodes (i.e., the connection method). For example... Figure 3 As shown, this invention starts with the two leaf nodes at the deepest level, where the left node is labeled T1 and the right node is labeled T2. The leaf node at the next higher level is labeled T3, and so on. Similarly, following a bottom-up order, the deepest non-leaf node is labeled O1, its parent node is labeled O2, and so on. This embodiment includes two types of actions; the first is swapping two tables (T... l ,T r The position of ) is represented as Swap(T) l ,T r The table shows that position swapping actions include I. s = (n × (n-1)) / 2 different operations. The second type is to rewrite node O using the j-th connection method in Op. i The connection method is represented as Override(O i Op j ), where Op is the set of all available connection methods in the database management system. Overriding connection methods includes I... o=|Op|×(n-1) different operations. In this embodiment, all actions are encoded as operations in the range [1, I... s +I o Let a be an integer a, and predefine an action mapping function Act(a,ICP) to map the integer a to the corresponding action on ICP. This can be represented as follows:

[0099]

[0100] When Swap(T) is called l ,T r When ), l and r satisfy the following conditions:

[0101] B l ≤a l+1 ,r=aB l +2

[0102]

[0103] B represents an intermediate function used for concise expression of the formula; it has no actual meaning.

[0104] When Override(O) is called i Op j When i and j satisfy the following conditions:

[0105]

[0106] j=(I s +I o -amod|Op|)+1,

[0107] mod means modulo division, i.e., the remainder operation.

[0108] Depending on the number of tables in the query graph, each query may have different action space constraints. For example, in Figure 3 In the query shown, Swap(T1,T5) is considered an illegal action. To address this issue, the plan optimizer can perform a validity check on the action space before the agent selects an action. This embodiment uses an action mask to prohibit illegal actions. To further prune the action space, this embodiment also employs a heuristic rule: when executing Swap(T1,T5)... l ,T r After that, the only operation that can be performed is Override(O). i Op j ), where O i It is T l or T r The parent node. This can also be achieved using an action mask. ​

[0109] Reward: The reward is used to evaluate the effect of the action selected by the agent. In the planning optimizer, the reward at each step t of each round e (i.e., for each input query) consists of and . The former is the positive feedback value of the action selected by the agent, and the latter is the penalty feedback value used to penalize inappropriate actions.

[0110] 1) Define the initial advantage function Adv init as: It is used to calculate the initial advantage between two candidate plans, indicating how much better CP r is compared to CP l .

[0111]

[0112] where U(CP) is related to the performance of CP (such as execution time). Since fine-grained advantage values are not suitable for dynamically changing system workloads. In this embodiment, the initial advantage is discretized into several intervals, taking a finite ordered point set {d i | i ∈ {1, 2,..., l}, 0 < d1 < … < dl < 1} and using it to divide the interval (-∞, 1] into l + 1 sub-intervals, obtaining an ordered sub-interval set D = {(d i , d i+1 | i ∈ {0, 1,..., l}, d0 → -∞, d l+1 = 1}. The present invention uses D k to represent the k-th element in D, and sets In this embodiment, the final advantage function Adv is defined, which takes a pair of plans as input and outputs a score.

[0113] Adv(CP l , CP r ) = k - 1 if Adv init (CP l , CP r ) ∈ D k ,

[0114] For each round e of the query Q, the planning optimizer starts from the original query plan and expects to generate a candidate plan at step t that is better than the plan at the previous t - 1 steps In this embodiment, the single-step reward is set as where is the estimated optimal plan among all candidate plans in the previous t - 1 steps. Considering that when a round e ends, the final output plan generated by this embodiment (That is, the plan to be executed, the final optimized plan) should have a significant weight in the total reward. Therefore, this embodiment is based on... Performance status increases round reward eb e In order to fully assess To assess performance, this embodiment selects the best-performing plan and the plan with median performance from the set of plans that have performed better than the original query plan for query Q. These plans, together with the original plan, constitute the reference plan set CP. ref This embodiment uses the formula. The true reward for each reference plan is calculated and used as a reference reward. Finally, this embodiment estimates... To determine The performance ranking range. eb e The definition is as follows:

[0115]

[0116] Here, refb0 is set to 1, which represents the upper limit value. It is expressed as follows:

[0117]

[0118] Where η is a constant, representing the weight of round rewards relative to single-step rewards.

[0119] 2) To encourage the agent to reach each different state in the fewest possible steps, this embodiment implements penalties for inappropriate actions. During the execution of the plan optimizer, there may be multiple action sequences of different lengths from the initial state to a certain state; here, an action sequence refers to a combination of the Swap and Override operators. To obtain more rewards, the agent may choose inappropriate action sequences. For example, suppose position O... i The execution time of the hash join operator is L1, the execution time of the merge join operator is L2, and the execution time of the nested loop join operator is L3. Assume L1 > L2 > L3, and the original query plan chooses hash join. To obtain more rewards, the agent might choose to perform a join in O(log n) time. i Initially, the join method is rewritten as a merge join operator, and then in subsequent steps it is rewritten as a nested loop join. To address this issue, this embodiment computes from the original incomplete plan. up to the current incomplete plan The minimum number of steps required is denoted as Furthermore, this embodiment introduces a penalty coefficient Y>0 to represent the weight of the penalty in the total reward. This embodiment sets the penalty feedback value as follows:

[0120]

[0121] If the current step is already the minimum value, then the penalty value is 0.

[0122] Agent: In the plan optimizer, the agent mainly consists of two modules: a first state representation network φ and an action selector π. The former is a Transformer-based network used to process states. It takes state s as input and outputs a state representation vector statevec. This embodiment uses a multi-layer fully connected neural network as the action selector. It takes statevec and actionmask as input and outputs the action code a that maximizes the cumulative expected reward.

[0123] φ(s)→statevec, π(statevec, actionmask)→a

[0124] Environment: Its main functions include 1) providing new states based on the actions taken by the agent; 2) evaluating the agent's actions to provide rewards.

[0125] For 1), this embodiment can use the traditional optimizer Γ of the database system. p To provide a new state, we can use the following formula:

[0126]

[0127] In the initial step, it takes query Q as input and outputs a complete plan CP. In non-initial steps, it takes an incomplete plan ICP and query Q as input and generates a complete plan CP guided by the ICP. The CP is encoded using PlanEncoding to obtain the new state.

[0128] Regarding 2), after obtaining CP, this embodiment will provide a reward value as described in the reward section. The key difference is that, in a real-world environment, this embodiment will use the database management system executor Ψ. p The plan is executed, and the execution time is used to represent the performance of plan U(CP), and the advantage value and final reward are calculated. In the simulation environment, the advantage value is evaluated by the plan comparator without executing the plan.

[0129] Figure 6 This demonstrates the overall training process of the plan optimizer. When query Q is input, the database management system's plan optimizer generates the initial query plan. (Line 2). The plan optimizer starts from... Extracting components consisting of the connection order and connection method (Line 3). The plan optimizer initializes the round buffer set T and the optimal estimated plan. (Lines 4-5). With each step, the plan optimizer will... Calculate illegal actions (line 7). If the previous operation was a Swap operator, the current operation will be restricted to only overriding adjacent join methods (lines 8-9). Next, the plan optimizer will... The previous state is represented by the step state (lines 10-11), and the state is represented accordingly. Use the actionmask to evaluate the action (line 12). Then apply the action to... And generate new incomplete plans (Line 13). This will guide the database management system optimizer to output a complete plan. (Line 14). The plan optimizer calculates the penalty value and uses it to initialize the reward (Line 15). If the current plan is incomplete... If this did not occur in this round, the plan optimizer will receive the reward value and... Add T (lines 16-18). If the current plan is better... The plan optimizer will use renew (Line 19). Gather experience to update the agent (Line 20). Finally, after finishing the exploration of the current workload, update the agent using experiences (Line 23).

[0130] The query plan optimization method based on deep reinforcement learning provided by this invention has at least the following advantages:

[0131] High training efficiency: This invention starts with the original query plan generated by an external plan optimizer, focusing on optimizing the suboptimal aspects of the original query plan without learning from scratch. In other words, the optimization capability of this invention originates from and surpasses that of the external plan optimizer. Each step of this invention generates a new execution plan, and timely feedback can be obtained by directly evaluating the performance of the new plan. Experimental results show that this invention typically requires only 1-3 optimization steps to obtain a better-performing plan. This invention can also efficiently interact with itself using a simulation environment, generating a large amount of high-quality simulation experience, effectively enhancing optimization capabilities.

[0132] Intelligent candidate plan generation: This invention generates candidate plans autonomously by training a plan optimizer, without relying on pre-injected expert knowledge to design the hint set.

[0133] Ample search space for plans: This invention employs a finer-grained (node-level) optimization approach. In similar scenarios, this invention has the potential to discover the globally optimal plan.

[0134] The query plan optimization system based on deep reinforcement learning provided by the present invention will be described below. The query plan optimization system based on deep reinforcement learning described below can be referred to in correspondence with the query plan optimization method based on deep reinforcement learning described above.

[0135] Reference Figure 7 This invention provides a query plan optimization system based on deep reinforcement learning, comprising an original query plan acquisition module, a plan optimizer, a plan comparator, and an update module, wherein...

[0136] The original query plan acquisition module is used to: acquire the original query plan, wherein the original query plan is generated by an external plan optimizer based on the user query;

[0137] The plan optimizer is used to: identify the suboptimal nodes of the original query plan and generate optimization actions to optimize the original query plan, resulting in multiple candidate plans;

[0138] The plan comparator is used to evaluate the advantage value between every two candidate plans among multiple candidate plans to obtain the optimal query plan;

[0139] The update module is used to: create a simulation environment using an external plan optimizer and a plan comparator; generate a simulated original query plan based on historical user queries using the external plan optimizer; optimize the simulated query plan based on the simulated original query plan using the plan optimizer and the plan comparator; evaluate the reward feedback for each candidate plan when the plan comparator evaluates the advantage value between each pair of candidate plans in the process of optimizing the simulated query plan; and collect simulation experience in the process of optimizing the simulated query plan to update the plan optimizer.

[0140] Figure 8 An example is a schematic diagram of the physical structure of an electronic device, such as... Figure 8 As shown, the electronic device may include a processor 810, a communication interface 820, a memory 830, and a communication bus 840. The processor 810, communication interface 820, and memory 830 communicate with each other via the communication bus 840. The processor 810 can call logical instructions in the memory 830 to execute a query plan optimization method based on deep reinforcement learning. The query plan optimization method is implemented through a query plan optimization framework based on deep reinforcement learning, which includes a plan optimizer, a plan comparator, and a simulation trainer. The query plan optimization method includes the following steps:

[0141] Obtain the original query plan, wherein the original query plan is generated by an external plan optimizer based on the user query;

[0142] The plan optimizer identifies suboptimal nodes in the original query plan and generates optimization actions to optimize the original query plan, resulting in multiple candidate plans.

[0143] The optimal query plan is obtained by evaluating the advantage value between each pair of candidate plans among multiple candidate plans using a plan comparison tool.

[0144] Furthermore, the logical instructions in the aforementioned memory 830 can be implemented as software functional units and, when sold or used as independent products, can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention, essentially, or the part that contributes to the prior art, or a part of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of the present invention. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, portable hard drives, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks.

[0145] On the other hand, the present invention also provides a computer program product, which includes a computer program that can be stored on a non-transitory computer-readable storage medium. When the computer program is executed by a processor, the computer can execute the query plan optimization method based on deep reinforcement learning provided by the above methods. The query plan optimization method is implemented through a query plan optimization framework based on deep reinforcement learning, wherein the query plan optimization framework includes a plan optimizer, a plan comparator, and a simulation trainer. The query plan optimization method includes the following steps:

[0146] Obtain the original query plan, wherein the original query plan is generated by an external plan optimizer based on the user query;

[0147] The plan optimizer identifies suboptimal nodes in the original query plan and generates optimization actions to optimize the original query plan, resulting in multiple candidate plans.

[0148] The optimal query plan is obtained by evaluating the advantage value between each pair of candidate plans among multiple candidate plans using a plan comparison tool.

[0149] In another aspect, the present invention also provides a non-transitory computer-readable storage medium storing a computer program thereon, which, when executed by a processor, is implemented to perform the query plan optimization method based on deep reinforcement learning provided by the above methods. The query plan optimization method is implemented through a query plan optimization framework based on deep reinforcement learning, wherein the query plan optimization framework includes a plan optimizer, a plan comparator, and a simulation trainer. The query plan optimization method includes the following steps:

[0150] Obtain the original query plan, wherein the original query plan is generated by an external plan optimizer based on the user query;

[0151] The plan optimizer identifies suboptimal nodes in the original query plan and generates optimization actions to optimize the original query plan, resulting in multiple candidate plans.

[0152] The optimal query plan is obtained by evaluating the advantage value between each pair of candidate plans among multiple candidate plans using a plan comparison tool.

[0153] The device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the modules can be selected to achieve the purpose of this embodiment according to actual needs. Those skilled in the art can understand and implement this without any creative effort.

[0154] Through the above description of the embodiments, those skilled in the art can clearly understand that each embodiment can be implemented by means of software plus necessary general-purpose hardware platforms, and of course, it can also be implemented by hardware. Based on this understanding, the above technical solutions, in essence or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, magnetic disk, optical disk, etc., and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute the methods described in the various embodiments or some parts of the embodiments.

[0155] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, and not to limit them; although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features; and these modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of the present invention.

Claims

1. A query plan optimization method based on deep reinforcement learning, characterized in that, This is achieved through a query plan optimization framework based on deep reinforcement learning, wherein the query plan optimization framework includes a plan optimizer and a plan comparator, and the deep reinforcement learning-based query plan optimization method includes: Obtain the original query plan, wherein the original query plan is generated by a query optimizer outside the query plan optimization framework based on the user query; The plan optimizer identifies suboptimal nodes in the original query plan and generates optimization actions to optimize the original query plan, resulting in multiple candidate plans. The optimal query plan for the user query is obtained by evaluating the advantage value between each pair of candidate plans among multiple candidate plans using a plan comparison tool. The plan optimizer includes a first-state representation network. and action selector network The process involves identifying suboptimal nodes in the original query plan using a plan optimizer and generating optimization actions to optimize the original query plan, resulting in multiple candidate plans, including: Based on the original query plan, the plan characteristics and step status characteristics are obtained. The original query plan is denoted as... , Representing the number of steps, the step state features are denoted as... , Indicates the maximum number of steps in a single optimization; The plan features are encoded to obtain a feature vector, where the feature vector is denoted as . ; The feature vector and the step state features are concatenated to obtain the initial state, which is denoted as . ; Based on the initial state, the network is represented by the first state. This yields the state representation vector, where the state representation vector is denoted as... ; Based on the state representation vector, through the action selector network This yields the action encoding mapping, where the action encoding mapping is denoted as... ; The action code mapping is compared with the preset action mapping table to obtain the optimized action, where the optimized action is denoted as... ; Perform optimization actions to obtain candidate plans. ; Candidate Program As a new original query plan, the above steps are repeated until the maximum number of steps in a single optimization is reached, in order to obtain multiple candidate plans.

2. The query plan optimization method based on deep reinforcement learning according to claim 1, characterized in that, The plan comparator includes a second-state representation network. and position-aware output layer The step of evaluating the advantage value between every two candidate plans among multiple candidate plans using a plan comparator to obtain the optimal query plan corresponding to the user query includes: Based on the new state of one of the two candidate plans, the network is represented by the second state. The node feature information and plan structure information of the candidate plan are input into the embedding layer to obtain the node information of each node in the candidate plan. Node representation and structural representation ; Representing nodes and structural representation The candidate plans are concatenated and passed through a multi-head attention network to obtain the query plan representation of the candidate plans; The query plan representation of the candidate plan is merged with the step state features of the candidate plan, and then passed through a linear layer network to obtain the final state representation vector of the candidate plan. Based on the final state representation vectors of every two candidate plans, the position-aware output layer... This allows us to determine the extent to which one candidate plan is superior to another, thus obtaining the advantage value between each pair of candidate plans. Based on the advantage value between every two candidate plans among multiple candidate plans, the reward feedback of each candidate plan is evaluated, and the optimal query plan corresponding to the user query is obtained.

3. The query plan optimization method based on deep reinforcement learning according to claim 2, characterized in that, The final state representation vector of every two candidate plans is then processed through the position-aware output layer. To determine the extent to which one candidate program is superior to another, and to obtain the advantage value between each pair of candidate programs, including: Based on the final state representation vectors of every two candidate plans, the position-aware output layer... Using the advantage function, we obtain the degree to which one candidate plan is superior to another, thus determining the advantage value between each pair of candidate plans. The expression for the advantage function is: ， ， ， ， In the expression of the dominance function, Denotes the initial advantage function. Denotes the final advantage function. This indicates the candidate plan in the left position. This indicates the candidate plan in the right-hand position. This indicates the performance of the candidate plan in the left position. The performance of the candidate plan at the right position is represented by the initial advantage function, which discretizes the initial advantage into several intervals. One of these intervals is chosen. Finite ordered set of elements And use it to divide the interval Divided into Subintervals, thus obtaining an ordered set of subintervals. , , express The first in Each element.

4. The query plan optimization method based on deep reinforcement learning according to claim 3, characterized in that, Also includes: A simulation environment is formed by using a plan comparator and a query optimizer outside the query plan optimization framework. The query optimizer generates a simulated original query plan based on historical user queries. The plan optimizer and plan comparator optimize the simulated original query plan. Simulation experience is collected during the simulated query plan optimization process to update the plan optimizer.

5. The query plan optimization method based on deep reinforcement learning according to claim 4, characterized in that, The process utilizes a plan comparator and a query optimizer outside the query plan optimization framework to form a simulation environment. The query optimizer generates simulated original query plans based on historical user queries. The plan optimizer and plan comparator then optimize the simulated original query plans. Simulation experience is collected during the optimization process to update the plan optimizer, including: During the simulated query plan optimization process, when the plan comparator evaluates the advantage value between every two candidate plans among multiple candidate plans, the plan comparator evaluates the reward feedback for each candidate plan, where the reward feedback includes positive feedback and penalty feedback.

6. The query plan optimization method based on deep reinforcement learning according to claim 5, characterized in that, During the simulated query plan optimization process, when the plan comparator evaluates the advantage value between every two candidate plans among multiple candidate plans, the reward feedback for each candidate plan is evaluated by the plan comparator, including: Based on the advantage value between every two candidate plans among multiple candidate plans, and according to the expressions for positive feedback and penalty feedback, the positive feedback and penalty feedback for each candidate plan are obtained; The sum of positive and negative feedback for each candidate plan is obtained and used as the reward feedback for each candidate plan. The expression for positive feedback is: ， In the expression for positive feedback, This indicates positive feedback. Indicates a single-step reward. This indicates the maximum number of steps in a single optimization. Indicates the number of steps. Indicates round reward. This indicates the weight of round rewards relative to single-step rewards; The expression for the punishment feedback is: ， In the expression for punishment feedback, This indicates feedback on punishment. Indicates the penalty coefficient. Indicates the origin of the incomplete plan up to the current incomplete plan The minimum number of steps required, incompletely planned as a tree structure containing only the connection order and connection method. Indicates the number of steps; The simulation experience includes data from any of the following or any combination thereof: initial state Action coding mapping Rewards and Feedback New state .

7. A query plan optimization system based on deep reinforcement learning, characterized in that, It includes a raw query plan acquisition module, a plan optimizer, a plan comparer, and an update module. The original query plan acquisition module is used to: acquire the original query plan, wherein the original query plan is generated by a query optimizer outside the query plan optimization framework based on the user query; The plan optimizer is used to: identify the suboptimal nodes of the original query plan and generate optimization actions to optimize the original query plan, resulting in multiple candidate plans; The plan comparator is used to evaluate the advantage value between every two candidate plans among multiple candidate plans, and to obtain the optimal query plan for the user query. The update module is used to: create a simulated environment using a plan comparator and a query optimizer outside the query plan optimization framework; generate a simulated original query plan based on historical user queries using the query optimizer outside the query plan optimization framework; perform simulated query plan optimization based on the simulated original query plan using the plan optimizer and plan comparator; during the simulated query plan optimization process, when the plan comparator evaluates the advantage value between every two candidate plans among multiple candidate plans, evaluate the reward feedback of each candidate plan through the plan comparator; and collect simulation experience during the simulated query plan optimization process for updating the plan optimizer. The plan optimizer includes a first-state representation network. and action selector network The process involves identifying suboptimal nodes in the original query plan using a plan optimizer and generating optimization actions to optimize the original query plan, resulting in multiple candidate plans, including: Based on the original query plan, the plan characteristics and step status characteristics are obtained. The original query plan is denoted as... , Representing the number of steps, the step state features are denoted as... , Indicates the maximum number of steps in a single optimization; The plan features are encoded to obtain a feature vector, where the feature vector is denoted as . ; The feature vector and the step state features are concatenated to obtain the initial state, which is denoted as . ; Based on the initial state, the network is represented by the first state. This yields the state representation vector, where the state representation vector is denoted as... ; Based on the state representation vector, through the action selector network This yields the action encoding mapping, where the action encoding mapping is denoted as... ; The action code mapping is compared with the preset action mapping table to obtain the optimized action, where the optimized action is denoted as... ; Perform optimization actions to obtain candidate plans. ; Candidate Program As a new original query plan, the above steps are repeated until the maximum number of steps in a single optimization is reached, in order to obtain multiple candidate plans.

8. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the program, it implements the query plan optimization method based on deep reinforcement learning as described in any one of claims 1 to 6.

9. A non-transitory computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by a processor, it implements the query plan optimization method based on deep reinforcement learning as described in any one of claims 1 to 6.

Images