Database Physical Optimization By Deep Reinforcement Learning

No matter how advanced a database is,performance has always been the focus of users and database vendors.Different applications have the different appropriate configuration.Database administrators need to configure and optimize the database according to the application to meet their performance requirements.However,designing an efficient physical configuration is difficult.First,designing an efficient physical configuration itself has proved to be an NP-Hard problem;second,the development of the business leads to the continuous expansion of data scale and query complexity,and the difficulty and frequency of tuning increase.There are three methods for the physical configuration of existing databases: manual tuning,traditional automatic tuning,and learning-based automatic tuning.Experience-based manual tuning usually results in sub-optimal configuration schemes.The traditional automatic tuning method codes the existing human experience,sets a specific optimization goal,adopt a greedy algorithm to get a solution using a computer.But the configuration scheme is usually also sub-optimal.Neural networks have a good ability to simulate high-dimensional relationships,and researchers have proposed many physical configuration schemes based on learning.However,scenarios supported by existing solutions are often constrained.Based on the advantages of the learning model and the problems of the above methods,this paper carries out research on physical optimization schemes based on deep reinforcement learning,mainly including the following aspects:First of all,the previous method based on reinforcement learning can only recommend single-table or even single-column indexes,without considering the constraints satisfied by the indexes into the model,and cannot model the interaction between indexes.To this end,we studied index recommendation for multiple tables under static workload considering the constraints on indexes to be built,and the recommended indexes include multi-attribute indexes.Furthermore,we discuss how to extend this method to dynamic workload situations.Second,we studied the materialized view recommendation that applies reinforcement learning for dynamic workload.The model can dynamically generate and delete materialized views based on workload.To quickly evaluate the performance of a query that can be rewritten using a materialized view,we also propose and implements a subquery-based rewriting method to evaluate the performance of a materialized view on a query.Third,we studied how to use reinforcement learning to create partitions.We refer to the idea of the agent committee to deal with the creation of partitions for different workloads.Different from the existing methods,we propose a new agent determination method and model training method.Furthermore,we deal with the situation where new queries are added.