华东师范大学学报（自然科学版） ›› 2020, Vol. 2020 ›› Issue (6): 52-62.doi: 10.3969/j.issn.1000-5641.201921013

• 计算机科学 • 上一篇    下一篇

CedarAdvisor: 负载自适应的自动化索引推荐工具

阳文灿, 胡卉芪, 段惠超, 胡耀艺, 钱卫宁   

  1. 华东师范大学 数据科学与工程学院, 上海 200062
  • 收稿日期:2019-08-26 发布日期:2020-12-01
  • 通讯作者: 胡卉芪, 男, 博士, 副教授, 主要研究方向为数据库系统、分布式系统. E-mail: hqhu@dase.ecnu.edu.cn E-mail:hqhu@dase.ecnu.edu.cn
  • 基金资助:
    国家自然科学基金青年科学基金(61702189)

CedarAdvisor: A load-adaptive automatic indexing recommendation tool

YANG Wencan, HU Huiqi, DUAN Huichao, HU Yaoyi, QIAN Weining   

  1. Institute for Data Science and Engineering, East China Normal University, Shanghai 200062, China
  • Received:2019-08-26 Published:2020-12-01

RichHTML

365

PDF

282

摘要： 索引在提高查询性能方面可以起到至关重要的作用, 数据库管理员的一项重要工作是为数据库选择合适的索引. 但随着数据库工作负载的不断复杂、数据量的持续增加、库中关系表的逐渐增多, 人为地分析工作负载成本、选择合适的索引并估算数据库空间变化情况变得越发困难. 本文设计了一款面向负载的自动化索引推荐工具——CedarAdvisor. 它从日志中自动化收集负载, 统计查询频率, 在为单条查询生成候选索引的基础上, 评估索引收益与代价, 通过动态规划算法生成整个负载的索引推荐. 最后我们在分布式数据库上验证了工具的有效性.

关键词: 数据库, 工作负载, 索引推荐

Abstract: Indexes play a pivotal role in improving the performance of queries. One of the important tasks of database administrators is to select appropriate indexes for databases. However, with the increasing complexity of database workloads, the rising amount of data volumes, and the gradually increasing number of tables, it has become more and more difficult to artificially analyze workload cost, select appropriate indexes, and estimate database spatial changes. This paper designs a load-oriented automatic indexing recommendation tool: CedarAdvisor. It collects workload automatically from logs and counts query frequency. First, it generates a candidate index for each query and then evaluates the respective revenue and cost of these candidates. Finally, it generates an indexing recommendation for the complete workload through a dynamic programming algorithm. We verify the effectiveness of the tool through experiments on the distributed database.

Key words: database, workload, index recommendation

中图分类号: