An index selection method based on deep reinforcement learning

Abstract

The present invention relates to the field of electrical communication technology, and in particular to an index selection method based on deep reinforcement learning, comprising creating a database instance; generating a training workload and a test workload according to the database instance and specified rules respectively; analyzing the training workload according to five heuristic rules to generate candidate indexes; setting index restriction conditions for this training based on the candidate indexes, and training an index selection agent based on the index restriction conditions; selecting an optimal index combination on a test workload using an index selection agent; creating an optimal index combination in a database instance, and adding corresponding indexes for specific instances, thereby improving the query performance of a database system, and solving the problem that the existing index selection method does not consider the impact of index interaction, and the query improvement rate decreases as the number of indexes increases.

Description

Technical Field

[0001] The present invention relates to the field of electrical communication technology, and in particular to an index selection method based on deep reinforcement learning. Background Art

[0002] Database systems have been running maturely and stably in the industry, and the era of big data has put forward higher requirements for database systems. The increase in data volume requires database systems to have faster query speeds and higher system throughput. The increasing number of data types and patterns makes the workload fast and diverse, requiring database systems to have the ability to respond quickly and accurately to dynamic changes in query loads. As the scale of data continues to increase, database performance tuning has become increasingly important for researchers, enterprises, and database administrators. An important aspect of database performance tuning is to choose the right index.

[0003] Setting appropriate indexes in the system can speed up the data access process. Existing index selection methods do not consider the impact of interactions such as specific application requirements and data distribution characteristics when indexing. As the number of indexes increases, the query improvement rate decreases, resulting in inappropriate indexes. Using inappropriate indexes not only wastes resources, but also causes unnecessary disk overhead and index maintenance costs for the system, such as index updates caused by insert, update, and delete queries, thereby reducing its performance. Summary of the invention

[0004] The purpose of the present invention is to provide an index selection method based on deep reinforcement learning, aiming to solve the problem that the existing index selection method does not consider the impact of index interaction, and there is a situation where the query improvement rate decreases as the number of indexes increases.

[0005] To achieve the above object, the present invention provides an index selection method based on deep reinforcement learning, comprising the following steps:

[0006] S1 creates a database instance;

[0007] S2 generates a training workload and a test workload respectively according to the database instance and the specified rules;

[0008] S3 analyzes the training workload according to five heuristic rules to generate candidate indexes;

[0009] S4 sets index restriction conditions for this training based on the candidate indexes, and trains the index selection agent based on the index restriction conditions;

[0010] S5 uses the index selection agent to select an optimal index combination on the test workload;

[0011] S6 creates the optimal index combination in the database instance.

[0012] The specific method of creating a database instance is as follows:

[0013] 1 GB of data is generated using the TPC-H tool, and the data is loaded into a database to obtain a database instance.

[0014] Among them, the five heuristic rules are:

[0015] Set all attributes that appear in the query statement as single-column indexes;

[0016] The attributes associated with the equality query and range query after the first character are used as multi-column indexes, and the order in the index is consistent with the order in the query statement;

[0017] If a second character appears, the multi-column index needs to contain the corresponding associated attributes and keep the order consistent;

[0018] If there are specific attributes to be queried, the multi-column index must contain all the attributes to be queried, which is a covering index;

[0019] If a multi-table multi-attribute join query occurs, all join attributes of each table in the join query will be set as multi-column indexes.

[0020] The specific method of training the index selection agent based on the index restriction condition is:

[0021] S40 defines the experience replay pool, target network update frequency, estimated Q network parameters and target network parameters of the index selection agent;

[0022] S41 initializes the environment state of the database to obtain an initialized database;

[0023] S42 enables the index selection intelligence to satisfy a preset action under a preset probability in the initialization database;

[0024] S43 executes the preset action, observes the reward and the next state, and arranges the current state, the preset action, the reward and the next state to obtain a storage sequence;

[0025] S44: inputting the stored sequence into the experience replay pool and updating the priority of the stored sequence;

[0026] S45 extracts batch training data from the storage sequence in the experience replay pool according to the priority level for updating to obtain a sampling sequence;

[0027] S46 calculates a target Q value based on the preset action, the storage sequence, the sampling sequence and the estimated Q network parameters;

[0028] S47 calculates the loss function of the index selection intelligence based on the target Q value, obtains a calculation result, and updates the current estimated Q network parameters according to the calculation result;

[0029] S48 updates the target network parameters after each interval of the target network updating frequency;

[0030] S49 iterates steps S40 to S48 until the number of iterations reaches the specified number and the training is completed.

