A database intelligent index recommendation method and system based on reinforcement learning

Abstract

The application provides a database intelligent index recommendation method and system based on reinforcement learning, which comprises the following modules: (1) an initialization module; (2) an action space definition module; (3) a reward function optimization module, which selects appropriate index operations to minimize the reward function value; (4) a model training module; and (5) a deployment and optimization module. The application introduces a reward function calculation method that comprehensively considers multiple key factors such as the optimization effect and size of the index, and the reward function can more comprehensively evaluate the good and bad of an action (index operation). Compared with a single index, this multi-factor comprehensive consideration method is more in line with the complexity of the actual database performance, and is helpful to improve the accuracy and applicability of the index recommendation algorithm. The introduction of the evaluation of the index size avoids excessive consumption of system resources such as memory and disk space. Considering the resource consumption factors such as the index size in the reward function helps to avoid performance problems caused by excessively large or small index configurations, and improves the robustness and practicality of the system.

Description

Technical Field

[0001] This invention relates to a database intelligent index recommendation method and system based on reinforcement learning, belonging to the field of database intelligent index recommendation. Background Technology

[0002] With the rapid development of the information age, database index recommendation has become increasingly crucial in improving database performance, reducing query response time, and meeting user needs. Current research primarily focuses on reinforcement learning-based database index recommendation algorithms to optimize database index configuration. However, the performance evaluation of these algorithms often relies on QphH@size, a key performance indicator in the TPC-H database metrics. This indicator mainly focuses on query execution time efficiency but neglects crucial aspects such as query execution plan quality and index size, thus limiting a comprehensive assessment of index optimization effectiveness.

[0003] QphH@size, a key performance indicator in TPC-H database metrics, primarily measures the query throughput of a database under a specific load, making it an important standard for evaluating database performance. However, this indicator mainly focuses on the efficiency of query execution time and cannot comprehensively assess the quality of the query execution plan. The following are some limitations of this indicator:

[0004] (1) Limited to query execution time: QphH@size primarily focuses on query execution time, neglecting other factors related to query performance, such as resource utilization and memory consumption. The optimization effect of an index is not only reflected in the reduction of execution time, but may also have a significant impact on the overall performance of the database.

[0005] (2) Ignoring query execution plan quality: This metric does not provide detailed information about the quality of query execution plans. The introduction of an index affects the choice of query execution plan, thus impacting overall performance. QphH@size does not provide an intuitive understanding of the quality of query execution plans, thus limiting in-depth analysis of index configuration.

[0006] (3) Factors such as index size are not considered: This metric does not take into account factors such as index size, which is one of the important factors affecting performance. An index that is too large or too small may lead to performance degradation, but these are not included in the QphH@size evaluation system.

[0007] In index recommendation systems, the quality of the reward function directly impacts the final performance of reinforcement learning algorithms. Therefore, choosing a reward function that effectively evaluates the quality of indexing actions is crucial for the index selection problem. When evaluating the quality of indexing actions, factors such as the optimization effect of the index and the index size must be considered. Attached Figure Description

[0008] Figure 1 This is a diagram illustrating the architecture of a database intelligent indexing and recommendation method based on reinforcement learning. Summary of the Invention

[0009] To address the aforementioned problems, this invention proposes a database intelligent index recommendation method based on reinforcement learning, the specific steps of which include:

[0010] (1) Randomly initialize the weight θ of the action parameters and delete all indexes in the database.

[0011] (2) Define the action space of the index recommendation system.

[0012] (3) Select appropriate index operations to minimize the reward function value.

[0013] (4) Collect data using simulated or real database query scenarios and build a training set; build a reinforcement learning model; after the model is built, use the collected data to train the model and optimize the model parameters and change the indexing action through the reward function.

[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] Further, in step (3), the reward function is calculated. Where ΔQphH@size represents the difference in QphH@size before and after using index I, and QphH@size represents the query processing capacity for a specific database size. I utility(I) This represents the optimization effect of index I, where N is the number of different query types in the workload, and I... size(I) K represents the size of index I. k This indicates the number of times the k-th query occurs in the workload. Indicates query Q k The cost estimate required without index I, Profit(I,Q) k ) indicates query Q k The estimated cost of querying after index I is created.

