中国综合性科技类核心期刊(北大核心)

中国科学引文数据库来源期刊(CSCD)

美国《化学文摘》(CA)收录

美国《数学评论》(MR)收录

俄罗斯《文摘杂志》收录

留言板

尊敬的读者、作者、审稿人, 关于本刊的投稿、审稿、编辑和出版的任何问题, 您可以本页添加留言。我们将尽快给您答复。谢谢您的支持!

姓名
邮箱
手机号码
标题
留言内容
验证码

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

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

阳文灿, 胡卉芪, 段惠超, 胡耀艺, 钱卫宁. CedarAdvisor: 负载自适应的自动化索引推荐工具[J]. 华东师范大学学报(自然科学版), 2020, (6): 52-62. doi: 10.3969/j.issn.1000-5641.201921013
引用本文: 阳文灿, 胡卉芪, 段惠超, 胡耀艺, 钱卫宁. CedarAdvisor: 负载自适应的自动化索引推荐工具[J]. 华东师范大学学报(自然科学版), 2020, (6): 52-62. doi: 10.3969/j.issn.1000-5641.201921013
YANG Wencan, HU Huiqi, DUAN Huichao, HU Yaoyi, QIAN Weining. CedarAdvisor: A load-adaptive automatic indexing recommendation tool[J]. Journal of East China Normal University (Natural Sciences), 2020, (6): 52-62. doi: 10.3969/j.issn.1000-5641.201921013
Citation: YANG Wencan, HU Huiqi, DUAN Huichao, HU Yaoyi, QIAN Weining. CedarAdvisor: A load-adaptive automatic indexing recommendation tool[J]. Journal of East China Normal University (Natural Sciences), 2020, (6): 52-62. doi: 10.3969/j.issn.1000-5641.201921013

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

doi: 10.3969/j.issn.1000-5641.201921013
基金项目: 国家自然科学基金青年科学基金(61702189)
详细信息
    通讯作者:

    胡卉芪, 男, 博士, 副教授, 主要研究方向为数据库系统、分布式系统. E-mail: hqhu@dase.ecnu.edu.cn

  • 中图分类号: TP392

CedarAdvisor: A load-adaptive automatic indexing recommendation tool

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

    Fig.  1  Main execution process of CedarAdvisor

    图  2  单条查询使用推荐索引效果

    Fig.  2  Recommended index effect for single query

    图  3  索引空间大小对执行效率的影响

    Fig.  3  The effect of index space size on execution efficiency

    表  1  负载组成

    Tab.  1  Workload composition

    负载组成频率/次负载组成频率/次
    Query3 2 Query17 1
    Query3–1 2 Query17–1 2
    Query3–2 1 Query17–2 1
    Query7 1 Query19 1
    Query7–1 4 Query19–1 1
    Query7–2 2 Query19–2 2
    Query15 2 Query29 1
    Query15–1 1 Query29–1 2
    Query15–2 1 Query29–2 3
    总计 30
    下载: 导出CSV

    表  2  负载候选索引

    Tab.  2  Workload candidate indexes

    索引所在表推荐索引
    date_dim create index idx on date_dim(d_year,d_qoy)
    date_dim create index idx on date_dim(d_quarter_name)
    date_dim create index idx on date_dim(d_moy,d_year)
    date_dim create index idx on date_dim(d_year,d_moy)
    catalog_sales create index idx on catalog_sales(cs_sold_date_sk)
    item create index idx on item(i_manager_id)
    item create index idx on item(i_manufact_id)
    store_sales create index idx on store_sales(ss_sold_date_sk)
    customer_demographics create index idx on customer_demographics(cd_education_status,cd_marital_status,cd_gender)
    customer_demographics create index idx on customer_demographics(cd_purchase_estimate)
    下载: 导出CSV
  • [1] GANTZ J, REINSEL D. The digital universe in 2020: Big data, bigger digital shadows, and biggest growth in the far east [EB/OL]. [2020-05-22]. https://www.emc.com/leadership/digital-universe/2012iview/index.htm.
    [2] KAO K F, LIAO I E. An index selection method without repeated optimizer estimations [J]. Information Sciences, 2009, 179(13): 2263-2272. doi:  10.1016/j.ins.2009.02.009
    [3] CHAUDHURI S, NARASAYYA V. AutoAdmin “what-if” index analysis utility [C]//Acm Sigmod International Conference on Management of Data. ACM, 1998.
    [4] CHAUDHURI S, NARASAYYA V R. Microsoft index tuning wizard for SQL server 7.0 [C]//Proceedings ACM SIGMOD International Conference on Management of Data, 1998.
    [5] AGRAWAL S, CHAUDHURI S, KOLLAR L, et al. Database tuning advisor for microsoft SQL server 2005: Demo [C]// Acm Sigmod International Conference on Management of Data. ELSEVIER, 2005.
    [6] CHAUDHURI S, NARASAYYA V. An Efficient, Cost-Driven Index Selection Tool for Microsoft SQL Server [C]// International Conference on Very Large Data Bases. [S.l.]: Morgan Kaufmann Publishers Inc, 1997.
    [7] LOHMAN G M, LIGHTSTONE S. SMART: Making DB2 (more) autonomic [C]// Proc of the 28th International Conference on Very Large Data Bases. [S.l.]: VLDB Endowment, 2002: 877-879.
    [8] VALENTIN G, ZULIANI M, ZILIO D C, et al. DB2 advisor: An optimizer smart enough to recommend its own indexes [C]// Proceedings of 16th International Conference on Data Engineering (Cat. No.00CB37073). IEEE, 2002.
    [9] ZILIO D C, RAO J, LIGHTSTONE S, et al. DB2 design advisor: Integrated automatic physical database design [C]//Proceedings of the 30th Annual International Conference on Very Large Data Bases, 2004: 1087-1097.
    [10] XIAOMI. SOAR [EB/OL]. [2020-05-22]. https://github.com/XiaoMi/soar.
    [11] Data Science & Engineering, East China Normal University. CEDAR. [EB/OL]. [2020-05-22]. https://github.com/daseECNU/Cedar/.
    [12] LAHDENMÄKI T, LEACH M. Relational Database Index Design and the Optimizer [M]. [S.l.]: John Wiley & Sons, Inc, 2005: 47.
    [13] The PostgreSQL Global Development Group. PostgreSQL [EB/OL]. [2020-05-22]. https://www.postgresql.org/.
    [14] TPC. TPC-DS [EB/OL]. [2020-05-22]. http://www.tpc.org/tpcds/.
  • 加载中
图(3) / 表(2)
计量
  • 文章访问数:  150
  • HTML全文浏览量:  112
  • PDF下载量:  20
  • 被引次数: 0
出版历程
  • 收稿日期:  2019-08-26
  • 刊出日期:  2020-11-25

目录

    /

    返回文章
    返回