[0031] The present invention discloses an index selection method based on deep reinforcement learning, which creates a database instance; generates a training workload and a test workload according to the database instance and specified rules respectively; analyzes the training workload according to five heuristic rules to generate candidate indexes; sets index restrictions for this training based on the candidate indexes, and trains an index selection agent based on the index restrictions; uses the index selection agent to select an optimal index combination on the test workload; creates a search for the optimal index combination in the database instance, and adds corresponding indexes for specific instances, thereby improving the query performance of the database system, and solving the problem that the existing index selection method does not consider the impact of index interaction, and the query improvement rate decreases as the number of indexes increases. BRIEF DESCRIPTION OF THE DRAWINGS

[0032] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0033] Figure 1 It is a flowchart of an index selection method based on deep reinforcement learning provided by the present invention.

[0034] Figure 2 is a flowchart for training an index selection agent.

[0035] Figure 3 It is a graph of the average cumulative reward value changing with the number of training steps.

[0036] Figure 4 This is a comparison chart of the query efficiency improvement rates of AutoAdmin and DDQN-MC.

[0037] Figure 5This is a comparison chart of the query performance improvement rates of NoDBA and DDQN-MC. DETAILED DESCRIPTION

[0038] Embodiments of the present invention are described in detail below, examples of which are shown in the accompanying drawings, wherein the same or similar reference numerals throughout represent the same or similar elements or elements having the same or similar functions. The embodiments described below with reference to the accompanying drawings are exemplary and are intended to be used to explain the present invention, and should not be construed as limiting the present invention.

[0039] See also Figures 1 to 5 The present invention provides an index selection method based on deep reinforcement learning, comprising the following steps:

[0040] S1 creates a database instance;

[0041] The specific method is: use the TPC-H tool to generate 1 GB of data, and load the data into the database to obtain a database instance.

[0042] Specifically, using SELECT T_A.C_B FROM...WHERE.... as a template, and then randomly selecting operators [>, <, =], predicates [AND, OR, JOIN], and aggregations [GROUP-BY, ORDER-BY], by randomly creating query conditions between one column and three columns, five groups of different types of query statements are created, each group generates four query statements, and the type examples are shown in Table 1:

[0043] Table 1 Query statement example table

[0044]

[0045] S2 generates a training workload and a test workload respectively according to the database instance and the specified rules;

[0046] Specifically, the values ​​of T_A and C_B are randomly selected from the database or replaced with COUNT(*), and 20 query statements for the training workload and the test workload are generated respectively.

[0047] S3 analyzes the training workload according to five heuristic rules to generate candidate indexes;

[0048] Specifically, in a query statement, creating indexes for attributes associated with predicate conditions, join conditions, GROUP-BY conditions, and ORDER-BY conditions can maximize query efficiency. Therefore, five heuristic rules are designed in this paper to provide high-quality candidate indexes to the greatest extent. The five heuristic rules are:

[0049] Set all attributes that appear in the query statement as single-column indexes;

[0050] The attributes associated with the equality query and range query after the first character (WHERE) are used as multi-column indexes, and the order in the index is consistent with the order in the query statement;

[0051] If the second character appears (ORDER-BY and GROUP-BY), the multi-column index needs to contain the corresponding associated attributes and keep the order consistent;

[0052] If there are specific attributes to be queried, the multi-column index must contain all the attributes to be queried, which is a covering index;

[0053] If a multi-table multi-attribute join query occurs, all join attributes of each table in the join query will be set as multi-column indexes.

[0054] Generation of candidate indexes:

[0055] Suppose there is a table with n columns. Create an index with dimension i (1≤i≤n) in the table. The first column of the index has n choices, the second column has n-1 choices, and so on. The number of possible indexes for this table is:

[0056]

[0057] If a table has 10 columns, there are 10 different possible indexes for 1 attribute, 90 possible indexes for 2 attributes, and 30,240 different possible indexes for 5 attributes. If you want to select multiple column indexes, the number of candidate indexes generated by enumeration will be very large, so you need to generate candidate indexes by using index evaluation rules. Limiting the number of candidate indexes can reduce the action dimension and state dimension in deep reinforcement learning.

[0058] S4 sets index restriction conditions for this training based on the candidate indexes, and trains the index selection agent based on the index restriction conditions;

[0059] Specifically, S40 defines the experience replay pool, target network update frequency, estimated Q network parameters, and target network parameters of the index selection agent;

[0060] Specifically, we first define the input of the index selection agent, the experience replay pool D, the capacity N, the number of training rounds V, the number of training steps per round T, the target network update frequency C, the estimated Q network parameters θ, and the target network parameters θ - .