[0016] Further calculation Among them, T base To query Q k Baseline execution time without index I; P impro (I,Q k ) is index I for query Q k Performance enhancement factor; γ×Uupdates (I) represents the index update cost, γ represents the average cost per update, and U updates (I) represents the total number of update operations performed on index I within a certain period; δ represents the rebuild cost per unit of index size, |I| represents the index size, ∈ represents the basic storage cost per unit of index size; ζ represents the storage cost per unit of complexity, U com (I) is a measure of the complexity of the index structure.

[0017] Based on the above methods, this invention proposes a database intelligent indexing and 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: This module defines the action space of the index recommendation system, that is, the index creation or deletion operations that the system can perform.

[0020] (3) Reward function optimization module: This module selects appropriate index operations to minimize the reward function value.

[0021] (4) Model training module: This module collects data using simulated or real database query scenarios, builds a training set, constructs a reinforcement learning model, and trains the model using the collected data after construction, and optimizes the model parameters and changes the indexing action through the reward function.

[0022] (5) Deployment and optimization module: This module deploys the trained model to the actual database system and continuously optimizes the model to adapt to changes in database performance and new query patterns.

[0023] Furthermore, in the reward function optimization module, the reward function is calculated. Where ΔQphH@size represents the difference in QphH@size before and after using index I, and QphH@size represents the query processing capacity for a specific database size. I utility(I) This represents the optimization effect of index I, where N is the number of different query types in the workload, and I... size(I) K represents the size of index I. k This indicates the number of times the k-th query occurs in the workload. Indicates query Q k The cost estimate required without index I, Profit(I,Q) k ) indicates query Q k The estimated cost of querying after index I is created.

[0024] Further calculation Among them, T base To query Q k Baseline execution time without index I; P impro (I,Q k ) is index I for query Q k Performance enhancement factor; γ×U updates (I) represents the index update cost, γ represents the average cost per update, and U updates (I) represents the total number of update operations performed on index I within a certain period; δ represents the rebuild cost per unit of index size, |I| represents the index size, ∈ represents the basic storage cost per unit of index size; ζ represents the storage cost per unit of complexity, U com (I) is a measure of the complexity of the index structure.

[0025] Compared with the prior art, the technical advantages of the present invention are as follows:

[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 multi-factor comprehensive consideration approach is more in line with the complexity of actual database performance and helps to improve the accuracy and applicability of index recommendation algorithms.

[0027] (2) Resource consumption considerations: 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 to avoid performance problems caused by index configurations that are too large or too small, thus improving the robustness and usability of the system. Detailed Implementation

[0028] This invention improves the retrieval accuracy and indexing performance of database index recommendation systems by proposing a database intelligent index recommendation system based on reinforcement learning. The specific algorithm is as follows:

[0029] (1) Randomly initialize the weight θ of the action parameters 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 proceeding to the model training phase.

[0031] (3) Model training:

[0032] ① Extract index candidates from the workload and map them to the initial state S, i.e., the state where no index has been built. ② Employ an ε-greedy strategy, where the index candidate agent maintains a balance between exploring unknown actions and gaining experience. Select action A, execute it, and receive the corresponding reward R and the next state S. t+1 After updating θ using the parameter formula, (S) t ,R,A,S t+1 ) Store the data in an experience pool for experience retrieval to reduce correlations between data and speed up training. Repeat this process until the loop is complete. ③ Utilize S t+1 Update S t Repeat the above process until training is complete and the state is updated.

[0033] (4) Deployment and optimization: Deploy the trained reinforcement learning model to the actual database system, decay the exploration rate ε at a frequency of 10%, repeat step (2) several times, and output the optimal index configuration.

[0034] The architecture of a database intelligent indexing and recommendation method based on reinforcement learning is as follows: Figure 1 As shown:

[0035] To provide more accurate and comprehensive feedback on the quality of an indexing action, this invention proposes a novel reward function calculation method: Here, ΔQphH@size represents the difference in QphH@size before and after using index I. The QphH@size metric represents the query processing capacity for a specific database size. This indicates the optimization effect of index I, I size(I) K represents the size of index I. k This indicates the number of times the k-th query occurs in the workload. Indicates query Q k The cost estimate required without index I, Profit(I,Q) k ) indicates query Q k The estimated cost of querying after index I is created.

