Database intelligent index recommendation method and system based on reinforcement learning
By introducing a reward function with a comprehensive consideration of multiple factors in database index recommendation, the problem of insufficient comprehensive index optimization effect and resource consumption in the existing technology is solved, the accuracy and applicability of the index recommendation algorithm are improved, and more efficient database performance is achieved and system stability.
Patent Information
- Application Number
- CN202411140440.7
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-08-19
- Publication Date
- 2024-11-29
- Estimated Expiration
- 2044-08-19
AI Technical Summary
The existing database index recommendation algorithm mainly relies on QphH@size in the TPC-H database performance evaluation indicators, ignoring key factors such as query execution plan quality and index size, resulting in insufficient comprehensive index optimization effect.
A database intelligent index recommendation method based on reinforcement learning is proposed. By defining reward functions that are comprehensively considered by multiple factors, including index optimization effect, size, resource consumption, etc., the index action is optimized to improve the accuracy and applicability of the index recommendation algorithm.
A more comprehensive evaluation of index actions is achieved, over-consumption of resources is avoided, database performance and system robustness are improved, query response time and disk I/O consumption are reduced.
Smart Images

Figure CN119046505A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to a method and system for recommending database intelligent indexes based on reinforcement learning, and belongs to the field of database intelligent index recommendation. Background Art
[0002] With the rapid development of the information age, database index recommendation has become increasingly critical in improving database performance, reducing query response time, and meeting user needs. In order to optimize the index configuration of the database, current research focuses on database index recommendation algorithms based on reinforcement learning. However, the performance evaluation of these algorithms often relies on QphH@size in the TPC-H database performance evaluation index. This index mainly focuses on the time efficiency of query execution, but ignores some key aspects such as the quality of query execution plans and index size, which limits the comprehensive evaluation of index optimization effects.
[0003] QphH@size in the TPC-H database performance evaluation index mainly measures the query throughput of the database under a specific load and is an important criterion for measuring database performance. However, this index mainly focuses on the efficiency of query execution time and cannot fully evaluate the quality of the query execution plan. The following are some limitations of this index:
[0004] (1) Limited to query execution time: QphH@size mainly focuses on query execution time, but ignores other factors related to query performance, such as resource utilization, memory consumption, etc. The optimization effect of an index is not only reflected in the reduction of execution time, but may also have an important impact on the overall performance of the database.
[0005] (2) Ignoring the quality of query execution plans: This indicator cannot provide detailed information about the quality of query execution plans. The introduction of an index will affect the selection of query execution plans, thereby affecting the overall performance. QphH@size cannot provide an intuitive understanding of the quality of query execution plans, thus limiting the in-depth analysis of index configuration.
[0006] (3) Not considering factors such as index size: This indicator does not consider factors such as index size, which is also one of the important factors affecting performance. An overly large or undersized index may lead to performance degradation, but these are not included in the QphH@size evaluation system.
[0007] In the index recommendation system, the quality of the reward function will directly affect the final effect of the reinforcement learning algorithm. Therefore, choosing a reward function that can effectively evaluate the quality of index actions is crucial for the index selection problem. When evaluating the quality of index actions, factors such as the optimization effect of the index and the size of the index need to be considered. BRIEF DESCRIPTION OF THE DRAWINGS
[0008] Figure 1 This is an architecture diagram of the database intelligent indexing recommendation method based on reinforcement learning. Summary of the invention
[0009] In order to solve the above problems, the present invention proposes a database intelligent index recommendation method based on reinforcement learning, and the specific steps include:
[0010] (1) Randomly initialize the weight θ of the action parameter and delete all indexes in the database.
[0011] (2) Define the action space of the index recommendation system.
[0012] (3) Select the appropriate index operation to minimize the reward function value.
[0013] (4) Collect data using simulated or real database query scenarios and construct a training set; build a reinforcement learning model; after the construction is completed, use the collected data to train the model and optimize the model parameters through the reward function to change the index action.
[0014] (5) Deploy the trained model to the actual database system and continuously optimize the model to adapt to changes in database performance and new query patterns.
[0015] Furthermore, in step (3), the reward function is calculated Among them, ΔQphH@size represents the difference of QphH@size before and after using index I, and QphH@size represents the query processing capacity under a specific database size. I utility(I) represents the optimization effect of index I, N is the number of different query types in the workload, and I size(I) Indicates the size of index I, K k represents the number of times the k-th query appears in the workload, Represents the query Q k The estimated cost when there is no index I, Profit(I,Q k ) represents the query Q k An estimate of the query cost after index I is created.
[0016] Furthermore, the calculation Among them, T base For query Q k Benchmark execution time without index I; P impro (I,Q k ) is the index I for query Q k Performance improvement factor; γ×Uupdates (I) is the index update cost, γ is the average cost of each update, and U updates (I) is the total number of update operations on index I within a certain period of time; δ is the reconstruction cost per unit index size, |I| is the index size, ∈ is the basic storage cost per unit index size; ζ is the storage cost per unit complexity, U com (I) is the complexity measure of the index structure.
[0017] Based on the above method, the present invention further proposes a database intelligent index recommendation system based on reinforcement learning, which includes:
[0018] (1) Initialization module: This module randomly initializes the weights θ of the action parameters and deletes all indexes in the database.
[0019] (2) Action space definition module, which defines the action space of the index recommendation system, that is, the index creation or deletion operations that the system can take.
[0020] (3) A reward function optimization module, which selects the appropriate index operation to minimize the reward function value.
[0021] (4) A model training module, which uses simulated or real database query scenarios to collect data and build a training set; builds a reinforcement learning model; and after the construction is completed, uses the collected data to train the model and optimizes the model parameters through the reward function to change the index action.
[0022] (5) Deployment and optimization module, which deploys the trained model to the actual database system and continuously optimizes the model to adapt to database performance changes and new query patterns.
[0023] Furthermore, in the reward function optimization module, the reward function is calculated Among them, ΔQphH@size represents the difference of QphH@size before and after using index I, and QphH@size represents the query processing capacity under a specific database size. I utility(I) represents the optimization effect of index I, N is the number of different query types in the workload, and I size(I) Indicates the size of index I, K k represents the number of times the k-th query appears in the workload, Represents the query Q k The estimated cost when there is no index I, Profit(I,Q k ) represents the query Q k An estimate of the query cost after index I is created.
[0024] Furthermore, the calculation Among them, T base For query Q k Benchmark execution time without index I; P impro (I,Q k ) is the index I for query Q k Performance improvement factor; γ×U updates (I) is the index update cost, γ is the average cost of each update, and U updates (I) is the total number of update operations on index I within a certain period of time; δ is the reconstruction cost per unit index size, |I| is the index size, ∈ is the basic storage cost per unit index size; ζ is the storage cost per unit complexity, U com (I) is the complexity measure of the index structure.
[0025] Compared with the prior art, the technical advantages of the present invention are:
[0026] (1) Comprehensive consideration of multiple factors: This invention introduces a reward function calculation method that comprehensively considers multiple key factors such as the optimization effect and size of the index. The new reward function can more comprehensively evaluate the quality of an action (index operation). Compared with a single indicator, this comprehensive consideration of multiple factors is more in line with the complexity of actual database performance and helps to improve the accuracy and applicability of the index recommendation algorithm.
[0027] (2) Consideration of resource consumption: An evaluation of index size is introduced to avoid excessive consumption of system resources such as memory and disk space. Considering resource consumption factors such as index size in the reward function helps avoid performance issues caused by overly large or undersized index configurations, and improves the robustness and practicality of the system. DETAILED DESCRIPTION
[0028] This paper aims to improve the database index recommendation system in terms of retrieval accuracy and indexing effect, and proposes a database intelligent index recommendation system based on reinforcement learning. The specific algorithm is as follows:
[0029] (1) Randomly initialize the action parameter weights θ and delete all indexes in the database.
[0030] (2) Define the action space of the index recommendation system, that is, the index creation or deletion operations that the system can take. This includes creating indexes on different columns of the database table or deleting existing indexes, and then entering the model training phase.
[0031] (3) Model training:
[0032] ① Extract index candidates from the workload and map them to the initial state S, that is, the state without indexing. ② Use the ε-greedy strategy to keep a balance between exploring unknown actions and experience. Select action A and get the corresponding reward R and next state S after execution. t+1 , after updating θ through the parameter formula, (S t ,R,A,S t+1 ) is stored in the experience pool, and experience recycling is performed to reduce the correlation between data and speed up training. This process is repeated until the cycle is completed. t+1 Update S t ; Repeat the above process until the training is completed and the status is updated.
[0033] (4) Deployment and optimization: Deploy the trained reinforcement learning model to the actual database system, decay the exploration rate ε by 10%, repeat step (2) several times, and output the optimal index configuration.
[0034] The architecture of the database intelligent index recommendation method based on reinforcement learning is as follows Figure 1 As shown:
[0035] In order to provide more accurate and comprehensive feedback on the quality of an index action, this paper proposes a new reward function calculation method: Among them, ΔQphH@size represents the difference of QphH@size before and after using index I. The QphH@size indicator represents the query processing capability under a specific database size. Indicates the optimization effect of index I, I size(I) Indicates the size of index I, K k represents the number of times the k-th query appears in the workload, Represents the query Q k The estimated cost when there is no index I, Profit(I,Q k ) represents the query Q k An estimate of the query cost after index I is created.
[0036] More specifically, in order to more accurately estimate the cost and decision optimization quality, the present invention decomposes the index maintenance cost into update cost and reconstruction cost, and considers the impact of different types of operations on the index in detail, which can more accurately predict the actual cost, and comprehensively considers the query execution time, maintenance cost and storage cost to comprehensively evaluate the overall benefit of the index. Therefore, Profit(I,Q k ) is calculated as:
[0037]
[0038] Among them, Tbase Represents the query Q k Benchmark execution time without index I; P impro (I,Q k ) indicates that index I is relevant to query Q k Performance improvement factor; γ×U updates (I) represents the index update cost, where γ represents the average cost of each update, and U updates (I) represents the total number of update operations on index I within a certain period of time; δ represents the reconstruction cost per unit index size, |I| represents the index size, ∈ represents the basic storage cost per unit index size; ζ represents the storage cost per unit complexity, U com (I) represents the complexity measure of the index structure.
[0039] QphH@size is calculated by combining two performance evaluation indicators: power@size and throughput@size. power@size is the query response time, and throughput@size is the number of queries that the database system can process per hour. in SF represents the size of the database, that is, the amount of data; It represents the total time required to execute 22 query statements of different complexity in the database; Indicates the time required to execute two refresh functions. The throughput@size indicator indicates the system's ability to process the most queries in the least amount of time, fully reflecting the parallel computing speed of the CPU and I / O. throughput@size = [(S×22) / T s ]×3600×SF, where S represents the query flow, the default value is 1; and T s It represents the time taken to run the query stream S. Specific embodiment:
[0041] (1) Input: A set of workloads containing query languages of varying complexity, such as:
[0042] {SELECT first_name,last_name,salary FROM employees;
[0043] SELECT product_name,price FROM products WHERE price>100;
[0044] …
[0045] SELECT product_name,quantity FROM inventory WHERE quantity<10;}
[0046] (2) The initial state S indicates that the employees, products, and inventory tables have not been indexed to complete the initialization.
[0047] The parameter values are assumed to be as follows:
[0048] ΔQphH@size=0.1,
[0049] I size(I) =5GB,
[0050] K k ={K 1 =30,K 2 =40,K 3 =30},
[0051]
[0052] Profit(I,Q 1 )=5,Profit(I,Q 2 )=7,Profit(I,Q 3 )=4,
[0053] (3) Model training:
[0054] ① Extract index candidates from the workload. Assume that the index candidates for the products table are:
[0055] CREATE INDEX idx_products_price ON products(price);
[0056] ②ε-greedy strategy selection action: randomly select an index candidate A with probability ε, or select the currently considered optimal index candidate with probability (1-ε). Executing the action means adding an index: CREATE INDEX idx_products_price ON products(price); calculate the reward based on the assumed parameters And get the next state S after state transfer t+1 , after updating θ through the parameter formula, (S t ,R,A,S t+1 ) into the experience pool. Repeat this process until the cycle is complete.
[0057] ③Use S t+1 Update S t; Repeat the above process until the training is completed and the status is updated.
[0058] (4) Output the best index configuration
[0059] ①For the employees table:
[0060] Create a single-column index: INDEX ON employees(first_name)
[0061] Create a single-column index: INDEX ON employees(last_name)
[0062] Create a single-column index: INDEX ON employees(salary)
[0063] ②For the products table:
[0064] Create a single-column index: INDEX ON products(price)
[0065] ③For the inventory table:
[0066] Create a single-column index: INDEX ON inventory(quantity)
[0067] In the above specific embodiments, the reward function calculation method based on comprehensive consideration of multiple factors proposed in the present invention comprehensively evaluates the optimization effect, index size, and resource consumption of the index. For example, when creating an index on the price column of the products table, the comprehensive score calculated by the reward function is 1.18, which reflects the reasonable control of resource consumption while improving query performance. In addition, through a detailed evaluation of the index configuration of the employees table, it is finally decided to retain the index of the salary column with the most significant performance improvement and the lowest resource consumption, thus avoiding unnecessary waste of resources. In the actual deployment process, through the continuous optimization of the reinforcement learning model, the database query response time and disk I / O consumption have been reduced to a certain extent compared with the prior art.
[0068] 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 separately by functions divided into various modules. 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.
[0069] Those skilled in the art also know that, in addition to implementing the controller in a purely computer-readable program code, the controller can be made to implement the same function in the form of logic gates, switches, application-specific integrated circuits, programmable logic controllers, and embedded microcontrollers by logically programming the method steps. 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.
[0070] 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.
[0071] 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.
[0072] 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.
[0073] 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 database intelligent index recommendation method based on reinforcement learning, Features: The specific steps of the method include: (1) Randomly initialize the weight θ of the action parameter and delete all indexes in the database. (2) Define the action space of the index recommendation system. (3) Select the appropriate index operation to minimize the reward function value. (4) Collect data using simulated or real database query scenarios and construct a training set; build a reinforcement learning model; after the construction is completed, use the collected data to train the model and optimize the model parameters through the reward function to change the index action. (5) Deploy the trained model to the actual database system and continuously optimize the model to adapt to changes in database performance and new query patterns.
2. A database intelligent index recommendation method based on reinforcement learning as claimed in claim 1, Features: In step (3), the reward function is calculated Among them, ΔQphH@size represents the difference of QphH@size before and after using index I, and QphH@size represents the query processing capacity under a specific database size. I utility(I) represents the optimization effect of index I, N is the number of different query types in the workload, and I size(I) Indicates the size of index I, K k represents the number of times the k-th query appears in the workload, Represents the query Q k The estimated cost when there is no index I, Profit(I,Q k ) represents the query Q k An estimate of the query cost after index I is created.
3. A database intelligent index recommendation method based on reinforcement learning as claimed in claim 2, Features: calculate Among them, T base For query Q k Benchmark execution time without index I; P impro (I,Q k ) is the index I for query Q k Performance improvement factor; γ×U updates (I) is the index update cost, γ is the average cost of each update, and U updates (I) is the total number of update operations on index I within a certain period of time; δ is the reconstruction cost per unit index size, |I| is the index size, ∈ is the basic storage cost per unit index size; ζ is the storage cost per unit complexity, U com (I) is the complexity measure of the index structure.
4. A database intelligent index recommendation system based on reinforcement learning, Features: The system includes: (1) Initialization module: This module randomly initializes the weights θ of the action parameters and deletes all indexes in the database. (2) Action space definition module, which defines the action space of the index recommendation system, that is, the index creation or deletion operations that the system can take. (3) A reward function optimization module, which selects the appropriate index operation to minimize the reward function value. (4) A model training module, which uses simulated or real database query scenarios to collect data and build a training set; builds a reinforcement learning model; and after the construction is completed, uses the collected data to train the model and optimizes the model parameters through the reward function to change the index action. (5) Deployment and optimization module, which deploys the trained model to the actual database system and continuously optimizes the model to adapt to database performance changes and new query patterns.
5. A database intelligent index recommendation system based on reinforcement learning as claimed in claim 4, Features: In the reward function optimization module, the reward function is calculated Among them, ΔQphH@size represents the difference of QphH@size before and after using index I, and QphH@size represents the query processing capacity under a specific database size. I utility(I) represents the optimization effect of index I, N is the number of different query types in the workload, and I size(I) Indicates the size of index I, K k represents the number of times the k-th query appears in the workload, Represents the query Q k The estimated cost when there is no index I, Profit(I,Q k ) represents the query Q k An estimate of the query cost after index I is created.
6. A database intelligent index recommendation system based on reinforcement learning as claimed in claim 5, Features: calculate Among them, T base For query Q k Benchmark execution time without index I; P impro (I,Q k ) is the index I for query Q k Performance improvement factor; γ×U updates (I) is the index update cost, γ is the average cost of each update, and U updates (I) is the total number of update operations on index I within a certain period of time; δ is the reconstruction cost per unit index size, |I| is the index size, ∈ is the basic storage cost per unit index size; ζ is the storage cost per unit complexity, U com (I) is the complexity measure of the index structure.
Citation Information
Patent Citations
Index selection method based on deep reinforcement learning
CN114579579A
Multi-attribute index selection based on deep reinforcement learning
CN115017130A
Learning type index recommendation method combining space occupation and scanning line number
CN116303450A
Database structure and index optimization method, equipment and medium
CN117785834A
Reinforcement learning techniques to improve searching and / or to conserve computational and network resources
US20190179938A1