[0061] S41 initializes the environment state of the database to obtain an initialized database;

[0062] Specifically, the basic elements of the index selection agent based on deep reinforcement learning can be expressed as:

[0063] (1) Environment: TPC-H instance based on PostgreSQL database.

[0064] (2) Agent: The agent is the index selection tuner that receives the state and reward from the environment and updates the strategy at each step to select the appropriate index.

[0065] (3) Action: During the index selection process, action a t It can be expressed as selecting an index i from the candidate indexes I at step t, i∈I.

[0066] (4) State: State s t It is represented by a vector, which is initialized to 0. Each position indicates whether the current candidate index is selected or not. When an index is selected, its corresponding position is set to 1, indicating that it has been selected. The dimension of the state space is equal to the dimension of the action space, that is, the number of candidate indexes.

[0067] (5) Policy: A policy is a function that maps states to actions. Policy π starts from s0 and selects an index at each time step to enter the next state s. t+1 , this process is repeated until the index number limit is reached. The result of executing the strategy is the selected index set.

[0068] (6) Reward: In reinforcement learning, reward is the direct source of experience for the agent to continuously improve itself and achieve its goals autonomously. When designing the reward function, we fully consider the impact of index interaction and the change of query performance under the current index configuration. We use Cost (W, I t ) and Cost (W, I0) under the initialization index configuration and Cost (W, I t-1 ) is compared, and its reward function is as shown in formula (6).

[0069]

[0070] α and β are two factors with a value range of [0,1], which are used to adjust the impact of index interaction on rewards. The larger the α / β is, the more the agent pays attention to the change in query performance brought about by the current index configuration relative to the previous index configuration, that is, the possible impact of index interaction, rather than considering the change in query performance of the current index configuration relative to the initial state.

[0071] S42 enables the index selection intelligence to satisfy a preset action under a preset probability in the initialization database;

[0072] Specifically, in state s t The lower index selection agent selects action a that satisfies formula (1) with probability ε t (the preset action), i.e. selecting the next index:

[0073]

[0074] S43 executes the preset action, observes the reward and the next state, and arranges the current state, the preset action, the reward and the next state to obtain a storage sequence;

[0075] Specifically, the current state s t , the preset action a t , the reward r t and the next state s t+1 Sort and get the storage sequence (s t ,a t ,r t ,s t+1 ).

[0076] S44 stores the stored sequence (s t ,a t ,r t ,s t+1 ) is input into the experience replay pool D, and the priority of the storage sequence is updated;

[0077] S45 extracts batch training data from the storage sequence in the experience replay pool according to the priority level for updating to obtain a sampling sequence;

[0078] S46 calculates a target Q value based on the preset action, the storage sequence, the sampling sequence and the estimated Q network parameters;

[0079] Specifically, the target Q value y is calculated according to formula (2): t ;

[0080]

[0081] S47 calculates the loss function of the index selection intelligence based on the target Q value, calculates the loss function according to formula (3), and updates the current Q network parameters according to the calculation result;

[0082]

[0083] S48 updates the target network parameters after each interval of the target network updating frequency;

[0084] Specifically, the target network parameter θ is updated every frequency C - ←θ.

[0085] S49 iterates steps S40 to S48 until the number of iterations reaches the specified number and the training is completed.

[0086] Specifically, after training is completed, the index selection agent learns the optimal strategy.

[0087] S5 uses the index selection agent to select an optimal index combination on the test workload;

[0088] S6 creates the optimal index combination in the database instance.

[0089] In order to verify the effectiveness of the method proposed in this paper (abbreviated as DDQN-MC), it is compared with the traditional index selection method AutoAdmin and the NoDBA method based on deep reinforcement learning, and the improvement of database query efficiency (i.e., the improvement of execution efficiency after index selection) under the same workload is used as the standard for comparative analysis. Using the improvement of query efficiency as the comparison standard can alleviate the impact of hardware conditions on this experiment to a certain extent.

[0090] Figure 3 The figure shows the change in the average reward value of DDQN-MC during the training process with the number of indexes limited to 3. It can be seen that as the number of training steps increases, the performance of the algorithm gradually improves. When the number of training steps increases by 500 times, the average reward value begins to stabilize and converge. The fluctuation of the reward indicates the continuous exploration behavior of the index selection agent.

[0091] AutoAdmin is an index selection algorithm proposed for Microsoft SQL Server. It can recommend both single-column and multi-column indexes. In order to compare with DDQN-MC, this paper uses AutoAdmin implemented for Postgresql for comparison. The workload is randomly generated by the template proposed in this paper, and the restriction condition is the number of indexes.