[0036] More specifically, to more accurately estimate costs and optimize decision quality, this invention decomposes index maintenance overhead into update costs and reconstruction costs, and meticulously considers the impact of different types of operations on the index. This allows for more accurate prediction of actual costs. Furthermore, it comprehensively considers query execution time, maintenance costs, and storage costs to fully evaluate the overall effectiveness of the index. Therefore, it proposes Profit(I,Q)... k The formula for calculating ) is:

[0037]

[0038] Among them, Tbase Indicates query Q k Baseline execution time without index I; P impro (I,Q k ) indicates that index I is used for query Q k Performance enhancement factor; γ×U updates (I) represents the index update cost, where γ represents the average cost per update, U updates (I) represents the total number of update operations performed on index I within a certain period of time; δ represents the rebuild cost per unit of index size, |I| represents the index size, ∈ represents the basic storage cost per unit of index size; ζ represents the storage cost per unit of complexity, U com (I) represents the complexity measure of the index structure.

[0039] QphH@size is calculated jointly using two performance metrics: power@size and throughput@size. Power@size represents the query response time, while throughput@size represents the number of queries the database system can process per hour. in SF represents the database size, i.e., the amount of data. This represents the total time required to execute 22 queries of varying complexity in the database; while This represents the time required to execute two refresh functions. The throughput@size metric indicates the system's ability to process the most queries in the shortest amount of time, fully reflecting the parallel processing speed of the CPU and I / O. throughput@size = [(S×22) / T] s ]×3600×SF, where S represents the query stream, defaulting to 1; and T s This indicates the time spent running the query stream S. Specific implementation examples:

