关键词: 查询优化, 计划生成, 学习排序, 数据库, 连接顺序, 连接类型, 扫描类型

Abstract: Query optimization is a key aspect of relational databases.In the traditional query optimization process,cardinality estimation of join and filter operations in a query is usually required in order to obtain a better execution plan.However,due to the inaccuracy of cardinality estimation,the results of query optimization are often unsatisfactory.Currently,some researches have been conducted to improve the cardinality estimation through machine learning-based methods and have made some progress.This paper finds that although these methods perform better in dealing with filtering predicates of numerical types in queries,they are ineffective for other complex filtering predicates.To address this problem,this paper proposes a query optimization algorithm based on learning to rank.The algorithm is capable of intelligently evaluating and ranking multiple execution plans for a single query to select the best plan for execution.The query optimization algorithm iteratively mines the better execution plans and collaborates with machine learning methods to finally filter out the optimal plan.Experimental results show that the proposed algorithm outperforms current learning-based query optimization algorithms on regular datasets and is more significant on complex datasets.

Key words: Query optimization, Plan generation, Learning to rank, Database, Join order, Join type, Scan type