[0092] from Figure 4 It can be seen that the query efficiency improvement rate brought by the indexes selected by DDQN-MC increases with the increase in the number of indexes, and the query efficiency improvement brought by the indexes selected by DDQN-MC is significantly higher than that of the indexes selected by AutoAdmin under the same number of indexes. Because the impact of index interaction is not fully considered, the query improvement rate of AutoAdmin decreases slightly when the number of indexes is 3 and 6, respectively, compared with the number of indexes is 2 and 5, but DDQN-MC takes into account the impact of index interaction and does not have this situation. The experimental results show that under given workloads and constraints, DDQN-MC can select a better index combination than AutoAdmin.

[0093] NoDBA uses workload and current index configuration as input to the neural network, and uses deep reinforcement learning with a custom reward function to implement an index selection agent, but it cannot recommend multi-column indexes. Because NoDBA cannot select indexes for workloads containing multi-table join query statements, in order to be able to compare with DDQN-MC, the workload of this comparative experiment is generated using the random workload template proposed by NoDBA, using SELECT COUNT(*)FROM LINEITEM first character.... as the template, randomly selecting operators [>, <, =] and predicates AND, and randomly creating query conditions between one column and seven columns to generate 10 query statements for each training workload and test workload.

[0094] Since the query statements in the workload only operate on the LINEITEM table, DDQN-MC can recommend single-column indexes and multi-column indexes. Figure 5 The experimental results clearly show that the index combination selected by DDQN-MC under the same workload and the same number of indexes can greatly improve the query performance of the database system compared to NoDBA. When designing the reward function, NoDBA only considers the change in the query performance of the database system relative to the initial state of the current index configuration, and does not consider the impact of index interaction, so the query performance improvement rate increases slowly or even regresses as the number of indexes increases.

[0095] By comparing in different scenarios, DDQN-MC can select a better index combination, effectively improving the query performance of the database.

[0096] What is disclosed above is only a preferred embodiment of the index selection method based on deep reinforcement learning of the present invention. Of course, it cannot be used to limit the scope of rights of the present invention. Ordinary technicians in this field can understand that all or part of the processes of the above embodiments are implemented, and equivalent changes made according to the claims of the present invention are still within the scope of the invention.

Claims

1. An index selection method based on deep reinforcement learning, characterized in that: The following steps are involved: S1 creates a database instance; S2 generates a training workload and a test workload respectively according to the database instance and the specified rules; S3 analyzes the training workload according to five heuristic rules to generate candidate indexes; S4 sets index restriction conditions for this training based on the candidate indexes, and trains the index selection agent based on the index restriction conditions; S5 uses the index selection agent to select an optimal index combination on the test workload; S6 creates the optimal index combination in the database instance; The five heuristic rules are: Set all attributes that appear in the query statement as single-column indexes; The attributes associated with the equality query and range query after WHERE are used as multi-column indexes, and the order in the index is consistent with the order in the query statement; If ORDER-BY and GROUP-BY appear, the multi-column index needs to contain the corresponding associated attributes and keep the order consistent; If there are specific attributes to be queried, the multi-column index must contain all the attributes to be queried, which is a covering index; If a multi-table multi-attribute join query occurs, all join attributes of each table in the join query will be set as multi-column indexes.

2. The index selection method based on deep reinforcement learning according to claim 1, characterized in that: The specific method of creating a database instance is: 1 GB of data is generated using the TPC-H tool, and the data is loaded into a database to obtain a database instance.

3. The index selection method based on deep reinforcement learning according to claim 2, characterized in that: The specific method of training the index selection agent based on the index restriction condition is: S40 defines the experience replay pool, target network update frequency, estimated Q network parameters and target network parameters of the index selection agent; S41 initializes the environment state of the database to obtain an initialized database; S42 enables the index selection agent to satisfy a preset action under a preset probability in the initialization database; S43 executes the preset action, observes the reward and the next state, and arranges the current state, the preset action, the reward and the next state to obtain a storage sequence; S44: inputting the stored sequence into the experience replay pool and updating the priority of the stored sequence; S45 extracts batch training data from the storage sequence in the experience replay pool according to the priority level for updating to obtain a sampling sequence; S46 calculates a target Q value based on the preset action, the storage sequence, the sampling sequence and the estimated Q network parameters; S47: Calculate the loss function of the index selection intelligence based on the target Q value, obtain a calculation result, and update the current estimated Q network parameters according to the calculation result; S48 updates the target network parameters at a frequency interval; S49 iterates steps S40 to S48 until the number of iterations reaches the specified number and the training is completed.

Images