[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 and the initialization is complete.

[0047] The parameter values ​​are assumed to be as follows:

[0048] ΔQphH@size=0.1,

[0049] I size(I) =5GB,

[0050] K k ={K1=30,K2=40,K3=30},

[0051]

[0052] Profit(I,Q1)=5,Profit(I,Q2)=7,Profit(I,Q3)=4,

[0053] (3) Model training:

[0054] ① Extract index candidates from the workload. Assume the index candidates for the table products are:

[0055] CREATE INDEX idx_products_price ON products(price);

[0056] ② The ε-greedy strategy selects an index candidate A with probability ε, or selects the currently considered optimal index candidate with probability (1-ε). The action is to add the index: `CREATE INDEX idx_products_price ON products(price);` The reward is calculated based on the assumed parameters. And obtain the next state S after the state transition. t+1 After updating θ using the parameter formula, (S) t ,R,A,S t+1 Store it in the experience pool. Repeat this process until the loop is complete.

[0057] ③Utilizing S t+1 Update S t Repeat the above process until training is complete and the state is updated.

[0058] (4) Output the optimal 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 specific embodiments described above, the reward function calculation method proposed in this invention, based on a comprehensive consideration of multiple factors, 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, demonstrating that resource consumption is reasonably controlled while improving query performance. Furthermore, through a detailed evaluation of the index configuration of the employees table, the index on the salary column, which offers the most significant performance improvement and consumes the least resources, was ultimately selected and retained, avoiding unnecessary resource waste. In actual deployment, through continuous optimization of the reinforcement learning model, both database query response time and disk I / O consumption have decreased to some extent compared to existing technologies.

[0068] The units, devices, or modules described in the above embodiments can be implemented by computer chips or physical entities, or by products with certain functions. For ease of description, the above devices are described by dividing them into various modules according to their functions. Of course, in implementing this application, the functions of each module can be implemented in one or more software and / or hardware, or the module that implements the same function can be implemented by a combination of multiple sub-modules or sub-units, etc. The device embodiments described above are merely illustrative. For example, the division of units is only a logical functional division, and there may be other division methods in actual implementation. For example, multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. Furthermore, the coupling or direct coupling or communication connection between the devices or units shown or discussed can be through some interfaces, and the indirect coupling or communication connection between the devices or units can be electrical, mechanical, or other forms.

[0069] Those skilled in the art will also know that, besides implementing the controller using purely computer-readable program code, the same functions can be achieved by logically programming the method steps, making the controller function as logic gates, switches, application-specific integrated circuits (ASICs), programmable logic controllers (PLCs), and embedded microcontrollers. Therefore, such a controller can be considered a hardware component, and the devices within it used to implement various functions can also be considered structures within that hardware component. Alternatively, the devices used to implement various functions can be considered as both software modules implementing the method and structures within a hardware component.

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

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

[0072] The various embodiments in this specification are described in a progressive manner. Similar or identical parts between embodiments can be referred to interchangeably. Each embodiment focuses on its differences from other embodiments. This application can be used in numerous general-purpose or special-purpose computer system environments or configurations. Examples include: personal computers, server computers, handheld or portable devices, tablet devices, multiprocessor systems, microprocessor-based systems, set-top boxes, programmable electronic devices, network PCs, minicomputers, mainframe computers, and distributed computing environments including any of the above systems or devices, etc.

[0073] The specific embodiments described above further illustrate the purpose, technical solution, and beneficial effects of this application. It should be understood that the above descriptions are merely specific embodiments of this application and are not intended to limit the scope of protection of this application. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this application should be included within the scope of protection of this application.

Claims

1. A database intelligent indexing and recommendation method based on reinforcement learning, characterized in that: The specific steps of this method include: Randomly initialize the weights of the action parameters θ Delete all indexes in the database; Define the action space of the index recommendation system; The index selection operation minimizes the reward function value; Collect data using simulated or real database query scenarios to build a training set; build a reinforcement learning model; after completion, train the model using the collected data, and optimize the model parameters and change the indexing action through the reward function; 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; When minimizing the reward function value during the index selection operation, the reward function is calculated. ,in, Indicates the use of an index I Before and after QphH@size Differences QphH@size This indicates the query processing capacity for a specific database size. , Indicates index I The optimization effect N The number of different query types in the workload. Indicates index I Size, K k Indicates the first k The number of times this type of query occurs in the workload. Indicates query Q k Without an index I The estimated cost required at that time Indicates query Q k In the index I The estimated cost of querying after the system is established.

2. The database intelligent indexing and recommendation method based on reinforcement learning as described in claim 1, characterized in that: calculate ,in, For query Q k Without an index I The baseline execution time at that time; For index I For query Q k Performance enhancement factors; For index update costs, The average cost per update. For a certain period of time, the index I Total number of update operations performed; The rebuild cost per unit index size, For index size, Basic storage overhead per unit index size; Storage overhead per unit of complexity, This is a measure of the complexity of the index structure.

3. A database intelligent indexing and recommendation system based on reinforcement learning, characterized in that: The system includes: The initialization module randomly initializes the weights of the action parameters. θ Delete all indexes in the database; The action space definition module defines the action space of the index recommendation system, that is, the index creation or deletion operations that the system can perform; The reward function optimization module selects an index operation to minimize the reward function value. The model training module collects data using simulated or real database query scenarios to build a training set; it builds a reinforcement learning model; after the model is built, it trains the model using the collected data and optimizes the model parameters and changes the indexing action through the reward function. The deployment and optimization module deploys the trained model to the actual database system and continuously optimizes the model to adapt to changes in database performance and new query patterns. In the reward function optimization module, the reward function is calculated. ,in, Indicates the use of an index I Before and after QphH@size differences QphH@size This indicates the query processing capacity for a specific database size. , Indicates index I The optimization effect N The number of different query types in the workload. Indicates index I Size, K k Indicates the first k The number of times this type of query occurs in the workload. Indicates query Q k Without an index I The estimated cost required at that time, Indicates query Q k In the index I The estimated cost of querying after the system is established.

4. The database intelligent indexing and recommendation system based on reinforcement learning as described in claim 3, characterized in that: calculate ,in, For query Q k Without an index I The baseline execution time at that time; For index I For query Q k Performance enhancement factors; For index update costs, The average cost per update. For a certain period of time, the index I Total number of update operations performed; The rebuild cost per unit index size, For index size, Basic storage overhead per unit index size; Storage overhead per unit of complexity, This is a measure of the complexity of the index structure.

Images