Intelligent Index Tuning Approach for Relational Databases
Author:
Affiliation:

Fund Project:

National Key Research and Development Program of China (2018YFB1700404); National Natural Science Foundation of China (U1736104, 61572122, 61532021); Fundamental Research Funds for the Central Universities (N171602003); CCF-Huawei Database System Innovation Research Plan (CCF-Huawei DBIR2019009B)

  • Article
  • | |
  • Metrics
  • |
  • Reference [27]
  • |
  • Related [20]
  • | | |
  • Comments
    Abstract:

    Indexing is one of the most effective techniques for relational databases to achieve fast queryprocessing. The intelligent index tuning technique can effectively adjust the index of the database instance to obtain efficient query performance. Most of the existing methods utilize the query log to generate candidate indices, and then use the artificially designed models to select indices, thereby the indices are adjusted. However, the candidate indices generated from the query log may not exist in the database instance, so they cannot precisely estimate the effects of such indices on the query processing. This study first designs and implements an intelligent index tuning system for the relational database. Secondly, it proposes a learning-based method to model the effects of indices for query processing, accordingly, the query optimization effect of an index can be accurately estimated when selecting optimized indices. Then, an efficient optimal index selection algorithm is designed to select a set of indices with the maximal utility from candidate indices, which satisfy the space threshold. Finally, experiments are conducted to test the performance of the proposed system in different settings. The experimental results show that the proposed technique can effectively adjust the index and achieve a significant improvement in query performance for a relational database.

    Reference
    [1] Silberschatz A, Korth HF, Sudarshan S. Database System Concepts. 4th ed., New York:McGraw-Hill, 1997.
    [2] Schwartz B, Zaitsev P, Tkachenko V. High Performance MySQL:Optimization, Backups, and Replication. 3rd ed., O'Reilly Media, Inc., 2012.
    [3] Zilio DC, Rao J, Lightstone S, et al. DB2 design advisor:Integrated automatic physical database design. In:Proc. of the 30th VLDB Conf. 2004. 1087-1097.
    [4] Finkelstein S, Schkolnick M, Tiberio P. Physical database design for relational databases. ACM Trans. on Database Systems, 1988, 13(1):91-128.
    [5] Ameri P, Meyer J, Streit A. On a new approach to the index selection problem using mining algorithms. In:Proc. of the 2015 IEEE Int'l Conf. on Big Data. IEEE, 2015. 2801-2810.
    [6] Zhang N, Haas PJ, Josifovski V, et al. Statistical learning techniques for costing XML queries. In:Proc. of the 31st Int'l Conf. Very Large Data Bases. 2005. 289-300.
    [7] Ahmad M, Duan S, Aboulnaga A, et al. Predicting completion times of batch query workloads using interaction-aware models and simulation. In:Proc. of the 14th Int'l Conf. on Extending Database Technology. ACM, 2011. 449-460.
    [8] Akdere M, Çetintemel U, Riondato M, et al. Learning-Based query performance modeling and prediction. In:Proc. of the 28th Int'l Conf. on Data Engineering. IEEE, 2012. 390-401.
    [9] Li J, König AC, Narasayya V, et al. Robust estimation of resource consumption for SQL queries using statistical techniques. Proc. of the VLDB Endowment, 2012,5(11):1555-1566.
    [10] Lahdenmaki T, Leach M. Relational Database Index Design and the Optimizers:DB2, Oracle, SQL Server. John Wiley & Sons, 2005.
    [11] Chaudhuri S, Narasayya VR. An efficient, cost-driven index selection tool for Microsoft SQL server. In:Proc. of the 23rd VLDB Conf. 1997. 146-155.
    [12] Bruno N, Chaudhuri S. An online approach to physical design tuning. In:Proc. of the 23rd Int'l Conf. on Data Engineering. IEEE, 2007. 826-835.
    [13] Schnaitter K, Abiteboul S, Milo T, et al. Colt:Continuous on-line tuning. In:Proc. of the 2006 ACM Int'l Conf. on Management of Data. ACM, 2006. 793-795.
    [14] Sattler KU, Geist I, Schallehn E. Quiet:Continuous query-driven index tuning. In:Proc. of the 2003 VLDB Conf. 2003. 1129-1132.
    [15] Petraki E, Idreos S, Manegold S. Holistic indexing in main-memory column-stores. In:Proc. of the 2015 ACM Int'l Conf. on Management of Data. ACM, 2015. 1153-1166.
    [16] Idreos S, Kersten ML, Manegold S. Database cracking. In:Proc. of the Conf. on Innovative Data Systems Research, Vol.7. 2007. 68-78.
    [17] Voigt H, Kissinger T, Lehner W. Smix:Self-managing indexes for dynamic workloads. In:Proc. of the 25th Int'l Conf. on Scientific and Statistical Database Management. ACM, 2013.
    [18] Goel S, Langford J, Strehl AL. Predictive indexing for fast search. In:Proc. of the Advances in Neural Information Processing Systems. 2009. 505-512.
    [19] Ma L, Van Aken D, Hefny A, et al. Query-based workload forecasting for self-driving database management systems. In:Proc. of the 2018 Int'l Conf. on Management of Data. ACM, 2018. 631-645.
    [20] Kesarwani M, Kaul A, Singh G, et al. Collusion-resistant processing of SQL range predicates. Data Science and Engineering, 2018, 3(4):323-340.
    [21] Oracle. MySQL 8.0 Reference Manual. MySQL Press, 2018.
    [22] Harris D, Harris S. Digital Design and Computer Architecture. Morgan Kaufmann Publishers, 2010.
    [23] Freedman DA. Statistical Models:Theory and Practice. Cambridge University Press, 2009.
    [24] Chen T. Introduction to Boosted Trees. University of Washington Computer Science, 2014.
    [25] Lecun Y, Bengio Y, Hinton G. Deep learning. Nature, 2015,521(7553):436.
    [26] Martello S. Knapsack Problems:Algorithms and Computer Implementations. Wiley-InterscienceSeries in Discrete Mathematics and Optimization, 1990.
    [27] Witten IH, Frank E, Hall MA, Pal CJ. Data Mining:Practical Machine Learning Tools and Techniques. Morgan Kaufmann Publishers, 2016.
    Cited by
    Comments
    Comments
    分享到微博
    Submit
Get Citation

邱涛,王斌,舒昭维,赵智博,宋子文,钟延辉.面向关系数据库的智能索引调优方法.软件学报,2020,31(3):634-647

Copy
Share
Article Metrics
  • Abstract:3361
  • PDF: 7780
  • HTML: 3502
  • Cited by: 0
History
  • Received:July 20,2019
  • Revised:September 10,2019
  • Online: January 10,2020
  • Published: March 06,2020
You are the first2033282Visitors
Copyright: Institute of Software, Chinese Academy of Sciences Beijing ICP No. 05046678-4
Address:4# South Fourth Street, Zhong Guan Cun, Beijing 100190,Postal Code:100190
Phone:010-62562563 Fax:010-62562533 Email:jos@iscas.ac.cn
Technical Support:Beijing Qinyun Technology Development Co., Ltd.

Beijing Public Network Security No. 11040202500063