Intelligent Index Recommendation Based On Adaptive Database Data Change

In databases,selecting the appropriate index configuration for a set of workloads can greatly improve the query efficiency of the database.However,choosing the right indexes for a database is not a simple task.Manual-based approaches for recommending indexes overly rely on their expertise and experience and have difficulty in dealing with index recommendations under complex workloads,while machine-learning-based approaches often require exploring large action and state spaces,thus consuming more costs and recommending suboptimal index configurations.Besides,they target mainly static databases,ignoring the dynamic changes in database data.In this paper,the index selection problem in database query optimization is as the main object of study to recommend the best index configuration for a set of workloads in a dynamic database to effectively solve the index selection problem,as follows:(1)Aiming at the problems of too many index candidates and low efficiency of algorithm training,we propose the RBOIRA,which combines rules and reinforcement learning to recommend the optimal index configuration for a set of workloads in a dynamic database.First,three heuristic rules are designed for pruning the index candidates.Second,combining the rules with reinforcement learning to recommending the optimal single-attribute index configuration for a set of workloads under a dynamic database.Finally,all experiments are conducted using the TPC-H standard dataset,where the comparison methods include the self-defined baselines and other related reinforcement learning methods.Experimental results show that RBOIRA is superior to other methods in all comparative experiments,where the ablation experiment and the selectivity threshold experiment further prove the rationality and effectiveness of the rules,and the performance analysis experiment of adaptive data change proves that it has certain robustness.(2)Aiming at the problems of unable to update index configuration,recommending only single-attribute indexes,and dynamic database self-adaptation,we propose the MIRA,which uses reinforcement learning to recommend the optimal multi-attribute index configuration for a set of workloads in database data change.First,index candidates are extracted based on workload characteristics.Second,the existing index configurations are updated in real time by using reinforcement learning to recommend the optimal multi-attribute index configuration for a set of workloads in a dynamic database.Finally,the experiments on the performance improvement ratio,index space consumption,and TPC-H performance test are conducted using the TPC-H dataset,where the comparison methods include self-defined baselines and other related reinforcement learning methods.Experimental results show that the MIRA not only outperforms its compare methods in query performance(ranging from about 3.84% to74.48%),but also that the index space consumed is much lower than the storage space consumed when all columns are indexed(about 59.41% of it),which fully proves the superiority of MIRA.