Study On Cardinality Estimation Method Based On Multi-Head Self-Attention Mechanism

The cardinality estimation in the database is an important issue in the database field,because the query optimizer of the database will select the final plan to be executed from multiple physical execution plans according to the expected results obtained by the cardinality estimation,which directly affects the accuracy of the query and effectiveness.MySQL database is a classic among relational databases,and its query optimizer can optimize the input SQL query statement to obtain a more efficient execution plan.However,MySQL still has two key problems when optimizing SQL query statements:(1)The built-in query optimizer of MySQL has low accuracy in estimating the cardinality of SQL query statements,especially when there are multiple Join connections,resulting in no Select a better query execution plan;(2)When MySQL connects multiple tables,the search space for the connection plan selection is too large,resulting in the inability to accurately evaluate the cost of the execution plan corresponding to the query statement,resulting in low query performance.In recent years,using machine learning methods to solve some traditional problems in query optimization in databases has become a new research hotspot.However,the existing machine learning-based methods still have some problems in the feature extraction of SQL query statements,such as poor feature extraction capabilities for query statements,and lack of consideration of the underlying implementation of the database for query statements containing Join.This paper focuses on some problems existing in MySQL database query optimization and the deficiencies in existing research,and studies the optimization method of MySQL database query optimization by using machine learning.First,to solve the problem of low accuracy of query cardinality estimation in MySQL database,this paper designs a cardinality estimation model based on the multi-head attention mechanism in neural network.By analyzing the traditional cardinality estimation method that does not consider the possible logical dependencies between the various tables and data columns in the database,and the problem of low accuracy,the breadth-first algorithm is used to generate labels based on the IMDB dataset.The data set;by using a variety of finer-grained coding methods to effectively extract the logical features between the data for different components of the sentence;The accuracy of cardinality estimates in optimization.Secondly,aiming at the problem of connection plan selection in multi-table connection of MySQL database,this paper designs a connection plan selection method for MySQL query statements based on reinforcement learning.This method uses the form of connection tree to reconstruct the multi-table connection plan of MySQL database,and converts the cost model of the underlying implementation method of MySQL database connection selection into a Markov decision process.Then use the DQN algorithm for intensive learning training,and finally effectively reduce the time for MySQL to execute multi-table join queries.Finally,the experimental results on the IMDb dataset show that the method in this paper effectively improves the accuracy of cardinality estimation in MySQL database query optimization,and can reduce the query time when the query statement contains multi-table joins,which verifies the method in this paper effectiveness.