SQL query optimization method and system based on deep reinforcement learning driving

Abstract

According to the SQL query optimization method and system based on deep reinforcement learning driving, in the stage that an optimizer generates a physical query execution plan, action screening and state regression technologies are combined, and the performance of a deep Q network is improved. And a more accurate Q value can be obtained by training the neural network by using the'action-state 'pair. Besides, by balancing the weight of the Q network and the weight of the dual-depth Q network, the error of a target estimation value is effectively reduced, so that more accurate Q value estimation is realized. After the method is adopted, the following technical effects are achieved: (1) flexible connection sequence optimization: deep reinforcement learning enables the action values of the target network and the evaluation network to be optimally updated by analyzing SQL query and dynamically adjusting the connection sequence; and (2) compared with the traditional method, the method not only can dynamically adjust the connection sequence in the query, but also can perform optimal selection according to reward feedback and historical data. According to the optimization strategy, the query time is remarkably shortened in complex multi-table connection query, the query execution efficiency is improved, and generation of a non-optimal plan is avoided.

Description

Technical Field

[0001] The present invention relates to a SQL query optimization method and system driven by deep reinforcement learning, and belongs to the field of database query. Background Art

[0002] With the rapid development of communication technology and the Internet, the amount of data continues to increase, and database query efficiency has become a limiting factor in large-scale business performance. Database experts, scholars, and engineers are committed to optimizing relational database queries from multiple perspectives, aiming to shorten query time and reduce resource overhead. Especially in the digital economy era, with the increasing load of complex analytical queries and single-task queries with multiple relational connections, databases need to handle online analysis of data warehouses to support real-time business decisions. Therefore, database query optimization research must address the challenges of database application requirements.

[0003] First, the limitation of the cost model is the main problem that affects the query optimization effect. Traditional database optimizers predict query execution overhead based on heuristic rules and linear cost models, and select query execution plans based on the principle of cost minimization. However, linear cost models are usually unable to accurately capture the complex cost curves in query execution. Especially in multi-table joins and complex query scenarios, the cost of query execution is closely related to factors such as cardinality, table size, the existence of indexes, and data distribution. Usually, the cost model assumes that there is a linear relationship between these factors. In actual situations, the change in query cost is more likely to be nonlinear or even sudden. In addition, with the increase in data volume and the increase in the complexity of associations between tables, the uncertainty in the cost model will be further amplified, making the query plan generated by the optimizer suboptimal or even inefficient.

[0004] Secondly, the lack of flexibility in selecting the connection order is also a common problem in existing optimizers. The execution efficiency of database queries is closely related to the connection order, and different connection orders will produce significantly different execution costs. However, traditional optimizers often determine the connection order based on fixed heuristic rules or predefined strategies, and lack the ability to dynamically adjust the connection order according to the specific query characteristics, resulting in the generated query plan being difficult to adapt to the needs of complex multi-table connections. Improper connection order will increase the query execution time exponentially, especially in scenarios where large-scale, multi-table related queries need to be processed. The wrong connection order will greatly reduce system performance and increase query response time.

[0005] Therefore, how to overcome the limitations of the existing cost model and improve the flexibility of the connection order selection so that the database optimizer can generate query execution plans more accurately and efficiently has become an important research direction in the field of database query optimization. Especially when facing complex, multi-table related queries, optimizing the connection order can significantly reduce query overhead, improve system performance, and meet the query requirements of modern database systems. BRIEF DESCRIPTION OF THE DRAWINGS

[0006] Figure 1 It is the system flow chart. Summary of the invention

[0007] In view of the problems existing in the prior art, this application proposes a SQL query optimization method driven by deep reinforcement learning. The specific steps are as follows:

[0008] 1. SQL parsing: converting the SQL query statement submitted by the user into an intermediate representation that can be executed by the database;

[0009] 2. Determine the connection order based on deep reinforcement learning: Find the optimal execution order for multi-table connection queries through intelligent decision-making algorithms;

[0010] 3. Training based on dynamic progressive search strategy: Optimizing deep reinforcement learning models.

[0011] Furthermore, in step (2), the method for determining the connection order based on deep reinforcement learning is as follows:

