Research On Optimization Of Multi-table Join Order Of Database Based On Monte Carlo Tree Search

The rapid development of the internet has witnessed a wider application of databases,promoting the development of database performance optimization.The study of database query optimization stands as an important branch of researches in the field of database performance optimization.The optimization of multi-table's joining process lies at the core of almost all database query optimizer whose aim is to make query plans that can be executed within minimum duration.Due to the inaccuracy of the database cost model,its statistics,as well as the limitation of the search algorithm,it is commonly seen that the current database management system could easily miss the join order that can be executed within shorter amount of time.An optimization method of the join order based on machine learning and Monte Carlo tree search is introduced to solve the problems mentioned above,which includes the join order selector and adaptive decision network.The Join order selector is used for the selection of the optimum database join order.At first,a new type of coding query plan is made for the databases,solving the problems of corresponding the coding plan with the join order.Later,a value network is designed for predicting the corresponding executing time of the query plan.The recorded query plan is used for the training of the network within the corresponding executing time.The network is then used for the reward feedback in the Monte Carlo tree search.After that,various kinds of join orders are simulated through the Monte Carlo tree and then assessed by the value network with UCT(Upper Confidence Bound Apply to Tree)in order to strike a balance between explorations and exploitations.When finishing the regulated explorations,the system would return to a recommended join order.Finally,in order to improve the general performance of the optimization system,an adaptive decision-making network is designed to select which join order selector the system is going to use.The PostgreSQL database together with the Join Order Benchmark are taken to test the system.At first,various query experiments are carried out on the PostgreSQL that has adopted the join order selector.As a result,the general performance of the system has been improved,which is better than the original PostgreSQL and the lastest optimization tool.Later,experiments are conducted on PostgreSQL that has adopted both the adaptive decision-making network and the join order selector.Eventually,the experiment has further helped to optimize the system.