Online Index Recommendation for Slow Queries

Database autonomy service (DAS) is a platform that provides assistance to database maintainers or administrators in managing a large number of database instances in major internet companies. An important task of DAS is to find missing indexes to improve the performance of slow queries reported from its managed online database instances. Traditional database systems provide the “what-if” function or the hypothetical index technique. Index metadata is modified to simulate the benefits of indexes for queries without creating physical index files. Decades of research have led to plenty of ideas for index recommendation through the use of the “what-if” function and different search strategies. However, the popular open-source database system MySQL, used by most internet companies, has not provided the “what-if” function. In Meituan, tens of thousands of MySQL instances have been deployed across many business lines. Consequently, the DAS platform has accumulated lots of index creation samples. In this paper, we introduce index learner (IdxL), designed to learn index creation knowledge from these informative index data. IdxL resolves the problem of index recommendation by formulating it into an end-to-end supervised learning problem. Given a slow query, IdxL uses learned index creation knowledge to directly predict the missing indexes. Experimental results demonstrate: (1) IdxL is superior to the state-of-the-art index recommendation methods, especially when the error in cost estimation was propagated to the search in candidate index space, and (2) in particular, IdxL achieves up to 97% performance gain over a state-of-the-art method relying on the optimizer's cost estimation in the Meituan-specific index recommendation scenario. Finally, we present the applied results of IdxL in the Meituan DAS platform, demonstrating its ability to transfer index creation knowledge from certain databases to others.