[0012] 2.1 Initialize the SQL parsed encoded vector as state s and input it into the dual-depth Q network. * ,θ) and Q(s′,a * ,θ - ) is a linear relationship that calculates the target value y=r+γ[βQ(s′,a * ,θ)+(1-β)Q(s′,a * ,θ - )], and update the Q value to determine the optimal connection order, where a and a * is the action taken in state s, θ is the parameter of the current Q network, θ - is the parameter of the target Q network, Q(s′,a * ,θ) represents the maximum action value when the target network is in state s′, Q(s′,a * ,θ - ) represents the minimum action value when the target network is in state s′, r is the immediate reward, γ is the discount factor, β is the weight, β∈[0,1], when β=0, the network is a dual-depth Q network, when β=1, only the Q network is used to select actions; Among them, β0 is the initial weight, c is the hyperparameter, a * = argmax Q(s′,a;θ),a L =argmin Q(s′,a;θ).

[0013] 2.2 Calculation Where α is the learning rate, is the target Q value, Q(s,a) is the estimated Q value, Q′(s,a) is the updated estimated Q value, (s,s′) is the external knowledge factor based on state and action, K(s,s′)=exp(-d(s,s′) 2 / 2σ 2 ), d(s,s′) is the distance from state s to the target state s′.

[0014] 2.3 During the database query process, each state s selects the optimal action a according to the current Q value. After each action is selected, the state s is updated to the new state s′ and the connection operation is recorded. The above process is repeated until a complete connection sequence is generated.

[0015] Based on the above method, the present invention further proposes a SQL query optimization system driven by deep reinforcement learning, which includes:

[0016] 1. SQL parsing module: This module converts the SQL query statements submitted by the user into an intermediate representation that can be executed by the database;

[0017] 2. Connection order determination module based on deep reinforcement learning: This module finds the optimal execution order for multi-table connection queries through intelligent decision-making algorithms;

[0018] 3. Dynamic progressive search strategy training module: This module optimizes the deep reinforcement learning model.

[0019] Furthermore, in the connection order determination module based on deep reinforcement learning, the connection order determination method based on deep reinforcement learning is as follows:

[0020] 2.1 Initialize the SQL parsed encoded vector as state s and input it into the dual-depth Q network. * ,θ) and Q(s′,a * ,θ - ) is a linear relationship that calculates the target value y=r+γ[βQ(s′,a * ,θ)+(1-β)Q(s′,a * ,θ - )], and update the Q value to determine the optimal connection order, where a and a * is the action taken in state s, β is the parameter of the current Q network, θ -is the parameter of the target Q network, Q(s′,a * ,θ) represents the maximum action value when the target network is in state s′, Q(s′,a * ,θ - ) represents the minimum action value when the target network is in state s′, r is the immediate reward, γ is the discount factor, β is the weight, β∈[0,1], when β=0, the network is a dual-depth Q network, when β=1, only the Q network is used to select actions; Among them, β0 is the initial weight, c is the hyperparameter, a * = argmax Q(s′,a;θ),a L =argmin Q(s′,a;θ).

[0021] 2.2 Calculation Where α is the learning rate, is the target Q value, Q(s,a) is the estimated Q value, Q′(s,a) is the updated estimated Q value, (s,s′) is the external knowledge factor based on state and action, K(s,s′)=exp(-d(s,s′) 2 / 2σ 2 ), d(s,s′) is the distance from state s to the target state s′.

[0022] 2.3 During the database query process, each state s selects the optimal action a according to the current Q value. After each action is selected, the state s is updated to the new state s′ and the connection operation is recorded. The above process is repeated until a complete connection sequence is generated.

[0023] When the optimizer generates a physical query execution plan, the performance of the deep Q network is improved by combining action screening and state regression techniques. A more accurate Q value can be obtained by training the neural network with "action-state" pairs. In addition, by balancing the weights of the Q network and the dual deep Q network, the error of the target estimate is effectively reduced, thereby achieving a more accurate Q value estimation.

[0024] After adopting the present invention, the following technical effects are achieved:

[0025] (1) Flexible connection order optimization: Deep reinforcement learning parses SQL queries and dynamically adjusts the connection order so that the action values ​​of the target network and the evaluation network are optimally updated.

[0026] (2) Compared with traditional methods, the present invention can not only dynamically adjust the connection order in the query, but also make the best selection based on reward feedback and historical data. This optimization strategy significantly shortens the query time in complex multi-table connection queries, improves the efficiency of query execution, and avoids the generation of non-optimal plans. DETAILED DESCRIPTION

[0027] For the same query, there are different connection orders, and thus various connection execution plans, which may differ by several orders of magnitude. In order to improve the execution efficiency of SQL statements, this application is carried out in the stage of optimizing the physical query execution plan. Figure 1 shown.

[0028] First, the query request is sent to the database, and then the SQL statement is parsed, the query information and connection operation information are initialized, and input into the neural network to receive the connection status of the query. Next, the training phase begins, and a progressive search strategy is used to select the connection action. Different SQL statements have different network parameters and rewards. In the training phase, the dual-depth Q network is used to evaluate the maximum action value, etc. The dual-depth Q network is used to optimize the connection order, thereby realizing the formulation of the SQL connection plan. The ultimate goal is to generate a physical query execution plan, which will be optimized in the connection order to improve the execution efficiency of the query.

[0029] The main process is:

[0030] (1) SQL parsing

[0031] When using deep reinforcement learning to solve the connection order problem, the query request SQL statement and database information need to be encoded as the input of the neural network. Each query is represented as a query tree, where each table is a leaf node. The connection between tables is defined as adding nodes to the query tree. After each node is added, the corresponding leaf node will be removed to ensure the correctness of the connection order. The new leaf node will first connect to the subtree to preserve the sub-connection structure information between tables.

[0032] Each table to be connected is considered as a connection candidate, and all combinations of all the candidates are used to connect the tables to be connected. The above data representation behavior can well preserve the hierarchical information between tables, facilitate the capture of the structure information of the connection tree, and reduce the cost of the optimizer in cost estimation. At the same time, after training the data containing local connections, the combination can be expanded to multi-table connections with a larger number of connections.

[0033] Convert the SQL query statements submitted by the user into intermediate representations that can be executed by the database, such as parse trees, abstract syntax trees, etc.

[0034] (2) Determine the connection order based on deep reinforcement learning and find the optimal execution order for multi-table connection queries through intelligent decision-making algorithms;

[0035] In the phase where the optimizer generates physical query execution plans, the performance of the deep Q network can be improved by combining action screening and state regression techniques. Specifically, the neural network is trained using "action-state" pairs to obtain more accurate Q values. In addition, by balancing the weights of the Q network and the dual deep Q network, the error of the target estimate is effectively reduced, thereby achieving a more accurate Q value estimate.

[0036] The dual-depth Q network consists of two parts: an evaluation network for mining the action corresponding to the maximum value and an evaluation target network responsible for the maximum action value. The vectorized query plan tree enables the features of the query plan to be trained by the model to predict the performance cost of the query plan.

[0037] Initialize the SQL parsed encoded vector as state s and input it into the improved dual-depth Q network to calculate Q(s′,a * ,θ) and Q(s′,a * ,θ - ) to calculate the target value, where a and a * is the action that can be taken in a given state s, θ is the parameter of the Q network, θ - is the parameter of the target Q network, and the target value y is calculated as:

[0038] y=r+γ[βQ(s′,a*;θ)+(1-β)Q(s′,a*,θ - )]

[0039] Among them, Q(s′,a * ,θ) represents the maximum action value in the target network at state s′, Q(s′,a * ,θ - ) represents the minimum action value of state s′ in the target network, r is the immediate reward, γ is the discount factor, β is defined as the weight, and the action selection calculates the action value by weighting the evaluation network and the target network, where β∈[0,1], when β=0, the network is equivalent to a dual-depth Q network, when β=1, the algorithm completely ignores the dual-depth Q network evaluation and only uses the Q network to select actions. This design allows the network to have more precise control over the weights of different networks at different stages, improving the adaptability of the network. The weight β calculation formula is:

[0040]

[0041] Among them, β0 is the initial weight, c is the hyperparameter, and action a * It is represented as the action with the maximum action value of the evaluation network, that is, a * = argmax Q(s′,a;θ),a L Indicates the action with the minimum action value of the evaluation network, i.e., a L=argminQ(s′,a;θ). Therefore, Q(s′,a * ,θ - ) represents the maximum action value in the target network at state s′, Q(s′,a L ,θ - ) represents the minimum action value of state s′ in the target network.

