Index selection method and device, equipment, storage medium and program product
Through the reinforcement learning algorithm, the problem of inefficiency in the index configuration of the index configuration when the workload changes is solved, efficient and dynamic index optimization is achieved, and database query performance is improved.
Patent Information
- Application Number
- CN202411312190.0
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-09-19
- Publication Date
- 2024-11-29
AI Technical Summary
Existing database index configurations are difficult to automatically optimize when workloads and query patterns change, relying on the professionalism and experience of the database administrator, resulting in inefficiency and difficulty in selecting the optimal index configuration.
The index selection method based on reinforcement learning algorithm is adopted, and the actions that meet the expected actions are searched from the action space through multiple iterations, and the database index configuration is automatically optimized, and the index configuration is evaluated and updated using the action value function to improve the efficiency and quality of index optimization.
Automatic database index optimization is realized, index optimization efficiency and quality is improved, database query performance is improved, and workloads are adapted to dynamically changing.
Smart Images

Figure CN119046288A_ABST
Abstract
Description
Technical Field
[0001] The present application relates to the technical field of database indexing, and in particular to an index selection method, apparatus, device, storage medium and program product. Background Art
[0002] The database index can quickly locate the location of data storage and improve data query efficiency. As workloads and query patterns continue to change, the original database index may no longer be efficient, and it is necessary to adjust the index configuration in a timely manner to improve database performance.
[0003] In the related art, a database administrator (DBA) usually continuously monitors changes in database workload and query patterns, adjusts index strategies based on these changes, and evaluates the costs and benefits of the adjusted strategies to decide whether to perform index optimization. In the strategy of index optimization performed by the DBA, the quality of index optimization is heavily dependent on the DBA's professionalism and experience, and the efficiency of index optimization is low. At the same time, when there are many selectable index configurations, it is difficult to select the optimal index configuration manually.
[0004] Therefore, there is an urgent need to provide an efficient and highly accurate index optimization solution. Summary of the invention
[0005] The present application provides an index selection method, apparatus, device, storage medium and program product, which searches for expected actions from the action space through multiple rounds of iterations, and uses the index configuration corresponding to the action as the database index, thereby realizing automatic index optimization and improving the efficiency of index optimization. The index configuration transferred after the action is evaluated through the action value function, and the index configuration is gradually optimized, thereby improving the quality of index optimization.
[0006] In a first aspect, the present application provides an index selection method, comprising:
[0007] Get the query workload of the database;
[0008] Repeat the following steps until the change in the action value obtained by multiple consecutive iterations is less than a preset threshold, or the number of iterations reaches a preset number, and determine that the next index configuration corresponding to the maximum action value is the index configuration of the database:
[0009] Based on the obtained action value, selecting a next action of the current index configuration from the constructed action space, wherein the action space includes a plurality of actions for adjusting the index configuration;
[0010] Based on the action value function, calculating an action value of performing the next action under the query workload to transfer the current index configuration to the next index configuration;
[0011] Update the current index configuration to the next index configuration.
[0012] Optionally, based on the action value function, calculating the action value of performing the next action under the query workload and transferring the current index configuration to the next index configuration includes:
[0013] Based on the reward function, calculating a reward value for performing the next action under the query workload and transferring the current index configuration to the next index configuration;
[0014] The action value of this iteration is determined based on the action value function, the reward value, the action value of the previous iteration, and the maximum action value corresponding to the next index configuration.
[0015] Optionally, the reward function is a weighted average of a query improvement reward item and a cost saving reward item;
[0016] The query improvement reward item is the difference between the query cost of the current index configuration and the query cost of the next index configuration; the query cost of the index configuration is the sum of the query costs of the query items in the query workload;
[0017] The cost saving reward item is the difference between the sum of the creation cost and the maintenance cost of the current index configuration and the sum of the creation cost and the maintenance cost of the next index configuration.
[0018] Optionally, after calculating, based on the action value function, the action value for performing the next action under the query workload and transferring the current index configuration to the next index configuration, the method further includes:
[0019] Based on the action value, an action value lookup table is updated, wherein the action value lookup is used to record the action value of each index configuration under each action.
[0020] Optionally, based on the obtained action value, select the next action of the current index configuration from the constructed action space, including:
[0021] From the action value lookup table, find the maximum action value corresponding to the current index configuration, and use the action corresponding to the maximum action value as the target action;
[0022] Based on the ε-greedy algorithm, an action is randomly selected from the constructed action space or the target action is selected as the next action configured for the current index.
[0023] Optionally, the probability ε in the ε-greedy algorithm is related to the number of iterations, or is related to the number of iterations and the action value corresponding to the number of iterations.
[0024] In a second aspect, the present application provides an index selection device, comprising:
[0025] A load acquisition module is used to obtain the query workload of the database;
[0026] An iteration module is used to repeatedly execute the following steps until the change of the action value obtained by multiple consecutive iterations is less than a preset threshold, or the number of iterations reaches a preset number, and determine that the next index configuration corresponding to the maximum action value is the index configuration of the database:
[0027] Based on the obtained action value, selecting a next action of the current index configuration from the constructed action space, wherein the action space includes a plurality of actions for adjusting the index configuration;
[0028] Based on the action value function, calculating an action value of performing the next action under the query workload to transfer the current index configuration to the next index configuration;
[0029] Update the current index configuration to the next index configuration.
[0030] In a third aspect, the present application provides an electronic device, comprising: a processor, and a memory communicatively connected to the processor;
[0031] The memory stores computer-executable instructions;
[0032] The processor executes the computer-executable instructions stored in the memory to implement the method provided in the first aspect of the present application.
[0033] In a fourth aspect, the present application provides a computer-readable storage medium, in which computer-executable instructions are stored. When the computer-executable instructions are executed by a processor, they are used to implement the method provided in the first aspect of the present application.
[0034] In a fifth aspect, the present application provides a computer program product, including a computer program, which implements the method provided in the first aspect of the present application when executed by a processor.
[0035] The index selection method, apparatus, device, storage medium and program product provided by the present application realize the adjustment of the index configuration of the database by constructing an action space, and obtain a variety of possible index configurations. Through an iterative process, at each iteration, an action value is obtained based on historical iterations, and the action of this iteration is obtained from the action space, and then the index configuration is updated under the action of this iteration, and the action value of the new index configuration is calculated. Whether to end the iterative process is determined by the change of the action value and the number of iterations. At the end of the iteration, the index configuration of the database is updated to the index configuration with the largest action value. Automatic optimization of the database index configuration based on the reinforcement learning algorithm is realized, and efficiency is improved. The reinforcement learning algorithm is used to continuously search for a better index configuration in the space, thereby improving the quality of index configuration optimization and thus improving the query performance of the database. BRIEF DESCRIPTION OF THE DRAWINGS
[0036] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present application and, together with the description, serve to explain the principles of the present application.
[0037] Figure 1 A schematic diagram of an index optimization process;
[0038] Figure 2 A flowchart of an index selection method provided in an embodiment of the present application;
[0039] Figure 3 A flowchart of another index selection method provided in an embodiment of the present application;
[0040] Figure 4 A flowchart of another index selection method provided in an embodiment of the present application;
[0041] Figure 5 A schematic diagram of the structure of an index selection device provided in an embodiment of the present application;
[0042] Figure 6 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application.
[0043] The above drawings have shown clear embodiments of the present application, which will be described in more detail later. These drawings and text descriptions are not intended to limit the scope of the present application in any way, but to illustrate the concept of the present application to those skilled in the art by referring to specific embodiments. DETAILED DESCRIPTION
[0044] Exemplary embodiments will be described in detail herein, examples of which are shown in the accompanying drawings. When the following description refers to the drawings, the same numbers in different drawings represent the same or similar elements unless otherwise indicated. The implementations described in the following exemplary embodiments do not represent all implementations consistent with the present application. Instead, they are merely examples of devices and methods consistent with some aspects of the present application as detailed in the appended claims.
[0045] First, some of the terms involved in this application are explained:
[0046] Index configuration: used to describe the composition of the index created by the database, which can be understood as an index collection.
[0047] Query workload: can include one or more query items. A query item can be a query field, a query condition, a query parameter, etc.
[0048] Action space: An action space constructed by considering possible index operations of the database, such as adding, deleting or modifying indexes. The action space includes multiple actions, such as one or more index operations, through which the index configuration of the data can be adjusted and the state transfer of the database index configuration can be realized.
[0049] Database indexes are a key factor in improving query performance. As database query workloads, query patterns, or database storage data change, existing index configurations may no longer be optimal. For example, the created index may occupy too many resources or may cause database query performance to degrade. Therefore, it is necessary to continuously optimize database indexes.
[0050] Figure 1 A schematic diagram of an index optimization process is shown in Figure 1. Figure 1 As shown in the figure, during the operation of the database, the database administrator DBA monitors the changes in the database query mode and stored data, and based on these changes, evaluates whether the current index configuration of the database is appropriate or needs to be adjusted. When the index configuration needs to be adjusted, the DBA combines his own professional knowledge and experience, comprehensively considers multi-dimensional factors such as cost and query efficiency, and determines the new index configuration.
[0051] Relying on the DBA's index optimization strategy is inefficient, and the optimization quality is limited by the DBA's expertise, making it difficult to find the optimal index configuration.
[0052] In the index selection problem (ISP), the core issue is how to find the index configuration that optimizes query efficiency while keeping storage and maintenance costs under control.
[0053] In order to improve the drawbacks of index optimization by DBAs, some rule-based or heuristic index optimization strategies are provided. These strategies do not have autonomous learning capabilities and are usually designed for specific query workloads. They cannot adapt to dynamically changing query workloads and have a narrow scope of application.
[0054] In order to improve the efficiency, dynamics and quality of index selection, the present application provides an index selection method, which implements an index selection strategy based on a reinforcement learning algorithm, and autonomously decides on the action of the next iteration through interactive learning with the environment. The specific scheme is to consider possible index operations under the current index configuration and query workload of the data, and construct an action space. During the iteration process, the action of this iteration is determined from the action space based on the action value obtained from the historical iteration, and the index configuration is updated based on the action. The action value brought by the index configuration update under the action is calculated, that is, the long-term reward value. The search for the optimal index configuration is realized with the long-term reward value as the evaluation parameter, which improves the efficiency and quality of index configuration optimization. At the same time, through the dynamic acquisition of query workload, dynamic optimization of index configuration can be realized, and the dynamic nature of optimization can be improved.
[0055] The technical solution of the present application and how the technical solution of the present application solves the above-mentioned technical problems are described in detail below with specific embodiments. The following specific embodiments can be combined with each other, and the same or similar concepts or processes may not be repeated in some embodiments. The embodiments of the present application will be described below in conjunction with the accompanying drawings.
[0056] Figure 2 A flowchart of an index selection method provided in an embodiment of the present application is provided. The index selection method can be executed by an electronic device with corresponding data processing capabilities, such as an index selection device of a database, such as Figure 2 As shown, the index selection method includes the following steps:
[0057] Step S201, obtaining the query workload of the database.
[0058] The query workload may be a query workload with the highest query frequency or the most commonly used query mode in a recent period of time for the database, or may be a specified one from query workloads recorded in a recent period of time for the database, such as the most complex query workload.
[0059] The query workload of the database can be obtained periodically to periodically select the database index. The query workload obtained at a time node corresponding to a cycle can be the query workload under a query mode with the highest query frequency recorded in the cycle. The DBA can also actively initiate an index selection request to obtain the query workload of the database once, and complete the database index selection once through a subsequent iterative process.
[0060] The query workload obtained may include multiple query items. The query workload W including m query items can be expressed as: W = {Qry 1 ,Qry 2 ,…,Qry m}, where Qry i (i is a positive integer from 1 to m) represents the i-th query item, and m is a positive integer greater than 2.
[0061] The database may be a large database, and the storage capacity of a large database is usually at the TB (Terabyte) level or even the PB (Petabyte) level. At the same time, the data structure stored in a large database is also relatively complex, and the data types are diversified or include multiple data models, and the data types may include structured data, semi-structured data and unstructured data. Data models include but are not limited to hierarchical models, network models and relational models. The index of a large database includes multi-level indexes and multiple index types, such as spatial indexes, time indexes, text indexes, etc. The index of a large database usually reaches a level that cannot be accommodated by the TB-level physical memory of a supercomputer or a computer cluster.
[0062] In some embodiments, before, after or while obtaining the query workload of the database, the current index configuration of the database may also be obtained as an initial value of the current index configuration during the iteration process.
[0063] The database index selection problem can be modeled as a Markov Decision Process (MDP) model, in which the state is used to represent the index configuration, the action is the action in the action space, which is used to adjust the index configuration, the strategy includes the action selection strategy, and the reward can include short-term reward and long-term reward. The long-term reward is the subsequent action value.
[0064] Before the iteration process, the state, action and reward of the MDP model can be initialized. The state can be initialized to the current index configuration of the database, the action can be initialized to any action, and the reward can be initialized based on prior knowledge or initialized to a smaller random number.
[0065] Step S202, repeatedly executing steps S203 to S205 until the change of the action value obtained by multiple consecutive iterations is less than a preset threshold, or the number of iterations reaches a preset number, and determining the next index configuration corresponding to the maximum action value as the index configuration of the database.
[0066] The preset threshold is a configurable parameter that users can adjust within a set range based on their needs.
[0067] The action value obtained by multiple consecutive iterations may be the action value obtained by N consecutive iterations, where N is a positive integer greater than 1, such as 3, 5, 6, etc.
[0068] The preset number is the upper limit of the number of iterations, such as 100 times, 1000 times, etc. When the number of iterations reaches the preset number, the iteration process ends.
[0069] When the action value obtained after multiple consecutive iterations changes little, that is, when the action value is stable, or when the number of iterations reaches a preset number, the iteration process ends and the selected index configuration is output, that is, the next index configuration corresponding to the maximum action value.
[0070] In each iteration, the following steps are performed:
[0071] Step S203: Based on the obtained action value, select the next action of the current index configuration from the constructed action space.
[0072] The action space includes multiple actions for adjusting index configuration, such as adding an index, deleting an index, modifying an index, etc.
[0073] In each iteration, based on the action values obtained in the historical iterations, an action is selected from the constructed action space as the next action, so as to adjust the current index configuration to the next index configuration through the next action.
[0074] In each iteration, the current index configuration is the index configuration updated in the previous iteration, that is, the next index configuration obtained under the next action selected in the previous iteration.
[0075] The action value may correspond to a set of actions and index configurations, and is used to describe the action value calculated by performing the action on the basis of the index configuration to adjust the index configuration. The next index configuration corresponding to the action value is the index configuration obtained after adjusting the index configuration corresponding to the action value corresponding to the action value.
[0076] The next action of the current index configuration can be selected from the constructed action space based on the action value corresponding to the current index configuration.
[0077] Specifically, when the maximum action value corresponding to the current index configuration, i.e., the maximum value of the action value, is greater than the preset value, the action corresponding to the maximum action value corresponding to the current index configuration can be selected as the next action. If the maximum action value corresponding to the current index configuration is less than or equal to the preset value, an action can be randomly selected from the action space as the next action.
[0078] In some embodiments, the next action of the current index configuration may be selected from the constructed action space based on the maximum value of the action value corresponding to the current index configuration and the maximum value of the obtained action values, and the actions corresponding to the two maximum values.
[0079] When the actions corresponding to the two maximum values are different, if the maximum value Max1 of the obtained action value is significantly higher than the maximum value Max2 of the action value corresponding to the current index configuration, such as the difference between Max1 and Max2 is greater than the first value, the difference between the ratio of Max1 and Max2 minus 1 is greater than the second value, etc., then the action corresponding to Max1 is the next action; conversely, if Max1 is not significantly higher than Max2, that is, Max1 and Max2 are approximately equal, then the action corresponding to Max2 is the next action.
[0080] Furthermore, when Max1 is significantly higher than Max2, one can be selected as the next action from the action corresponding to Max1 and the action randomly selected from the action space based on the ε-greedy algorithm; similarly, when Max1 is not significantly higher than Max2, one can be selected as the next action from the action corresponding to Max2 and the action randomly selected from the action space based on the ε-greedy algorithm.
[0081] Step S204: Based on the action value function, the action value of performing the next action under the query workload and transferring the current index configuration to the next index configuration is calculated.
[0082] The action value is used to represent the long-term reward value brought by performing the corresponding action (such as the next action) to adjust the database index configuration under the query workload. The long-term reward value can be a weighted average of the immediate reward and the long-term expected reward. The immediate reward can be measured by the cost savings and query efficiency improvement directly brought by transferring the current index configuration to the next index configuration, and the long-term expected reward can be measured by the maximum expected reward value of the next index configuration.
[0083] After determining the next action in this iteration, the next action is executed under the current index configuration to adjust the index configuration of the database from the current index configuration to the next index configuration. At the same time, based on the action value function, the action value of executing the next action to transfer the current index configuration to the next index configuration under the query workload W is calculated.
[0084] During each iteration, after obtaining the action value of this iteration, the iteration number, the action value of this iteration, the action corresponding to the action value (i.e. the next action mentioned above) and the index configuration (such as the current index configuration, or the current index configuration and the next index configuration) can be stored.
[0085] Optionally, based on the action value function, calculating the action value of performing the next action under the query workload and transferring the current index configuration to the next index configuration includes:
[0086] Based on the reward function, calculate the reward value for executing the next action under the query workload and transferring the current index configuration to the next index configuration; based on the action value function, the reward value, the action value of the previous iteration and the maximum action value corresponding to the next index configuration, determine the action value of this iteration.
[0087] The reward function is used to characterize the query efficiency improvement and cost savings brought about by adjusting the index configuration under the query workload W.
[0088] Specifically, for each index configuration, including the current index configuration and the next index configuration, there is a corresponding certain cost, including but not limited to the creation cost and the maintenance cost. In the reward function, an item regarding the difference in the cost of the two index configurations may be included to characterize the cost savings under the new index configuration. At the same time, different index configurations have different query efficiencies, such as response time, when executing the same query workload. In the reward function, an item regarding the difference in the query efficiency of the two index configurations may also be included to characterize the improvement in query efficiency under the new index configuration.
[0089] The response time of query workload W when using a certain index configuration to query may be the sum of the response times of query items in query workload W when using the index configuration to query.
[0090] The action value corresponding to the next index configuration may be positively correlated with the reward value and the maximum action value corresponding to the next index configuration, that is, the larger the reward value and the maximum action value corresponding to the next index configuration, the larger the action value corresponding to the next index configuration.
[0091] For any index configuration, the corresponding maximum action value is the maximum value of the action values corresponding to the index configuration obtained at the current time.
[0092] Before the iteration process begins, an action value lookup table can be created and initialized to record the action values obtained in each iteration. The initial value of the action value can be a small random number or set based on experience. When calculating the action value of a new round of iteration, the action value of the previous round of iteration and the maximum action value corresponding to the next index configuration can be found based on the action value lookup table.
[0093] Exemplarily, the action value function can be expressed as:
[0094] Q(k+1)=Q(k)+α[R(i k+1)+γmax a Q(i k+1 )-Q(k)]
[0095] Where k represents the number of iterations; Q(k+1) represents the action value calculated at the k+1th iteration, and the next index configuration at the k+1th iteration is recorded as i k+1 ;R(i k+1 ) is i k+1 Reward value; max a Q(i k+1 ) indicates i k+1 The corresponding maximum action value; α and γ are both constants, α represents the learning rate, and γ is the discount factor, which is used to evaluate the importance of the maximum action value.
[0096] The action value of the new index configuration is calculated by the reward value that represents the immediate return value and the maximum action value that represents the long-term return value. This allows the action value to better represent the long-term and short-term benefits brought by the new index configuration, thereby improving the quality of the index configuration selected based on the action value.
[0097] Optionally, the reward function is a weighted average of a query improvement reward item and a cost saving reward item; the query improvement reward item is the difference between the query cost of the current index configuration and the query cost of the next index configuration; the query cost of the index configuration is the sum of the query costs of each query item in the query workload; the cost saving reward item is the difference between the sum of the creation cost and the maintenance cost of the current index configuration and the sum of the creation cost and the maintenance cost of the next index configuration.
[0098] For example, the reward function is expressed as:
[0099] R(i k ) = k 1 ·R time (i k )+k 2 ·R cost (i k )
[0100]
[0101]
[0102] Among them, R(i k ) represents the reward function; i k represents the kth index configuration, which can be the aforementioned next index configuration; i cur Indicates the current index configuration; R time (i k ) indicates index configuration i k The query improvement reward item is specifically the query time improvement reward item; Rcost (i k ) indicates index configuration i k Cost saving incentives; k 1 and k 2 are weight coefficients, which are configurable parameters; m is the number of query items included in the query workload; Indicates that the jth query item in the query workload is using the current index configuration i cur The response time is This means that the jth query item in the query workload uses index configuration i k Response time and Respectively represent the j-th query item in the query workload using the current index
[0103] Configuration cur Creation and maintenance costs; and They represent the jth query item in the query workload using index configuration i. k The creation cost and maintenance cost of
[0104] By comprehensively considering the improvement of query performance and cost savings, the reward value under the new index configuration is calculated, which improves the comprehensiveness and accuracy of the reward value calculation, thereby providing data support for search configuration optimization.
[0105] Step S205: update the current index configuration to the next index configuration.
[0106] Update the index configuration of the database from the current index configuration to the next index configuration to achieve state transfer. After the state transfer, a round of iteration is completed, and the number of iterations is increased by 1. If the iteration end condition is not met, a new round of iteration begins, and a new action is selected from the action space, the action value is calculated, and the state transfer is performed, and so on, until the iteration end condition is met.
[0107] The index selection method provided in this embodiment realizes the adjustment of the index configuration of the database by constructing an action space, and obtains a variety of possible index configurations. Through the iterative process, at each iteration, an action value is obtained based on the historical iteration, and the action of this iteration is obtained from the action space, and then the index configuration is updated under the action of this iteration, and the action value of the new index configuration is calculated. It is determined whether to end the iterative process according to the change of the action value and the number of iterations. At the end of the iteration, the index configuration of the database is updated to the index configuration with the largest action value. Automatic optimization of the database index configuration based on the reinforcement learning algorithm is realized, and efficiency is improved. The reinforcement learning algorithm is used to continuously search for a better index configuration in the space, thereby improving the quality of index configuration optimization and thus improving the query performance of the database.
[0108] Figure 3 A flowchart of another index selection method provided in an embodiment of the present application. Figure 2 On the basis of the illustrated embodiment, step S202 is further refined, and a step of updating the action value lookup table is added after step S203.
[0109] like Figure 3 As shown, the index selection method provided in this embodiment may specifically include the following steps in each iteration:
[0110] Step S301, searching the action value lookup table for the maximum action value corresponding to the current index configuration, and taking the action corresponding to the maximum action value as the target action.
[0111] The action value search is used to record the action value of each index configuration obtained under each action.
[0112] Before the iteration begins, the action space and the state space may be pre-constructed, and the state space includes a new index configuration obtained by adjusting the current index configuration of the database under each action in the action space.
[0113] The action space and state space can be constructed based on the current index configuration of the database and possible index operations, such as deleting, adding, and modifying indexes.
[0114] The action value lookup table uses each index configuration in the state space as a row (or column), and the action adjusted to obtain the index configuration as a column (or row), and records the action value of each index configuration obtained under each action.
[0115] Table 1 Action value lookup table
[0116]
[0117] Exemplarily, Table 1 is an action value lookup table provided in an embodiment of the present application. As shown in Table 1, the action value lookup table is for k index configurations, and index configuration i k The corresponding action is Action(i k ), that is, through the action Action(i k ) Adjust the current index configuration to index configuration i k Each pair of index configuration and action corresponds to two types of action values, namely, the action value Q when the state is improved and pro And the action value Q when the state is not improved dec The status with prompt means that the reward value of the index configuration is greater than 0, and the status without improvement means that the reward value of the corresponding index configuration is less than or equal to 0. pro (i k ) indicates the index configuration i calculated when the status is prompted k Corresponding action value; Q dec (i k ) indicates the index configuration i calculated without status prompt k The corresponding action value.
[0118] Before the iteration process begins, each action value in the action value lookup table is initialized, such as being initialized to a small random number. Then, as the iteration process proceeds, the action values in the action value lookup table are gradually updated, or action values are added to the action value lookup table.
[0119] When determining the next action in each iteration, first look up the maximum value of the action values of the current index configuration under each action based on the action value lookup table, that is, look up the maximum action value corresponding to the current index configuration, and take the action corresponding to the maximum action value as the target action.
[0120] Step S302: Based on the ε-greedy algorithm, randomly select an action from the constructed action space or select the target action as the next action configured for the current index.
[0121] Specifically, an ε-greedy algorithm can be used to randomly select an action from the action space as the next action with probability ε, and to select the target action as the next action with probability 1-ε.
[0122] The probability ε may be a fixed value or a dynamically changing value. For example, the probability ε may be determined based on the number of iterations.
[0123] Optionally, the probability ε in the ε-greedy algorithm is related to the number of iterations, or is related to the number of iterations and the action value corresponding to the number of iterations.
[0124] In some embodiments, the probability ε may be negatively correlated with the number of iterations.
[0125] In other embodiments, the probability ε at the current iteration may be determined based on the number of iterations of the current iteration and the distribution of action values obtained in previous iterations.
[0126] Specifically, the initial probability may be determined based on the number of iterations of this iteration, and then the initial probability may be increased or decreased based on the change trend of the action values obtained in previous iterations to obtain the probability ε.
[0127] The initial probability is negatively correlated with the number of iterations.
[0128] Exemplarily, the initial probability may be a linear decay function or a nonlinear function with the number of iterations as an independent variable.
[0129] Exemplarily, the initial probability may be a piecewise function of the number of iterations, and the number of iterations may be divided into multiple intervals by multiple thresholds, and different intervals correspond to different initial probabilities. Taking two thresholds, namely the first number and the second number, as an example, when the number of iterations is less than the first number, the initial probability is the first probability, when the number of iterations is greater than or equal to the first number but less than the second number, the initial probability is the second probability, and when the number of iterations is greater than or equal to the second number, the initial probability is the third probability.
[0130] When the action values calculated for many consecutive times recently show an increasing trend, the initial probability can be increased. Conversely, when the action values calculated for many consecutive times show a decreasing trend, the initial probability can be reduced.
[0131] When increasing or decreasing the initial probability, it may be increased or decreased in fixed steps, for example, increased or decreased by 0.1.
[0132] When increasing the initial probability, the value of the increase in the initial probability may also be determined based on the specific numerical values of the increase in the action values of two adjacent iterations.
[0133] When reducing the initial probability, the value by which the initial probability is reduced may also be determined based on the specific numerical values by which the action values of two adjacent iterations are reduced.
[0134] By combining the number of iterations and the action value, the probability ε is dynamically determined, which balances the exploration and learning capabilities of the reinforcement learning algorithm and speeds up the efficiency of finding the optimal index configuration.
[0135] Step S303: Based on the action value function, the action value of performing the next action under the query workload and transferring the current index configuration to the next index configuration is calculated.
[0136] Step S304: updating the action value lookup table based on the action value.
[0137] After each iteration calculates a new action value, the action value lookup table is updated based on the current index configuration and the next action corresponding to the new action value. For example, the action value corresponding to the current index configuration and the next action recorded in the action value lookup table is updated to the new action value, or the action value corresponding to the current index configuration and the next action is added to the action value lookup table to implement the maintenance of the action value lookup table and provide a basis for selecting actions for the next round of iteration.
[0138] Step S305: update the current index configuration to the next index configuration.
[0139] In this embodiment, the action values recorded in the action value lookup table that is continuously updated with the iterative process are used as a reference for action selection. Actions are randomly selected with probability ε and the best action is selected with probability 1-ε. This achieves a balance between exploration and learning in the reinforcement learning process, avoids search results falling into local optimality, improves the quality of index selection, and improves the efficiency of searching for the optimal index configuration.
[0140] Figure 4 A flowchart of another index selection method provided in an embodiment of the present application is shown in FIG. Figure 4 As shown, the index selection method mainly includes the following steps:
[0141] Taking the database as the environment, based on the current index configuration and index operation set of the database, the action space and state space are constructed. Each action in the action space corresponds to an index configuration in the state space. For example, action a in the state space k Corresponding index configuration i k , indicating that through action a k Update or adjust the database index configuration to index configuration i k .
[0142] Initialize the action value lookup table, for example, initialize each action value in the action value lookup table to a small random number, or initialize the action value based on prior knowledge.
[0143] In the iteration process, taking the kth iteration as an example, in the current index configuration i k Next, based on the ε-greedy algorithm, select the next action a from the action space k+1 ; Calculate and execute the next action a k+1 Get the action value and update the action value lookup table; execute the next action a k+1 Realize the state transfer of the environment and change the index configuration of the database from i k Update to i k+1 , completing one round of iteration. If the iteration end condition is not met, the k+1th iteration is continued, and the current index configuration at this time is i k+1; If the iteration end condition is met, the maximum action value is searched from the action value lookup table, and the next index configuration corresponding to the maximum action value, that is, the index configuration corresponding to the maximum action value is adjusted under the action corresponding to the maximum action value, which is the index configuration of the database finally output or selected.
[0144] Corresponding to the index selection method provided in the aforementioned embodiment, the embodiment of the present application also provides a corresponding device. Figure 5 A schematic diagram of the structure of an index selection device provided in an embodiment of the present application is shown in FIG. Figure 5 As shown, the index selection device includes: a load acquisition module 510 and an iteration module 520.
[0145] The load acquisition module 510 is used to obtain the query workload of the database; the iteration module 520 is used to repeatedly execute the following steps until the change in the action value obtained by multiple consecutive iterations is less than a preset threshold, or the number of iterations reaches a preset number, and the next index configuration corresponding to the maximum action value is determined as the index configuration of the database: based on the obtained action value, the next action of the current index configuration is selected from the constructed action space, wherein the action space includes multiple actions for adjusting the index configuration; based on the action value function, the action value of performing the next action under the query workload and transferring the current index configuration to the next index configuration is calculated; and the current index configuration is updated to the next index configuration.
[0146] Optionally, when calculating the action value in each iteration, the iteration module 520 is specifically configured to:
[0147] Based on the reward function, calculate the reward value for executing the next action under the query workload and transferring the current index configuration to the next index configuration; based on the action value function, the reward value, the action value of the previous iteration and the maximum action value corresponding to the next index configuration, determine the action value of this iteration.
[0148] Optionally, the reward function is a weighted average of a query improvement reward item and a cost saving reward item; the query improvement reward item is the difference between the query cost of the current index configuration and the query cost of the next index configuration; the query cost of the index configuration is the sum of the query costs of each query item in the query workload; the cost saving reward item is the difference between the sum of the creation cost and the maintenance cost of the current index configuration and the sum of the creation cost and the maintenance cost of the next index configuration.
[0149] Optionally, the iteration module 520 is further configured to:
[0150] After calculating the action value of performing the next action under the query workload and transferring the current index configuration to the next index configuration based on the action value function, the action value lookup table is updated based on the action value, wherein the action value lookup is used to record the action value of each index configuration obtained under each action.
[0151] Optionally, when selecting the next action in each iteration, the iteration module 520 is specifically configured to:
[0152] From the action value lookup table, find the maximum action value corresponding to the current index configuration, and use the action corresponding to the maximum action value as the target action; based on the ε-greedy algorithm, randomly select an action from the constructed action space or select the target action as the next action of the current index configuration.
[0153] Optionally, the probability ε in the ε-greedy algorithm is related to the number of iterations, or is related to the number of iterations and the action value corresponding to the number of iterations.
[0154] The index selection device provided in the embodiment of the present application can be used to execute the technical solution of the index selection method provided in any of the above embodiments of the present application. Its implementation principle and technical effect are similar, and this embodiment will not be repeated here.
[0155] Figure 6 This is a schematic diagram of the structure of an electronic device provided in an embodiment of the present application. Figure 6 As shown, the electronic device of this embodiment may include: at least one processor 601; and a memory 602 communicatively connected to the at least one processor; wherein the memory 602 stores instructions executable by the at least one processor 601, and the instructions are executed by the at least one processor 601 so that the electronic device executes the method described in any of the above embodiments.
[0156] Optionally, the memory 602 may be independent or integrated with the processor 601 .
[0157] When the memory 602 is independently provided, the device further includes a bus for connecting the memory 602 and the processor 601 .
[0158] The implementation principle and technical effects of the electronic device provided in this embodiment can be found in the aforementioned embodiments and will not be described in detail here.
[0159] An embodiment of the present application further provides a computer-readable storage medium, in which computer-executable instructions are stored. When the computer-executable instructions are executed by a processor, the method provided in any of the aforementioned embodiments can be implemented.
[0160] An embodiment of the present application also provides a computer program product, including a computer program, which implements the method provided in any of the above embodiments when executed by a processor.
[0161] In the several embodiments provided in this application, it should be understood that the disclosed devices and methods can be implemented in other ways. For example, the device embodiments described above are only schematic, for example, the division of the modules is only a logical function division, and there may be other division methods in actual implementation, such as multiple modules can be combined or integrated into another system, or some features can be ignored or not executed.
[0162] The above-mentioned integrated module implemented in the form of a software function module can be stored in a computer-readable storage medium. The above-mentioned software function module is stored in a storage medium, including a number of instructions for causing a computer device (which can be a personal computer, a server, or a network device, etc.) or a processor to perform some steps of the method described in each embodiment of the present application.
[0163] It should be understood that the processor may be a central processing unit (CPU), or other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), etc. A general-purpose processor may be a microprocessor or any conventional processor. The steps of the method disclosed in the application may be directly implemented as being executed by a hardware processor, or may be implemented by a combination of hardware and software modules in the processor.
[0164] The memory may include a high-speed memory, and may also include a non-volatile memory, such as at least one disk memory, and may also be a USB flash drive, a mobile hard disk, a read-only memory, a magnetic disk, or an optical disk.
[0165] The bus can be an Industry Standard Architecture (ISA) bus, a Peripheral Component Interconnect (PCI) bus, or an Extended Industry Standard Architecture (EISA) bus, etc. The bus can be divided into an address bus, a data bus, a control bus, etc. For ease of representation, the bus in the drawings of the present application is not limited to only one bus or one type of bus.
[0166] The storage medium may be implemented by any type of volatile or non-volatile storage device or a combination thereof, such as static random access memory, electrically erasable programmable read-only memory, erasable programmable read-only memory, programmable read-only memory, read-only memory, magnetic memory, flash memory, magnetic disk or optical disk. The storage medium may be any available medium that can be accessed by a general or special purpose computer.
[0167] An exemplary storage medium is coupled to the processor so that the processor can read information from the storage medium and write information to the storage medium. Of course, the storage medium can also be an integral part of the processor. The processor and the storage medium can be located in a dedicated integrated circuit. Of course, the processor and the storage medium can also exist as discrete components in the electronic device or the control device of the vehicle sentry mode.
[0168] It should be noted that, in this article, the terms "include", "comprises" or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, an element defined by the sentence "comprises a ..." does not exclude the existence of other identical elements in the process, method, article or device including the element.
[0169] The serial numbers of the above-mentioned embodiments of the present application are for description only and do not represent the advantages or disadvantages of the embodiments.
[0170] Through the description of the above implementation methods, those skilled in the art can clearly understand that the above-mentioned embodiment methods can be implemented by means of software plus a necessary general hardware platform, and of course by hardware, but in many cases the former is a better implementation method. Based on such an understanding, the technical solution of the present application, or the part that contributes to the prior art, can be embodied in the form of a software product, which is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk), and includes a number of instructions for a terminal device (which can be a mobile phone, computer, server, or network device, etc.) to execute the methods provided in each embodiment of the present application.
[0171] Those skilled in the art will readily appreciate other embodiments of the present application after considering the specification and practicing the invention disclosed herein. The present application is intended to cover any modification, use or adaptation of the present application, which follows the general principles of the present application and includes common knowledge or customary techniques in the art that are not disclosed in the present application. The specification and examples are intended to be exemplary only, and the true scope and spirit of the present application are indicated by the following claims.
[0172] It should be understood that the present application is not limited to the precise structures that have been described above and shown in the drawings, and that various modifications and changes may be made without departing from the scope thereof. The scope of the present application is limited only by the appended claims.
Claims
1. An index selection method, characterized in that: include: Get the query workload of the database; Repeat the following steps until the change in the action value obtained by multiple consecutive iterations is less than a preset threshold, or the number of iterations reaches a preset number, and determine that the next index configuration corresponding to the maximum action value is the index configuration of the database: Based on the obtained action value, selecting a next action of the current index configuration from the constructed action space, wherein the action space includes a plurality of actions for adjusting the index configuration; Based on the action value function, calculating an action value of performing the next action under the query workload to transfer the current index configuration to the next index configuration; Update the current index configuration to the next index configuration.
2. The method according to claim 1, characterized in that Calculating, based on the action value function, an action value for performing the next action under the query workload and transferring the current index configuration to the next index configuration, including: Based on the reward function, calculating a reward value for performing the next action under the query workload and transferring the current index configuration to the next index configuration; The action value of this iteration is determined based on the action value function, the reward value, the action value of the previous iteration, and the maximum action value corresponding to the next index configuration.
3. The method according to claim 2, characterized in that The reward function is a weighted average of a query improvement reward item and a cost saving reward item; The query improvement reward item is the difference between the query cost of the current index configuration and the query cost of the next index configuration; the query cost of the index configuration is the sum of the query costs of the query items in the query workload; The cost saving reward item is the difference between the sum of the creation cost and the maintenance cost of the current index configuration and the sum of the creation cost and the maintenance cost of the next index configuration.
4. The method according to any one of claims 1 to 3, characterized in that: After calculating the action value of performing the next action under the query workload and transferring the current index configuration to the next index configuration based on the action value function, the method further includes: Based on the action value, an action value lookup table is updated, wherein the action value lookup is used to record the action value of each index configuration obtained under each action.
5. The method according to claim 4, characterized in that Based on the obtained action value, the next action of the current index configuration is selected from the constructed action space, including: From the action value lookup table, find the maximum action value corresponding to the current index configuration, and use the action corresponding to the maximum action value as the target action; Based on the ε-greedy algorithm, an action is randomly selected from the constructed action space or the target action is selected as the next action configured for the current index.
6. The method according to claim 5, characterized in that The probability ε in the ε-greedy algorithm is related to the number of iterations, or to the number of iterations and the action value corresponding to the number of iterations.
7. An index selection device, characterized in that: include: A load acquisition module is used to obtain the query workload of the database; An iteration module is used to repeatedly execute the following steps until the change of the action value obtained by multiple consecutive iterations is less than a preset threshold, or the number of iterations reaches a preset number, and determine that the next index configuration corresponding to the maximum action value is the index configuration of the database: Based on the obtained action value, selecting a next action of the current index configuration from the constructed action space, wherein the action space includes a plurality of actions for adjusting the index configuration; Based on the action value function, calculating an action value of performing the next action under the query workload to transfer the current index configuration to the next index configuration; Update the current index configuration to the next index configuration.
8. An electronic device, characterized in that: include: A processor, and a memory communicatively connected to the processor; The memory stores computer-executable instructions; The processor executes the computer-executable instructions stored in the memory to implement the method according to any one of claims 1 to 6.
9. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer-executable instructions, which are used to implement the method according to any one of claims 1 to 6 when executed by a processor.
10. A computer program product, characterized in that The method comprises a computer program, wherein when the computer program is executed by a processor, the method according to any one of claims 1 to 6 is implemented.
