Abstract
Indexes are essential for increasing query speed. Traditional databases require database administrators to manually tune indexes based on knowledge and their experience. In recent years, AI techniques have been successfully applied to many areas including automatic index recommendation. Reinforcement Learning (RL) methods such as Deep Q-Network (DQN) can find better indexes than traditional methods, but still suffer from the huge action space. Previous RL methods tried to solve it by pre-narrowing action space to several candidate indexes, which may omit some useful indexes. This paper focuses on offline Index Selection Problem (ISP) and tries to solve the problem via invalid action mask in a tree-structured action space. First, we use Double DQN and Dueling DQN to replace traditional DQN to get better estimation of Q-values. Then we propose a novel index recommendation approach DQN-AMTAS that collects all possible indexes in a tree and recommends multi-column indexes from left to right via invalid action mask based on the Leftmost Prefix Rule. We conduct extensive experiments on TPC-H and TPC-DS datasets. The experimental results show the superiority of our proposed DQN-AMTAS compared with state-of-the-art index recommendation algorithms.
This is a preview of subscription content, log in via an institution.
Buying options
Tax calculation will be finalised at checkout
Purchases are for personal use only
Learn about institutional subscriptionsReferences
Basu, D., et al.: Regularized cost-model oblivious database tuning with reinforcement learning. In: Hameurlain, A., Küng, J., Wagner, R., Chen, Q. (eds.) Transactions on Large-Scale Data- and Knowledge-Centered Systems XXVIII. LNCS, vol. 9940, pp. 96–132. Springer, Heidelberg (2016). https://doi.org/10.1007/978-3-662-53455-7_5
Bruno, N., Chaudhuri, S.: Automatic physical database tuning: a relaxation-based approach. In: Proceedings of the 2005 ACM SIGMOD International Conference on Management of Data, pp. 227–238 (2005). https://doi.org/10.1145/1066157.1066184
Chaudhuri, S., Narasayya, V.: Anytime algorithm of database tuning advisor for microsoft sql server (2020)
Choenni, S., Blanken, H., Chang, T.: Index selection in relational databases. In: Proceedings of ICCI 1993: 5th International Conference on Computing and Information, pp. 491–496. IEEE (1993). https://doi.org/10.1109/ICCI.1993.315323
Dash, D., Polyzotis, N., Ailamaki, A.: Cophy: a scalable, portable, and interactive index advisor for large workloads. arXiv preprint arXiv:1104.3214 (2011). https://doi.org/10.48550/arXiv.1104.3214
Fotouhi, F., Galarce, C.E.: Genetic algorithms and the search for optimal database index selection. In: Sherwani, N.A., de Doncker, E., Kapenga, J.A. (eds.) Great Lakes CS 1989. LNCS, vol. 507, pp. 249–255. Springer, New York (1991). https://doi.org/10.1007/BFb0038500
Huang, S., Ontañón, S.: A closer look at invalid action masking in policy gradient algorithms. arXiv preprint arXiv:2006.14171 (2020). https://doi.org/10.48550/arXiv.2006.14171
Ip, M.Y.L., Saxton, L.V., Raghavan, V.V.: On the selection of an optimal set of indexes. IEEE Trans. Softw. Eng. 2, 135–143 (1983). https://doi.org/10.1109/TSE.1983.236458
Kossmann, J., Halfpap, S., Jankrift, M., Schlosser, R.: Magic mirror in my hand, which is the best in the land? an experimental evaluation of index selection algorithms. In: Proceedings of the VLDB Endowment, vol. 13, no. 12, pp. 2382–2395 (2020). https://doi.org/10.14778/3407790.3407832
Lan, H., Bao, Z., Peng, Y.: An index advisor using deep reinforcement learning. In: Proceedings of the 29th ACM International Conference on Information & Knowledge Management, pp. 2105–2108 (2020). https://doi.org/10.1145/3340531.3412106
Mnih, V., et al.: Playing atari with deep reinforcement learning. arXiv preprint arXiv:1312.5602 (2013). https://doi.org/10.48550/arXiv.1312.5602
Papadomanolakis, S., Ailamaki, A.: An integer linear programming approach to database design. In: 2007 IEEE 23rd International Conference on Data Engineering Workshop, pp. 442–449. IEEE (2007). https://doi.org/10.1109/ICDEW.2007.4401027
Piatetsky-Shapiro, G.: The optimal selection of secondary indices is np-complete. ACM SIGMOD Rec. 13(2), 72–75 (1983). https://doi.org/10.1145/984523.984530
Schlosser, R., Kossmann, J., Boissier, M.: Efficient scalable multi-attribute index selection using recursive strategies. In: 2019 IEEE 35th International Conference on Data Engineering (ICDE), pp. 1238–1249. IEEE (2019). https://doi.org/10.1109/ICDE.2019.00113
Sharma, A., Schuhknecht, F.M., Dittrich, J.: The case for automatic database administration using deep reinforcement learning. arXiv preprint arXiv:1801.05643 (2018). https://doi.org/10.48550/arXiv.1801.05643
Valentin, G., Zuliani, M., Zilio, D.C., Lohman, G., Skelley, A.: Db2 advisor: an optimizer smart enough to recommend its own indexes. In: Proceedings of 16th International Conference on Data Engineering (Cat. No. 00CB37073), pp. 101–110. IEEE (2000). https://doi.org/10.1109/ICDE.2000.839397
Van Hasselt, H., Guez, A., Silver, D.: Deep reinforcement learning with double q-learning. In: Proceedings of the AAAI Conference on Artificial Intelligence, vol. 30 (2016). https://doi.org/10.1609/aaai.v30i1.10295
Wang, Z., Schaul, T., Hessel, M., Hasselt, H., Lanctot, M., Freitas, N.: Dueling network architectures for deep reinforcement learning. In: International Conference on Machine Learning, pp. 1995–2003. PMLR (2016)
Acknowledgements
This study was supported by the Natural Science Foundation of Shaanxi Province of China (Grant No.2021JM068).
Author information
Authors and Affiliations
Corresponding author
Editor information
Editors and Affiliations
Rights and permissions
Copyright information
© 2022 The Author(s), under exclusive license to Springer Nature Switzerland AG
About this paper
Cite this paper
Wu, Y., Zhang, Y., Li, N. (2022). Index Advisor via DQN with Invalid Action Mask in Tree-Structured Action Space. In: Chen, W., Yao, L., Cai, T., Pan, S., Shen, T., Li, X. (eds) Advanced Data Mining and Applications. ADMA 2022. Lecture Notes in Computer Science(), vol 13726. Springer, Cham. https://doi.org/10.1007/978-3-031-22137-8_32
Download citation
DOI: https://doi.org/10.1007/978-3-031-22137-8_32
Published:
Publisher Name: Springer, Cham
Print ISBN: 978-3-031-22136-1
Online ISBN: 978-3-031-22137-8
eBook Packages: Computer ScienceComputer Science (R0)