[0042] When updating the Q value, the difference in the loss function is calculated through the mean square error, and the reward corresponding to the query action is obtained according to the cost model. Among them, α is the learning rate, which determines the size of the error learning. represents the target Q value, Q(s,a) represents the estimated Q value, and the Q value update process is as follows:

[0043]

[0044] Among them: α is the learning rate, is the target Q value, Q(s,a) is the estimated Q value, Q′(s,a) is the updated estimated Q value, (s,s′) is the external knowledge factor based on state and action, K(s,s′)=exp(-d(s,s′) 2 / 2σ 2 ), d(s,s′) is the distance from state s to the target state s′.

[0045] (3) Training process based on dynamic progressive search strategy: Gradually optimize the deep reinforcement learning model, especially in the connection order optimization task, and adjust the strategy through continuous training, so that the model can explore and utilize the search space more efficiently.

[0046] The dynamic progressive search strategy selects actions in three situations according to the size of the ε value, namely, selecting the action with the largest Q value for update, randomly selecting an action under the current situation, and using the softmax function to select an action.

[0047]

[0048] Among them, E dp (a) is the policy function for selecting actions, argmax Q(s,a) represents the action with the maximum Q value under the state, rand() is a random number in the range of (0,1), R(s) represents the randomly selected action under the state, P(s,a) is the action probability distribution calculated by the softmax function, and arg P(s,a) represents the action corresponding to P(s,a).

[0049] The process of gradually decreasing the ε value from 1 to 0 realizes the stage from exploration to development. According to the number of training steps, the parameter ε is dynamically adjusted with the ratio of high reward and low reward to achieve dynamic switching between stages. This method designs an expression that can control the change of parameter ε, such as

[0050] Among them, i is the current training step number, K is the training step number, K t represents the number of training rounds, K h Indicates that the sum of the reward values ​​during training is greater than R TH The number of episodes, K t Indicates that the sum of the reward values ​​during training is less than R TH The number of episodes, R TH is the boundary for distinguishing the training effect. u1, u2, and u3 represent the weights of each influencing factor. In the early stage of training, this value decreases slowly, but as the training progresses, the ε value increases with each round of K. h After the ε value drops to 0, a softmax-based action strategy training is added. Where v is a scalar and P(s,a) is the probability of action a being selected in state s. As learning progresses to this stage, each action has a different reward value, and as learning deepens, the system prefers actions with high reward values.

[0051] According to the exploration strategy, the connection action is selected and applied to the environment to obtain a new state. This process is repeated to obtain the final connection plan, and then the required data is returned to the database to complete the whole process.

[0052] The algorithm automatically selects the best connection order through the dual-depth Q network, and the generated physical query execution plan is optimized by the connection order, which can improve the query execution efficiency. In the process of generating the connection order, this application effectively reduces the error of the target estimate by balancing the weights of the Q network and the dual-depth Q network, and obtains a more accurate Q value estimate. Compared with traditional query optimization methods, this algorithm uses deep reinforcement learning technology to more intelligently optimize the connection order and improve the efficiency and accuracy of query execution.

[0053] A more specific embodiment:

[0054] Assume that there is a database containing multiple tables, including employees, departments, and salaries. The goal is to obtain salary information of employees under certain conditions from these tables to optimize the query execution plan.

[0055] Assume the SQL query statement is:

[0056] SELECT Employees.Name,Departments.DepartmentName,Salaries.Salary

[0057] FROM Employees

[0058] JOIN Departments ON Employees.DepartmentID=Departments.DepartmentID

[0059] JOIN Salaries ON Employees.EmployeeID=Salaries.EmployeeID

[0060] WHERE Employees.Name='John';

[0061] First, parse the SQL statement to identify the tables involved in the query (Employees and Salaries) as well as the connection condition (Employees.EmployeeID = Salaries.EmployeeID) and the filter condition (Employees.DepartmentID = 3).

[0062] Build a query tree: Take each table as a leaf node and build a query tree to represent the query structure. In this example, the query tree will contain two leaf nodes: Employees and Salaries.

[0063] Action selection and join order determination: In this example, assume the query state after parsing and vectorization:

[0064] s can be expressed as: s = [1,0,0,1,0,1,0,1,0,0,0,1,0,0,1,0,1,0,0]

