Journal of Computer Applications ›› 2020, Vol. 40 ›› Issue (2): 420-425.DOI: 10.11772/j.issn.1001-9081.2019101762

• CCF NDBC 2019 • Previous Articles     Next Articles

Query execution plan selection under concurrent query

Zefeng PEI, Baoning NIU(), Jinwen ZHANG, Muhammad AMJAD   

  1. College of Information and Computer,Taiyuan University of Technology,Taiyuan Shanxi 030024,China
  • Received:2019-09-18 Revised:2019-10-03 Accepted:2019-10-24 Online:2019-10-31 Published:2020-02-10
  • Contact: Baoning NIU
  • About author:PEI Zefeng, born in 1995, M. S. candidate. His research interests include database management and optimization.
    ZHANG Jinwen, born in 1988, Ph. D. His research interests include database system performance management.
    AMJAD Muhammad, born in 1978, Ph. D. His research interests include database query performance prediction.
  • Supported by:
    the National Natural Science Foundation of China(61572345)

并行查询下查询执行计划的选择

裴泽锋, 牛保宁(), 张锦文, Muhammad Amjad   

  1. 太原理工大学 信息与计算机学院,太原 030024
  • 通讯作者: 牛保宁
  • 作者简介:裴泽锋(1995—),男,山西兴县人,硕士研究生,CCF会员,主要研究方向:数据库管理及优化
    张锦文(1988—),男,山西阳泉人,博士,CCF会员,主要研究方向:数据库系统的性能管理
    Amjad Muhammad(1978—),男,巴基斯坦人,博士,CCF会员,主要研究方向:数据库查询性能预测。
  • 基金资助:
    国家自然科学基金资助项目(61572345)

Abstract:

Query is the main workload of a database system, and its efficiency determines the performance of the database system. There are multiple execution plans for a query, and the existing query optimizers can only statically select a better execution plan for a query according to the configuration parameters of the database system. There are complex and variable resource contentions between concurrent queries, and such contentions are difficult to be reflected accurately through configuration parameters; besides, the efficiency of the same execution plan is not consistent in different scenarios. The selection of the execution plans for concurrent queries needs to consider the influence between queries — query interaction. Based on the above, a metric for measuring the influence of query interaction on the query under concurrent query called QIs (Query Interactions) was proposed. For the selection of query execution plan under concurrent query, a method called TRating (Time Rating) was proposed to dynamically select the execution plan for the query. In the method, the influence of query interaction on the queries executed with different plans in the query combination was measured, and the plan with small influence of query interaction was selected as the better execution plan for the query. Experimental results show that TRating can select a better execution plan for the query with an accuracy of 61%, which is 25% higher than that of the query optimizer; and the accuracy of the proposed method is as high as 69% when selecting suboptimal execution plan for the query.

Key words: concurrent query, query interaction, query optimizer, query execution plan, better execution plan

摘要:

查询是数据库系统的主要负载,其效率决定了数据库性能的好坏。一个查询存在多种执行计划,当前,查询优化器只能按照数据库系统的配置参数,静态地为查询选择一个较优的执行计划。并行查询间存在复杂多变的资源争用,很难通过配置参数准确反映,而且同一执行计划在不同情景下的效率并不一致。并行查询下执行计划的选择需考虑查询间的相互影响——查询交互。基于此,提出了一种在并行查询下度量查询受查询交互影响大小的标准QIs。针对并行查询下查询执行计划的选择,还提出了一种动态地为查询选择执行计划的方法TRating,该方法通过比较查询组合中按不同执行计划执行的查询受查询交互影响的大小,选择受查询交互影响较小的执行计划作为该查询的较优执行计划。实验结果表明,TRating方法为查询选择较优执行计划的准确率达61%,相比查询优化器提高了25%;而且在为查询选择次优执行计划时,其准确率也高达69%。

关键词: 并行查询, 查询交互, 查询优化器, 查询执行计划, 较优执行计划

CLC Number: