基于多线程通信机制的云数据库查询优化方法

Cloud Database Query Optimization Method Based on Multi-threaded Communication Mechanism

从本地数据库架构转变为云上数据库架构,对于大型复杂系统的高效运行和海量异构数据的存储分析至关重要。云数据库的启发式查询优化方法存在局限性,如难以获得最优执行计划使得查询性能下降、无法从历史查询中学习经验等。基于学习的优化方法虽然能够提升查询性能,但也存在缺陷,如没有对物理运算符进行编码、无法捕捉执行计划的结构特征、依赖于错误的基数估计和代价估计、现有强化学习算法的探索性较差、训练速度较慢以及收敛不稳定。针对上述问题,提出了一种新型基于异步Soft Actor-Critic的连接查询优化器(Asynchronous Soft Actor-Critic for Join Query, ASA-Join)。ASA-Join利用多线程通信机制设计了异步Soft Actor-Critic算法,能够增加策略的随机性和智能体的探索能力,尽可能避免陷入局部最优解;集成了一种新的编码方法,将物理运算符和执行计划的结构特征进行编码,能够准确表示执行计划;设计了状态表示模型,利用Bidirectional Gate Recurrent Unit (BiGRU)来捕捉执行计划的结构特征;设计了一种新的奖励机制,利用多任务学习方法将执行计划的代价和延迟均作为优化目标,使得执行计划能够反映真实的查询时间。在真实数据集JOB和TPC-H上对ASA-Join的代价和延迟进行评估。实验结果表明,ASA-Join的性能优于现有基于强化学习的优化器。

The transformation from local database architecture to cloud database architecture is crucial for the efficient operation of large and complex systems and the storage and analysis of massive heterogeneous data. However, the heuristic query optimization method of cloud database has limitations, such as difficulty in obtaining the optimal execution plan, which reduces query performance and leads to inability to learn experiences from historical queries. Although learning-based optimization methods can improve query performance, these methods still have some shortcomings, such as not encoding physical operators, being unable to capture the structural characteristics of the execution plan, relying on wrong cardinality estimation and cost estimation, poor exploratory properties of existing reinforcement learning algorithms, slow training speed, and unstable convergence. To overcome the above problems, a novel type of join query optimizer based on asynchronous Soft Actor-Critic is proposed, called Asynchronous Soft Actor-Critic for Join Query(ASA-Join). ASA-Join uses the multi-threaded communication mechanism to design an asynchronous Soft Actor-Critic algorithm, which can increase the randomness of the strategy and the exploration ability of the agent, and avoid falling into the local optimal solution as much as possible. ASA-Join integrates a new encoding method, which encodes the physical operator and the structural features of the execution plan, which can accurately represent the execution plan. ASA-Join designs a state representation model and uses Bidirectional Gate Recurrent Unit(BiGRU) to capture the structural features of the execution plan. ASA-Join designs a new reward mechanism, which uses the multi-task learning method to take both the cost and latency of the execution plan as the optimization goal, so the execution plan can reflect the real query time. The cost and latency of ASA-Join are evaluated on real datasets JOB and TPC-H, and the experimental results show that ASA-Join outperforms existing reinforcement learning-based optimizers.