[0065] Each element of the vector represents a specific node or relationship in the query tree.

[0066] Assume that during the calculation process, the action value given by the evaluation network is Q(s′,a * ; θ) = 50, the action value given by the target network is Q(s′,a * ,θ - )=45.

[0067] Assume that reward r = 10, discount factor γ = 0.9, and hyperparameter c = 0.1. According to the given formula, the weight β = 0.6 is calculated, and the final target value y = 53.2, which will be used in the loss function to update the Q value.

[0068] Substituting into the formula, we can get Q(s′,a′)=90, Q=91, and finally L(θ)=|91-80| 2 =121.

[0069] Next, actions are selected according to the dynamic progressive search strategy, and this process is repeated until convergence. Based on the connection sequence obtained through training, a physical query execution plan is generated to execute the query and return the required data.

[0070] In the calculation, the Q values ​​of the evaluation network and the target network were 50 and 45 respectively. Through weight adjustment and dynamic target value calculation, the final target value was 53.2, which was better than the situation where the static cost model could not be adaptively adjusted. Deep reinforcement learning parses SQL queries and dynamically adjusts the connection order to optimally update the action values ​​of the target network and the evaluation network.

[0071] Compared with traditional methods, the present invention can not only dynamically adjust the connection order in the query, but also make the best choice based on reward feedback and historical data. This optimization strategy significantly shortens the query time in complex multi-table connection queries, improves the efficiency of query execution, and avoids the generation of non-optimal plans.

[0072] The units, devices or modules described in the above embodiments can be implemented by computer chips or entities, or by products with certain functions. For the convenience of description, the above devices are described in various modules according to their functions. Of course, when implementing this application, the functions of each module can be implemented in the same or more software and / or hardware, or the modules that implement the same function can be implemented by a combination of multiple sub-modules or sub-units. The device embodiments described above are only schematic. For example, the division of the units is only a logical function division. There may be other division methods in actual implementation, such as multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the coupling or direct coupling or communication connection between each other shown or discussed can be through some interfaces, indirect coupling or communication connection of devices or units, which can be electrical, mechanical or other forms.

[0073] Those skilled in the art also know that, in addition to implementing the controller in a purely computer-readable program code, the method steps can be logically programmed so that the controller can implement the same function in the form of logic gates, switches, application-specific integrated circuits, programmable logic controllers, and embedded microcontrollers. Therefore, such a controller can be considered as a hardware component, and the devices for implementing various functions included therein can also be considered as structures within the hardware component. Or even, the devices for implementing various functions can be considered as both software modules for implementing the method and structures within the hardware component.

[0074] The present application may be described in the general context of computer-executable instructions executed by a computer, such as program modules. Generally, program modules include routines, programs, objects, components, data structures, classes, etc. that perform specific tasks or implement specific abstract data types. The present application may also be practiced in distributed computing environments where tasks are performed by remote processing devices connected through a communication network. In a distributed computing environment, program modules may be located in local and remote computer storage media, including storage devices.

[0075] It can be known from the description of the above implementation methods that those skilled in the art can clearly understand that the present application can be implemented by means of software plus a necessary general hardware platform. Based on such an understanding, the technical solution of the present application can be essentially or partly contributed to the prior art in the form of a software product, which can be stored in a storage medium such as ROM / RAM, a magnetic disk, an optical disk, etc., and includes several instructions for enabling a computer device (which can be a personal computer, a mobile terminal, a server, or a network device, etc.) to execute the methods described in the various embodiments of the present application or certain parts of the embodiments.

[0076] The various embodiments in this specification are described in a progressive manner, and the same or similar parts between the various embodiments can be referred to each other, and each embodiment focuses on the differences from other embodiments. The present application can be used in many general or special computer system environments or configurations. For example: personal computers, server computers, handheld devices or portable devices, tablet devices, multi-processor systems, microprocessor-based systems, set-top boxes, programmable electronic devices, network PCs, minicomputers, mainframe computers, distributed computing environments including any of the above systems or devices, etc.

[0077] The specific embodiments described above further illustrate the purpose, technical solutions and beneficial effects of the present application. It should be understood that the above description is only a specific embodiment of the present application and is not intended to limit the scope of protection of the present application. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the present application should be included in the scope of protection of the present application.

