Database execution plan optimization method and device, equipment, medium and product
By acquiring execution plan features, system load status, and historical performance data, fusing them into a fusion feature vector, and inputting it into a pre-trained reinforcement learning model, the execution plan is adjusted. This solves the problem of traditional query optimizers optimizing execution plans under dynamic loads and complex scenarios, achieving more efficient query optimization.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- JINZHUAN INFORMATION TECHNOLOGY CO LTD
- Filing Date
- 2026-01-28
- Publication Date
- 2026-05-15
AI Technical Summary
Traditional cost-based query optimizers struggle to generate optimal execution plans when faced with dynamically changing workloads and complex query scenarios.
By acquiring execution plan features, system load status, and historical performance data, these are fused into a fusion feature vector, which is then input into a pre-trained reinforcement learning model to obtain optimization and adjustment actions, thereby adjusting the execution plan.
Optimize execution plans for dynamic workloads and complex query scenarios to improve query efficiency and resource utilization.
Smart Images
Figure CN122045241A_ABST
Abstract
Description
Technical Field
[0001] This invention relates to the field of database technology, and in particular to a method, apparatus, device, medium, and product for optimizing database execution plans. Background Technology
[0002] With the advent of the big data era, the amount of data processed by database systems and the complexity of queries are growing exponentially. As the core output of the database query optimizer, the execution plan directly determines the efficiency of query execution and resource utilization.
[0003] Traditional cost-based optimizers (CBOs) rely on statistics and heuristic rules to generate execution plans, but they often struggle to generate optimal execution plans when faced with dynamically changing workloads and complex query scenarios. Summary of the Invention
[0004] This invention provides a method, apparatus, device, medium, and product for optimizing database execution plans, in order to solve the problem that existing cost-based query optimizers cannot adapt to dynamic workloads and complex query scenarios.
[0005] According to one aspect of the present invention, a method for optimizing a database execution plan is provided, comprising: In response to a user's query request, the system obtains the current execution plan for the query and extracts the execution plan features of the current execution plan. Obtain the current system load status of the database system and the historical performance data of the query statement, and extract and fuse the execution plan features, system load status and historical performance data to obtain a fused feature vector; The fused feature vector is input into a pre-trained reinforcement learning model to obtain the optimized adjustment action output by the reinforcement learning model for the current execution plan; Based on the optimization and adjustment actions, the current execution plan is adjusted, and the adjusted execution plan is used as the target plan for executing the query statement.
[0006] According to another aspect of the present invention, a database execution plan optimization apparatus is provided, comprising: The plan feature acquisition module is used to respond to a user's query request, acquire the current execution plan for the query statement, and extract the execution plan features of the current execution plan. The fusion feature vector determination module is used to obtain the current system load status of the database system and the historical performance data of the query statement, and to extract and fuse the execution plan features, system load status and historical performance data to obtain the fusion feature vector; The action determination module is used to input the fused feature vector into a pre-trained reinforcement learning model and obtain the optimized adjustment action output by the reinforcement learning model for the current execution plan. The execution plan adjustment module is used to adjust the current execution plan according to the optimization adjustment action, and use the adjusted execution plan as the target plan for executing the query statement.
[0007] According to another aspect of the present invention, an electronic device is provided, the electronic device comprising: At least one processor; and A memory communicatively connected to the at least one processor; wherein, The memory stores a computer program that can be executed by the at least one processor, the computer program being executed by the at least one processor to enable the at least one processor to perform the database execution plan optimization method according to any embodiment of the present invention.
[0008] According to another aspect of the present invention, a computer-readable storage medium is provided, the computer-readable storage medium storing computer instructions for causing a processor to execute and implement the database execution plan optimization method according to any embodiment of the present invention.
[0009] According to another aspect of the present invention, a computer program product is provided, comprising a computer program that, when executed by a processor, implements a method for optimizing a database execution plan according to any embodiment of the present disclosure.
[0010] The technical solution of this invention, in response to a user's query request, obtains the current execution plan for the query statement, extracts the execution plan features of the current execution plan, obtains the current system load status of the database system and the historical performance data of the query statement, and performs feature extraction and fusion of the execution plan features, system load status and historical performance data to obtain a fused feature vector. This fused feature vector is then input into a pre-trained reinforcement learning model to obtain the optimization and adjustment actions output by the reinforcement learning model for the current execution plan. Finally, based on the optimization and adjustment actions, the current execution plan is adjusted, and the adjusted execution plan is used as the target plan for executing the query statement. This achieves execution plan optimization based on execution plan features, system load status and historical performance data, enabling optimization of the execution plan under dynamic workloads and complex query scenarios.
[0011] It should be understood that the description in this section is not intended to identify key or essential features of the embodiments of the present invention, nor is it intended to limit the scope of the invention. Other features of the invention will become readily apparent from the following description. Attached Figure Description
[0012] To more clearly illustrate the technical solutions in the embodiments of the present invention, the accompanying drawings used in the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.
[0013] Figure 1 This is a flowchart of a database execution plan optimization method according to Embodiment 1 of the present invention; Figure 2 This is a flowchart of a database execution plan optimization method according to Embodiment 2 of the present invention; Figure 3 This is a schematic diagram of the structure of a database execution plan optimization device according to Embodiment 3 of the present invention; Figure 4 This is a schematic diagram of the structure of an electronic device that implements the database execution plan optimization method of the present invention. Detailed Implementation
[0014] To enable those skilled in the art to better understand the present invention, the technical solutions of the present invention will be clearly and completely described below with reference to the accompanying drawings of the embodiments of the present invention. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of the present invention.
[0015] It should be noted that the terms "first," "second," etc., in the specification, claims, and accompanying drawings of this invention are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of the invention described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover a non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.
[0016] Example 1 Figure 1 This is a flowchart illustrating a database execution plan optimization method according to Embodiment 1 of the present invention. This embodiment is applicable to situations where execution plan optimization is performed based on execution plan characteristics, system load status, and historical performance data. This method can be executed by a database execution plan optimization device, which can be implemented in hardware and / or software and can be configured in various general-purpose computing devices. Figure 1 As shown, the method includes: S110. In response to a user's query request, obtain the current execution plan for the query statement and extract the execution plan features of the current execution plan.
[0017] An execution plan is a tree or graph structure generated by the database query optimizer in response to a query request for a given query statement. It consists of a series of basic operators (such as table scans, index lookups, joins, sorting, and aggregations). The execution plan specifies the detailed steps and execution order in which the execution engine accesses data, performs calculations, and returns results.
[0018] In this embodiment of the invention, after detecting a query request initiated by a user based on a query statement, the current execution plan for the query statement is first obtained. Specifically, this can be generated by a cost-based query optimizer. The current execution plan is then parsed to obtain its characteristics. Specifically, starting from the root node of the execution plan, the entire plan tree structure can be traversed using either depth-first or breadth-first search. For each operator node encountered, its operator type, estimated execution cost (cost estimate), and unique attribute parameters of that operator node (e.g., the index name or join condition used) are recorded.
[0019] Simultaneously, the data flow between operator nodes is recorded to capture the structural information of the current execution plan. After traversal, all collected operator sequences, cost estimates, and data flow patterns are standardized and concatenated in a predetermined order into a fixed-dimensional, numerical execution plan feature.
[0020] S120. Obtain the current system load status of the database system and the historical performance data of the query statements, and extract and fuse the features of the execution plan, system load status and historical performance data to obtain a fused feature vector.
[0021] System load status is the dynamic usage status of database server hardware resources and internal queues at the time a query request occurs. For example, system load status includes CPU utilization, memory utilization, disk I / O busy rate, etc.
[0022] Historical performance data is statistical information on the actual performance metrics of the same query statement during multiple historical executions. For example, historical performance data includes tuple processing speed, cache hit rate, query execution time, and the actual execution cost of each operator in the execution plan.
[0023] In this embodiment of the invention, after obtaining the execution plan characteristics, it is also necessary to collect the current system load status of the database system, and process the system load status through feature engineering to obtain a load status feature vector. Simultaneously, historical performance data is queried for the current query statement, and the historical performance data is converted into a historical performance feature vector through feature engineering.
[0024] Finally, the load status feature vector, historical performance feature vector, and execution plan feature vector obtained from the fir tree will be fused by concatenation or weighted summation to obtain a fused feature vector.
[0025] Optionally, the execution plan features include at least one of operator sequence, cost estimation, and data flow pattern; the system load status includes at least one of CPU utilization, memory utilization, and disk I / O busy rate; and the historical performance data includes at least one of tuple processing speed, cache hit rate, query execution time, and the actual execution cost of each operator in the execution plan.
[0026] In this optional embodiment, specific details of execution plan characteristics, system load status, and historical performance data are provided: wherein, the execution plan characteristics include at least one of operator sequence, cost estimation, and data flow pattern; the system load status includes at least one of CPU utilization, memory utilization, and disk I / O busy rate; and the historical performance data includes at least one of tuple processing speed, cache hit rate, query execution time, and actual execution cost of each operator in the execution plan.
[0027] S130. Input the fused feature vector into the pre-trained reinforcement learning model and obtain the optimized adjustment action output by the reinforcement learning model for the current execution plan.
[0028] The reinforcement learning model is based on reinforcement learning models such as deep Q-networks, proximal policy optimization (PPO) models, and actor-critic models. It is pre-trained using a combination of the current state corresponding to the query statement, adjustment actions, the reward value corresponding to the action, and the next state after execution as sample data. The current state includes the execution plan characteristics of the execution plan corresponding to the query statement, the system load status, and the historical performance data of the query statement.
[0029] Optimization and adjustment actions are specific modifications to the current execution plan. For example, optimization and adjustment actions may include reordering operators in the execution plan, changing table join algorithms, adjusting data scanning methods, and modifying execution parallelism.
[0030] In this embodiment of the invention, the fused feature vector is used as the input state and input to the pre-trained reinforcement learning model. The reinforcement learning model analyzes the input state through its internal policy network and, based on its learned policy, selects an adjustment action from a predefined action space that yields the maximum reward value under the current input state as the optimization adjustment action. Specific actions in the action space may include reordering operators in the execution plan, changing the table join algorithm, adjusting the data scanning method, and modifying the execution parallelism, etc.
[0031] S140. Based on the optimization and adjustment actions, adjust the current execution plan and use the adjusted execution plan as the target plan for executing the query statement.
[0032] In this embodiment of the invention, after obtaining the optimization and adjustment action, the current execution plan is adjusted according to the optimization and adjustment action, and the adjusted execution plan is used as an execution query statement to obtain the target plan. For example, if the optimization and adjustment action is "changing the join algorithm from nested loop to hash join", then the join operator node in the execution plan can be located, and its algorithm data can be modified to obtain the adjusted execution plan.
[0033] Optionally, after using the adjusted execution plan as the target plan for executing the query statement, the method further includes: Obtain the actual performance metrics of the adjusted execution plan when executing the query statement; Based on the actual performance indicators and the preset benchmark performance, the immediate reward generated by performing the optimization adjustment action is calculated; the immediate reward is positively correlated with the performance improvement. The current state at the time of executing this query request, the optimization and adjustment action, the immediate reward, and the next state after execution will be used as new sample data; the current state includes execution plan characteristics, system load status, and historical performance data. Based on the new sample data, the pre-trained reinforcement learning model is incrementally updated online.
[0034] In this optional embodiment, specific steps are provided after using the adjusted execution plan as the target plan for executing the query statement: Obtaining the actual performance metrics of the adjusted execution plan when executing the query statement. These actual performance metrics reflect the optimization effect of the adjustment actions in a real-world operating environment and are fundamentally different from the estimated metrics before optimization. The actual performance metrics may include tuple processing speed, cache hit rate, query execution time, and the actual execution cost of each operator in the execution plan. In addition, they may also include total execution time and peak CPU usage time.
[0035] Based on actual performance metrics and preset baseline performance, the immediate reward generated by the optimization and adjustment actions is calculated. The immediate reward is positively correlated with the performance improvement. The baseline performance can be obtained by calculating the average of actual performance metrics over a set historical period. Specifically, the actual performance metrics and preset baseline performance can be substituted into the reward value calculation function to calculate the immediate reward generated by the optimization and adjustment actions.
[0036] The current state at the time of executing this query request, the optimization and adjustment actions, the immediate reward, and the next state after execution are used as new sample data. The current state includes the execution plan characteristics, system load status, and historical performance data collected after the query request was initiated. The next state is the execution plan characteristics, system load status, and historical performance data collected and constructed after the query request is completed. The execution plan characteristics are the features of the adjusted execution plan, the system load status is the system load status after execution, and the historical performance data includes the new performance data obtained in this execution, compared to the historical performance data in the current state. Finally, based on the new sample data, the pre-trained reinforcement learning model is incrementally updated online. This online incremental update of the model based on actual operating data allows the reinforcement learning model to continuously learn and adapt to changes in the load environment.
[0037] The technical solution of this invention, in response to a user's query request, obtains the current execution plan for the query statement, extracts the execution plan features of the current execution plan, obtains the current system load status of the database system and the historical performance data of the query statement, and performs feature extraction and fusion of the execution plan features, system load status and historical performance data to obtain a fused feature vector. This fused feature vector is then input into a pre-trained reinforcement learning model to obtain the optimization and adjustment actions output by the reinforcement learning model for the current execution plan. Finally, based on the optimization and adjustment actions, the current execution plan is adjusted, and the adjusted execution plan is used as the target plan for executing the query statement. This achieves execution plan optimization based on execution plan features, system load status and historical performance data, enabling optimization of the execution plan under dynamic workloads and complex query scenarios.
[0038] Example 2 Figure 2 This is a flowchart of a database execution plan optimization method provided in Embodiment 2 of the present invention. This embodiment further refines the above embodiment, providing specific steps for inputting the fused feature vector into a pre-trained reinforcement learning model, obtaining the optimization adjustment actions output by the reinforcement learning model for the current execution plan, and adjusting the current execution plan according to the optimization adjustment actions, and using the adjusted execution plan as the target plan for executing the query statement. Figure 2 As shown, the method includes: S210. In response to a query request initiated by a user, obtain the current execution plan for the query statement and extract the execution plan features of the current execution plan.
[0039] S220. Obtain the current system load status of the database system and the historical performance data of the query statements, and extract and fuse the execution plan features, system load status and historical performance data to obtain a fused feature vector.
[0040] S230. Input the fused feature vector into the pre-trained reinforcement learning model, and evaluate the action value of the candidate adjustment action based on the fused feature vector through the reinforcement learning model.
[0041] Candidate adjustment actions are a predefined action space consisting of basic optimization operations that can be executed by the database execution plan. Each candidate adjustment action corresponds to an atomic modification to the execution plan.
[0042] In this embodiment of the invention, the fused feature vector is input into a pre-trained reinforcement learning model. The value network in the reinforcement learning model performs nonlinear processing on the input fused feature vector, and outputs an action value assessment for each candidate adjustment action. For example, if the action space contains 10 candidate adjustment actions, the model outputs a 10-dimensional vector, where the i-th vector value represents the expected reward value of taking the i-th candidate adjustment action in the current state, i.e., the action value assessment.
[0043] Optionally, candidate adjustment actions include at least one of the following: reordering operators in the execution plan, changing the table join algorithm, adjusting the data scanning method, and modifying the execution parallelism.
[0044] S240. Based on the action value assessment, select the action with the highest value assessment from the candidate adjustment actions as the optimized adjustment action output.
[0045] In this embodiment of the invention, after the model outputs the action value evaluation vector, the entire value evaluation vector is traversed, and the target element with the largest value is found through pairwise comparison operations. The candidate adjustment action corresponding to the target element is then output as the optimized adjustment action.
[0046] Optionally, the reinforcement learning model can be trained in the following way: When executing a query statement in the database system, the current status is collected; the current status includes the execution plan characteristics of the execution plan corresponding to the query statement, the system load status, and the historical performance data of the query statement; By using a preset exploration strategy, the system selects adjustment actions based on the current state and updates the execution plan corresponding to the query statement based on these adjustment actions. After executing the query statement based on the adjusted execution plan, the actual performance metrics are obtained, and the reward value obtained due to the adjustment action is calculated based on the actual performance metrics and the preset baseline performance. The current state, adjustment action, reward value, and the next state after execution are combined to form a sample data and stored in the experience replay buffer. Batch sample data is periodically extracted from the experience replay buffer to maximize the reward value, and the reinforcement learning model is trained in batches until the model performance converges or the preset number of iterations is reached.
[0047] In this optional embodiment, a specific training method for the reinforcement learning model is provided: When a query is executed in the data system, the current state is collected, including the execution plan characteristics of the corresponding execution plan, the system load status, and the historical performance data of the query. An adjustment action is selected based on the current state using a preset exploration strategy, and the execution plan corresponding to the query is updated based on the adjustment action. The exploration measurement can be an ε-greedy strategy. The selected adjustment action is applied to the current execution plan, generating an adjusted execution plan, which is then delivered to the database execution engine for execution.
[0048] After running the adjusted execution plan, the actual performance metrics are obtained. Based on the difference between the actual performance metrics and the preset baseline performance, the reward value obtained due to the adjustment action is calculated. The current state, the adjustment action, the reward value, and the next state after execution are combined to form a sample data, which is stored in the experience replay buffer. The baseline performance can be the historical average performance metric of the query statement before the start of this training.
[0049] After constructing the sample data, batch sample data is periodically extracted from the experience replay buffer to maximize the reward value. This batch training is performed on the reinforcement learning model until the model performance converges or the preset number of iterations is reached. The core objective of the training algorithm is to adjust the internal network parameters of the reinforcement learning model so that the model's value prediction for the optimal adjustment action in a given state is closer to the true optimal value target.
[0050] S250. Based on the optimization and adjustment action, adjust the current execution plan, and randomly allocate the query requests of the received query statements to the adjusted execution plan or the current execution plan for execution according to a preset ratio.
[0051] In this embodiment of the invention, the current execution plan is adjusted according to the optimization and adjustment actions to obtain the adjusted execution plan. When a query request for the same query statement is received again, traffic routing is performed according to a preset ratio, randomly assigning the request to either the adjusted execution plan or the current execution plan. For example, the preset ratio is 20%:80%. Therefore, through traffic routing, 20% of the subsequent query requests for the same query statement are assigned to the adjusted execution plan for execution, serving as the experimental group; the remaining 80% are assigned to the current execution plan for execution, serving as the control group.
[0052] S260, Obtain the first performance metric of the adjusted execution plan and the second performance metric of the current execution plan.
[0053] In this embodiment of the invention, a first performance metric of the adjusted execution plan and a second performance metric of the current execution plan are obtained. The first and second performance metrics include at least one of the following: tuple processing speed, cache hit rate, query execution time, and the actual execution cost of each operator in the execution plan.
[0054] S270. If the improvement of the first performance indicator compared to the second performance indicator exceeds a set threshold, confirm that the adjusted execution plan will be used as the target plan for executing the query statement.
[0055] In this embodiment of the invention, corresponding items in the first and second performance metrics are compared. If the improvement of the first performance metric compared to the second performance metric exceeds a set threshold, the adjusted execution plan is confirmed as the target plan for subsequent executions of the query statement. That is, when a query request for the query statement is received again, the target plan is directly used for execution. By directing only a portion of the traffic to the adjusted execution plan, the potential risk of performance regression is limited to a controllable range. Even if there are problems with the adjusted execution plan, it will only affect a small number of requests, ensuring the overall stability of the system and business continuity.
[0056] The technical solution of this invention, in response to a user's query request, obtains the current execution plan for the query statement, extracts the execution plan features of the current execution plan, obtains the current system load status of the database system, and the historical performance data of the query statement. It then performs feature extraction and fusion of the execution plan features, system load status, and historical performance data to obtain a fused feature vector. This fused feature vector is input into a pre-trained reinforcement learning model. Based on the fused feature vector, the reinforcement learning model evaluates the action value of candidate adjustment actions. According to the action value evaluation, the action with the highest value evaluation is selected as the optimized adjustment action output. Based on the optimized adjustment action... The current execution plan is adjusted, and the query requests of the received query statements are randomly assigned to the adjusted execution plan or the current execution plan according to a preset ratio. The first performance index of the adjusted execution plan and the second performance index of the current execution plan are obtained. If the improvement of the first performance index compared with the second performance index exceeds a set threshold, the adjusted execution plan is confirmed as the target plan for executing the query statement. Using the adjusted execution plan as the target plan for executing the query statement can optimize the execution plan under dynamic workloads and complex query scenarios. On the other hand, the safe implementation of the adjusted execution plan is ensured through comparative testing.
[0057] Example 3 Figure 3 This is a schematic diagram of a database execution plan optimization device provided in Embodiment 3 of the present invention. Figure 3 As shown, the device includes: The plan feature acquisition module 310 is used to respond to a query request initiated by a user, acquire the current execution plan for the query statement, and extract the execution plan features of the current execution plan; The feature vector fusion determination module 320 is used to obtain the current system load status of the database system and the historical performance data of the query statement, and to extract and fuse the execution plan features, system load status and historical performance data to obtain a fused feature vector. The action determination module 330 is used to input the fused feature vector into a pre-trained reinforcement learning model and obtain the optimized adjustment action output by the reinforcement learning model for the current execution plan. The execution plan adjustment module 340 is used to adjust the current execution plan according to the optimization adjustment action, and use the adjusted execution plan as the target plan for executing the query statement.
[0058] The technical solution of this invention, in response to a user's query request, obtains the current execution plan for the query statement, extracts the execution plan features of the current execution plan, obtains the current system load status of the database system and the historical performance data of the query statement, and performs feature extraction and fusion of the execution plan features, system load status and historical performance data to obtain a fused feature vector. This fused feature vector is then input into a pre-trained reinforcement learning model to obtain the optimization and adjustment actions output by the reinforcement learning model for the current execution plan. Finally, based on the optimization and adjustment actions, the current execution plan is adjusted, and the adjusted execution plan is used as the target plan for executing the query statement. This achieves execution plan optimization based on execution plan features, system load status and historical performance data, enabling optimization of the execution plan under dynamic workloads and complex query scenarios.
[0059] Optionally, the database execution plan optimization apparatus also includes a model training module, specifically used for: When a query statement is executed in the database system, the current status is collected; the current status includes the execution plan characteristics of the execution plan corresponding to the query statement, the system load status, and the historical performance data of the query statement; By using a preset exploration strategy, an adjustment action is selected based on the current state, and the execution plan corresponding to the query statement is updated based on the adjustment action; After executing the query statement based on the adjusted execution plan, the actual performance indicators are obtained, and the reward value obtained due to the adjustment action is calculated based on the actual performance indicators and the preset benchmark performance. The current state, the adjustment action, the reward value, and the next state after execution are combined to form a sample data and stored in the experience replay buffer. Batch sample data is periodically extracted from the experience replay buffer to maximize the reward value, and the reinforcement learning model is trained in batches until the model performance converges or the preset number of iterations is reached.
[0060] Optionally, the execution plan features include at least one of operator sequence, cost estimation, and data flow pattern; the system load status includes at least one of CPU utilization, memory utilization, and disk I / O busy rate; and the historical performance data includes at least one of tuple processing speed, cache hit rate, query execution time, and the actual execution cost of each operator in the execution plan.
[0061] Optionally, the database execution plan optimization apparatus also includes: The actual performance metrics acquisition module is used to acquire the actual performance metrics of the adjusted execution plan when executing the query statement after using the adjusted execution plan as the target plan for executing the query statement. The instant reward calculation module is used to calculate the instant reward generated by performing the optimization adjustment action based on the actual performance indicators and the preset benchmark performance; the instant reward is positively correlated with the performance improvement. The sample construction module is used to take the current state at the time of executing this query request, the optimization and adjustment action, the immediate reward, and the next state after execution as new sample data; the current state includes execution plan characteristics, system load status, and historical performance data. The model optimization module is used to perform online incremental updates to the pre-trained reinforcement learning model based on the new sample data.
[0062] Optionally, the adjustment action determination module 330 is specifically used for: The fused feature vector is input into a pre-trained reinforcement learning model, and the reinforcement learning model evaluates the action value of candidate adjustment actions based on the fused feature vector. Based on the action value assessment, the action with the highest value assessment is selected from the candidate adjustment actions as the optimized adjustment action output.
[0063] Optionally, the candidate adjustment actions include at least one of the following: reordering operators in the execution plan, changing the table join algorithm, adjusting the data scanning method, and modifying the execution parallelism.
[0064] Optionally, the execution plan adjustment module 340 includes: The execution test unit is used to adjust the current execution plan according to the optimization adjustment action, and randomly allocate the query requests of the query statement received again to the adjusted execution plan or the current execution plan for execution according to a preset ratio; The test metric acquisition unit is used to acquire the first performance metric of the adjusted execution plan and the second performance metric of the current execution plan; The target plan confirmation unit is used to use the adjusted execution plan as the target plan for executing the query statement when the improvement of the first performance indicator compared with the second performance indicator exceeds a set threshold.
[0065] The database execution plan optimization apparatus provided in this embodiment of the invention can execute the database execution plan optimization method provided in any embodiment of the invention, and has the corresponding functional modules and beneficial effects of the execution method.
[0066] In the technical solution of this invention, the information collected is information and data authorized by the user or fully authorized by all parties. The collection, storage, use, processing, transmission, provision, disclosure and application of related data all comply with the relevant laws, regulations and standards of relevant countries and regions, take necessary confidentiality measures, do not violate public order and good morals, and provide corresponding operation entry points for users to choose to authorize or refuse.
[0067] Example 4 According to embodiments of the present invention, the present invention also provides an electronic device, a readable storage medium, and a computer program product.
[0068] Figure 4 A schematic diagram of an electronic device 10 that can be used to implement embodiments of the present invention is shown. The electronic device is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, application processors, blade application processors, mainframe computers, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as personal digital processors, cellular phones, smartphones, wearable devices (such as helmets, glasses, watches, etc.), and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely illustrative and are not intended to limit the implementation of the invention described and / or claimed herein.
[0069] like Figure 4 As shown, the electronic device 10 includes at least one processor 11 and a memory, such as a read-only memory (ROM) 12 or a random access memory (RAM) 13, communicatively connected to the at least one processor 11. The memory stores computer programs executable by the at least one processor. The processor 11 can perform various appropriate actions and processes based on the computer program stored in the ROM 12 or loaded from storage unit 18 into the RAM 13. The RAM 13 can also store various programs and data required for the operation of the electronic device 10. The processor 11, ROM 12, and RAM 13 are interconnected via a bus 14. An input / output (I / O) interface 15 is also connected to the bus 14.
[0070] Multiple components in electronic device 10 are connected to I / O interface 15, including: input unit 16, such as keyboard, mouse, etc.; output unit 17, such as various types of displays, speakers, etc.; storage unit 18, such as disk, optical disk, etc.; and communication unit 19, such as network card, modem, wireless transceiver, etc. Communication unit 19 allows electronic device 10 to exchange information / data with other devices through computer networks such as the Internet and / or various telecommunications networks.
[0071] Processor 11 can be a variety of general-purpose and / or special-purpose processing components with processing and computing capabilities. Some examples of processor 11 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various special-purpose artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, a digital signal processor (DSP), and any suitable processor, controller, microcontroller, etc. Processor 11 performs the various methods and processes described above, such as methods for optimizing database execution plans.
[0072] In some embodiments, the database execution plan optimization method may be implemented as a computer program tangibly contained in a computer-readable storage medium, such as storage unit 18. In some embodiments, part or all of the computer program may be loaded and / or mounted on electronic device 10 via ROM 12 and / or communication unit 19. When the computer program is loaded into RAM 13 and executed by processor 11, one or more steps of the database execution plan optimization method described above may be performed. Alternatively, in other embodiments, processor 11 may be configured to execute the database execution plan optimization method by any other suitable means (e.g., by means of firmware).
[0073] Various implementations of the systems and techniques described above herein can be implemented in digital electronic circuit systems, integrated circuit systems, field-programmable gate arrays (FPGAs), application-specific integrated circuits (ASICs), application-specific standard products (ASSPs), systems-on-a-chip (SoCs), complex programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various implementations may include: implementations in one or more computer programs that can be executed and / or interpreted on a programmable system including at least one programmable processor, which may be a dedicated or general-purpose programmable processor, capable of receiving data and instructions from a storage system, at least one input device, and at least one output device, and transmitting data and instructions to the storage system, the at least one input device, and the at least one output device.
[0074] Computer programs used to implement the methods of the present invention can be written in any combination of one or more programming languages. These computer programs can be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, such that when executed by the processor, the computer programs cause the functions / operations specified in the flowcharts and / or block diagrams to be performed. The computer programs can be executed entirely on a machine, partially on a machine, as a standalone software package partially on a machine and partially on a remote machine, or entirely on a remote machine or application.
[0075] In the context of this invention, a computer-readable storage medium can be a tangible medium that may contain or store a computer program for use by or in conjunction with an instruction execution system, apparatus, or device. A computer-readable storage medium may include, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatus, or devices, or any suitable combination thereof. Alternatively, a computer-readable storage medium may be a machine-readable signal medium. More specific examples of machine-readable storage media include electrical connections based on one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fibers, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof.
[0076] To provide interaction with a user, the systems and techniques described herein can be implemented on an electronic device having: a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user; and a keyboard and pointing device (e.g., a mouse or trackball) through which the user provides input to the electronic device. Other types of devices can also be used to provide interaction with the user; for example, feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including sound input, voice input, or tactile input).
[0077] The systems and technologies described herein can be implemented in computing systems that include backend components (e.g., as data application processors), or computing systems that include middleware components (e.g., application application processors), or computing systems that include frontend components (e.g., user computers with graphical user interfaces or web browsers through which users can interact with implementations of the systems and technologies described herein), or any combination of such backend, middleware, or frontend components. The components of the system can be interconnected via digital data communication of any form or medium (e.g., communication networks). Examples of communication networks include local area networks (LANs), wide area networks (WANs), blockchain networks, and the Internet.
[0078] A computing system can include clients and applications. Clients and applications are generally geographically separated and typically interact via a communication network. The client-application relationship is established by computer programs running on the respective computers and having a client-application relationship with each other. An application can be a cloud application, also known as a cloud computing application or cloud server, which is a hosting product within the cloud computing application ecosystem. It addresses the shortcomings of traditional physical servers and VPS applications, such as high management difficulty and weak business scalability.
[0079] It should be understood that the various forms of processes shown above can be used, with steps reordered, added, or deleted. For example, the steps described in this invention can be executed in parallel, sequentially, or in different orders, as long as the desired result of the technical solution of this invention can be achieved, and this is not limited herein.
[0080] The specific embodiments described above do not constitute a limitation on the scope of protection of this invention. Those skilled in the art should understand that various modifications, combinations, sub-combinations, and substitutions can be made according to design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of this invention should be included within the scope of protection of this invention.
Claims
1. A method for optimizing a database execution plan, characterized in that, include: In response to a user's query request, the system obtains the current execution plan for the query and extracts the execution plan features of the current execution plan. Obtain the current system load status of the database system and the historical performance data of the query statement, and extract and fuse the execution plan features, system load status and historical performance data to obtain a fused feature vector; The fused feature vector is input into a pre-trained reinforcement learning model to obtain the optimized adjustment action output by the reinforcement learning model for the current execution plan; Based on the optimization and adjustment actions, the current execution plan is adjusted, and the adjusted execution plan is used as the target plan for executing the query statement.
2. The method according to claim 1, characterized in that, The reinforcement learning model is trained in the following manner: When a query statement is executed in the database system, the current status is collected; the current status includes the execution plan characteristics of the execution plan corresponding to the query statement, the system load status, and the historical performance data of the query statement; By using a preset exploration strategy, an adjustment action is selected based on the current state, and the execution plan corresponding to the query statement is updated based on the adjustment action; After executing the query statement based on the adjusted execution plan, the actual performance indicators are obtained, and the reward value obtained due to the adjustment action is calculated based on the actual performance indicators and the preset benchmark performance. The current state, the adjustment action, the reward value, and the next state after execution are combined to form a sample data and stored in the experience replay buffer. Batch sample data is periodically extracted from the experience replay buffer to maximize the reward value, and the reinforcement learning model is trained in batches until the model performance converges or the preset number of iterations is reached.
3. The method according to claim 1, characterized in that, The execution plan features include at least one of operator sequence, cost estimation, and data flow pattern; the system load status includes at least one of CPU utilization, memory utilization, and disk I / O busy rate; the historical performance data includes at least one of tuple processing speed, cache hit rate, query execution time, and actual execution cost of each operator in the execution plan.
4. The method according to claim 1, characterized in that, After using the adjusted execution plan as the target plan for executing the query statement, the process also includes: Obtain the actual performance metrics of the adjusted execution plan when executing the query statement; Based on the actual performance indicators and the preset benchmark performance, the immediate reward generated by performing the optimization adjustment action is calculated; the immediate reward is positively correlated with the performance improvement. The current state at the time of executing this query request, the optimization and adjustment action, the immediate reward, and the next state after execution will be used as new sample data; the current state includes execution plan characteristics, system load status, and historical performance data. Based on the new sample data, the pre-trained reinforcement learning model is incrementally updated online.
5. The method according to claim 1, characterized in that, The fused feature vector is input into a pre-trained reinforcement learning model to obtain the optimization adjustment actions output by the reinforcement learning model for the current execution plan, including: The fused feature vector is input into a pre-trained reinforcement learning model, and the reinforcement learning model evaluates the action value of candidate adjustment actions based on the fused feature vector. Based on the action value assessment, the action with the highest value assessment is selected from the candidate adjustment actions as the optimized adjustment action output.
6. The method according to claim 5, characterized in that, The candidate adjustment actions include at least one of the following: reordering operators in the execution plan, changing the table join algorithm, adjusting the data scanning method, and modifying the execution parallelism.
7. The method according to claim 1, characterized in that, Based on the optimization and adjustment actions, the current execution plan is adjusted, and the adjusted execution plan is used as the target plan for executing the query statement, including: Based on the optimization and adjustment actions, the current execution plan is adjusted, and the query requests of the query statements received again are randomly allocated to the adjusted execution plan or the current execution plan for execution according to a preset ratio; Obtain the first performance metric of the adjusted execution plan and the second performance metric of the current execution plan; If the improvement of the first performance indicator compared to the second performance indicator exceeds a set threshold, the adjusted execution plan will be used as the target plan for executing the query statement.
8. A database execution plan optimization device, characterized in that, include: The plan feature acquisition module is used to respond to a user's query request, acquire the current execution plan for the query statement, and extract the execution plan features of the current execution plan. Fusion The feature vector determination module is used to obtain the current system load status of the database system and the historical performance data of the query statement, and to extract and fuse the execution plan features, system load status and historical performance data to obtain a fused feature vector. The action determination module is used to input the fused feature vector into a pre-trained reinforcement learning model and obtain the optimized adjustment action output by the reinforcement learning model for the current execution plan. The execution plan adjustment module is used to adjust the current execution plan according to the optimization adjustment action, and use the adjusted execution plan as the target plan for executing the query statement.
9. An electronic device, characterized in that, The electronic device includes: At least one processor; and A memory communicatively connected to the at least one processor; wherein, The memory stores a computer program that can be executed by the at least one processor, the computer program being executed by the at least one processor to enable the at least one processor to perform the database execution plan optimization method according to any one of claims 1-7.
10. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer instructions that cause a processor to execute the method for optimizing the database execution plan according to any one of claims 1-7.
11. A computer program product, characterized in that, The computer program product includes a computer program that, when executed by a processor, implements the method for optimizing a database execution plan according to any one of claims 1-7.