Research On Automatic Recommendation Method Of Secondary Index For Cloud Database

In recent years,cloud databases have gained a broad market by virtue of their ondemand purchase,volume-based pricing,high scalability,and high availability.The current cloud database systems either don't establish secondary indices,or the developers manually add the indices after analyzing the user's query characteristics,resulting in long query time,slow queries,low system throughput,and low efficiency.The current researches on automatic index recommendation have problems such as the inability to recommend multikey indices and the inability to establish a general model for multiple workloads,which are not applicable to the business scenarios of cloud databases.Therefore,it is of great significance to study the automatic recommendation method of secondary indexes for cloud databases.For problem of the lack of indices in cloud databases,a single-workload index automatic recommendation method based on reinforcement learning is first designed.This method uses deep Q-learning to build an index recommendation model,designs an action space that can recommend multi-key indices and a reward function that makes a balance between the time and space benefits,and automatically learns index recommendation strategy through feedback information in the process of interacting with the database.To overcome the shortcoming that the single-workload model with poor versatility and long training time can only recommend indices for a single workload,a more versatile automatic index recommendation method for multiple-workloads is proposed.First,a deep neural network is used to analyze the relationship between query time and query selectivity to establish the query time prediction model,and then deep Q-learning is used to interact with query time prediction model to obtain the execution time of multiple-workloads under different index configurations,which automatically learnes index recommendation strategies for multiple-workloads,and finally k-means clustering algorithm is used to convert the cloud database workloads containing a large number of queries into several query templates according to selectivity characteristics,and index configurations are recommended for them.In order to verify the effectiveness of the method,a comprehensive test was conducted using real cloud database instances provided by TPC-H and a well-known cloud service provider.The results show that with the index configuration recommended by the singleworkload model,the execution time of the workload is reduced by 82%-99% compared with the case without index,and it is reduced by 31%-88% compared with the single-key index recommendation method.The index configurations recommended by the multipleworkloads model reduce the average execution time of the simulated workloads by 56%-72%,reduce the total workload execution time by 76% compared with the existing index recommendation method for multiple workloads,and reduce the execution time of a real workload containing 1000 different queries by 92%.