Claims

1. A SQL query optimization method driven by deep reinforcement learning, characterized in that: The specific steps are as follows: (1) SQL parsing: converting the SQL query statement submitted by the user into an intermediate representation that can be executed by the database; (2) Determine the connection order based on deep reinforcement learning: Find the optimal execution order for multi-table connection queries through intelligent decision-making algorithms; (3) Training based on dynamic progressive search strategy: Optimizing deep reinforcement learning models.

2. The SQL query optimization method based on deep reinforcement learning as claimed in claim 1, characterized in that: In step (2), the method for determining the connection order based on deep reinforcement learning is as follows: 2.1 Initialize the SQL parsed encoded vector as state s and input it into the dual-depth Q network. * ,θ) and Q(s′,a * ,θ - ) is a linear relationship that calculates the target value y=r+γ[βQ(s′,a * ,θ)+(1-β)Q(s′,a * ,θ - )], and update the Q value to determine the optimal connection order, where a and a * is the action taken in state s, θ is the parameter of the current Q network, θ - is the parameter of the target Q network, Q(s′,a * ,θ) represents the maximum action value when the target network is in state s′, Q(s′,a * ,θ - ) represents the minimum action value when the target network is in state s′, r is the immediate reward, γ is the discount factor, β is the weight, β∈[0,1], when β=0, the network is a dual-depth Q network, when β=1, only the Q network is used to select actions; Among them, β0 is the initial weight, c is the hyperparameter, a * = argmax Q(s′,a;θ),a L =argmin Q(s′,a;θ). 2.2 Calculation Where α is the learning rate, is the target Q value, Q(s,a) is the estimated Q value, Q′(s,a) is the updated estimated Q value, (s,s′) is the external knowledge factor based on state and action, K(s,s′)=exp(-d(s,s′) 2 / 2σ 2 ), d(s,s′) is the distance from state s to the target state s′. 2.3 During the database query process, each state s selects the optimal action a according to the current Q value. After each action is selected, the state s is updated to the new state s′ and the connection operation is recorded. The above process is repeated until a complete connection sequence is generated.

3. A SQL query optimization system driven by deep reinforcement learning, characterized by: The system includes: (1) SQL parsing module: This module converts the SQL query statement submitted by the user into an intermediate representation that can be executed by the database; (2) Connection order determination module based on deep reinforcement learning: This module finds the optimal execution order for multi-table connection queries through an intelligent decision-making algorithm; (3) Dynamic progressive search strategy training module: This module optimizes the deep reinforcement learning model.

4. The SQL query optimization system based on deep reinforcement learning as claimed in claim 3, characterized in that: In the connection order determination module based on deep reinforcement learning, the connection order determination method based on deep reinforcement learning is as follows: 2.1 Initialize the SQL parsed encoded vector as state s and input it into the dual-depth Q network. * ,θ) and Q(s′,a * ,θ - ) is a linear relationship that calculates the target value y=r+γ[βQ(s′,a * ,θ)+(1-β)Q(s′,a * ,θ - )], and update the Q value to determine the optimal connection order, where a and a * is the action taken in state s, θ is the parameter of the current Q network, θ - is the parameter of the target Q network, Q(s′,a * ,θ) represents the maximum action value when the target network is in state s′, Q(s′,a * ,θ - ) represents the minimum action value when the target network is in state s′, r is the immediate reward, γ is the discount factor, β is the weight, β∈[0,1], when β=0, the network is a dual-depth Q network, when β=1, only the Q network is used to select actions; Among them, β0 is the initial weight, c is the hyperparameter, a * = argmax Q(s′,a;θ),a L =argmin Q(s′,a;θ). 2.2 Calculation Where α is the learning rate, is the target Q value, Q(s,a) is the estimated Q value, Q′(s,a) is the updated estimated Q value, (s,s′) is the external knowledge factor based on state and action, K(s,s′)=exp(-d(s,s′) 2 / 2σ 2 ), d(s,s′) is the distance from state s to the target state s′. 2.3 During the database query process, each state s selects the optimal action a according to the current Q value. After each action is selected, the state s is updated to the new state s′ and the connection operation is recorded. The above process is repeated until a complete connection sequence is generated.

Images