Research On Learning-based Database Query Optimization

With the rapid development of communication technology and the internet,data volume has proliferated and database query efficiency has gradually become a bottleneck for large-scale business performance.In database query optimization,cardinality estimation and join order selection are the two most crucial components.Traditional cardinality estimation algorithms are unable to utilize the underlying data distribution,resulting in significant errors.Additionally,traditional join order selection is unable to efficiently search the vast solution space,leading to excessively long execution time for query plans.This thesis deeply investigates the current research status of database query optimization and the frontier technology of artificial intelligence,and innovatively proposes a learningbased query optimization algorithm.In order to solve the problem of huge errors in traditional cardinality estimation,this thesis proposes a learning based cardinality estimation algorithm GTR based on Graph Transformer.By studying the current query optimizer structure and combining the cuttingedge results in the field of graph neural networks,this thesis encodes queries into graphstructured data,utilizes Graph Transformer based on graph attention mechanism to extract query features,perceives the underlying data distribution patterns,mines the correlation information between attributes and tables,and finally significantly improves the accuracy of cardinality estimation.In order to solve the problem that traditional join order selection has difficulty in producing excellent execution plans,this thesis proposes a join order selection algorithm TARL based on Tree Attention and reinforcement learning.The thesis abstracts the join order selection as a Markov decision process and solves it through reinforcement learning.Based on the Attention mechanism,combined with the tree structure and data transmission characteristics of query execution plans,this thesis proposes Tree Attention to extract features from the execution plan,and combines it with GTR to extract features from the original query,which together make up the value network model.By using the DQN reinforcement learning algorithm framework and interacting with the database,the model is trained with real-time delay as a guide to predict long term rewards of joins,generating excellent query execution plan and significantly reducing query latency.Finally,this thesis combines the learning based cardinality estimation algorithm GTR and join order selection algorithm TARL with Ti DB to implement a prototype system.The experimental results show that the cardinality estimation algorithm GTR and the join order selection algorithm TARL have significantly improved multiple indicators compared to Ti DB and other learning based algorithms,sensibly improving the database query efficiency.