Research On Calcite-Based Query Optimization Approaches For Relational Databases

As a fundamental software for data storage and management,databases’ query performance directly affects the experience of user.The rapid development of Internet technology has generated a large amount of data,which poses challenges for database query optimization.Materialized view(mv)is one method of query optimization,which involves multiple stages such as cost estimation,view design,and view utilization.However,big data presents technical challenges for related solutions,while the rapid development of technologies such as machine learning,logic-based equivalent subquery recognition,and the Calcite open-source optimizer has brought new opportunities to each stage.(i)For the cost estimation stage,a graph attention network is introduced to learn the dependency between nodes.By introducing statistical information such as base table selectivity and join selectivity,and using learned embedding to replace one-hot encoding,an improved feature encoding method is proposed,which can simultaneously estimate the cost of the original query statement and query statement rewritten based on mvs.(ii)For the view design stage,a view design scheme is studied that simultaneously covers view selection strategies and view exit mechanisms.The scheme includes a candidate mv selection algorithm based on the Cosette equivalent query statement recognizer and a mv scoring mechanism based on Newton’s cooling law.(iii)For view utilization,mv query rewriting technology based on Calcite is implemented.The system can register specified mv definitions and complete the rewriting of the original query statement in the context of the mvs,and finally evaluate the rewriting scheme using the above cost estimation model.Finally,the system that optimizes queries through mvs has been implemented using the above technology.The experimental results of two query workloads on real-world datasets show that compared with Postgre SQL,TGAE has significantly improved cost estimation accuracy and rewrite evaluation accuracy.Under the condition that the static rewritten query statement has lower cost,the number of TGAE evaluations that are correct on average increased by 20.2%(Synthetic)and 45.7%(JOB-light).