Automatic index recommendation for improved query performance

Abstract

Systems and methods described herein relate to automatic index recommendations for improved database query performance. A candidate index is identified. The candidate index is associated with a database query classified as a slow query. A feature vector is generated for each candidate index to represent declaration features and statistical features associated with the candidate index. The feature vectors are provided to one or more machine learning models to obtain index recommendation values for each candidate index. An index recommendation is presented at a user device. The index recommendation identifies a first index of the candidate indexes based at least in part on an index recommendation value obtained for the first index. A user input indicating a user's selection of the first index is received. In response to user input, the database schema is updated to include the first index.

Description

Technical Field

[0001] The subject matter disclosed herein generally relates to using indexes to improve database query performance. More specifically but not exclusively, the subject matter relates to systems and methods suitable for providing automatic index recommendations. Background Art

[0002] Indexes are important tools for addressing slow queries in database systems. For example, if the execution time of a database query exceeds a predetermined threshold (e.g., the database query takes longer than 1 second to execute) or the database query is flagged for attention based on user complaints related to its execution time, the database query can be classified as a "slow query".

[0003] Although creating indexes can reduce the number of slow queries and thus improve database query performance, indexes should be carefully selected because they can introduce additional overhead in the database system. Identifying suitable indexes can be a difficult or time-consuming task, especially in large dynamic database systems where multiple indexes can potentially be implemented to attempt to address slow queries. Additionally, once a suitable index is identified, evaluating its performance and integrating it into the database schema can pose technical challenges. Summary of the Invention

[0004] According to an example embodiment of the present disclosure, a system is provided, including: at least one memory storing instructions; and one or more processors configured by the instructions to perform operations, the operations including: identifying a plurality of candidate indexes associated with a database query classified as a slow query; for each candidate index of the plurality of candidate indexes, generating a feature vector representing the statement features and statistical features associated with the candidate index; providing the feature vector to one or more machine learning models to obtain an index recommendation value for each candidate index of the plurality of candidate indexes; causing an index recommendation identifying a first index of the plurality of candidate indexes to be presented at a user device, the first index being identified in the index recommendation based at least in part on the index recommendation value obtained for the first index; receiving user input indicating the user's selection of the first index; and in response to receiving the user input, automatically updating the database schema to include the first index.

[0005] According to an example embodiment of the present disclosure, the operations further include: detecting that the query execution time of a database query exceeds a threshold; and in response to detecting that the query execution time associated with the database query exceeds the threshold: designating the database query as a slow query, and automatically triggering obtaining an index recommendation value.

[0006] According to an example embodiment of the present disclosure, wherein the database query is one of a plurality of database queries, the operation further includes: using an index recommendation application to monitor the query execution time of each of the plurality of database queries, and the database query is specified from the plurality of database queries.

[0007] According to an example embodiment of the present disclosure, the operation further includes, before presenting the index recommendation at the user device: evaluating the performance of the first index according to a predetermined performance metric; and selecting the first index to be included in the index recommendation based on the performance of the first index.

[0008] According to an example embodiment of the present disclosure, wherein evaluating the performance of the first index includes: determining a first query execution time of the database query when executed for a data subset from the database associated with the database query without applying the first index; determining a second query execution time of the database query when executed for the data subset from the database by applying the first index; and comparing the first query execution time with the second query execution time.

[0009] According to an example embodiment of the present disclosure, wherein causing the index recommendation to be presented at the user device includes causing a user interface to be generated, and the user interface presents the index recommendation together with an indication of the performance of the first index.

[0010] According to an example embodiment of the present disclosure, each of the plurality of candidate indexes has one or more candidate index columns, and the statement feature indicates the position of each of the one or more candidate index columns within the database query.

[0011] According to an example embodiment of the present disclosure, for each of the one or more candidate index columns, the position is encoded in a feature vector by an encoding scheme that indicates the presence or absence of the corresponding query condition involving the candidate index column.

[0012] According to an example embodiment of the present disclosure, each of the plurality of candidate indexes has one or more candidate index columns, and the statistical feature includes at least one of the following: at least one selectivity value associated with the database query, at least one base value associated with the database query, the number of null values in each of the one or more candidate index columns, or the number of rows associated with each of the one or more candidate index columns.

[0013] According to an example embodiment of the present disclosure, at least one candidate index among a plurality of candidate indexes is a multi-column candidate index, and a feature vector of the multi-column candidate index is generated by: generating column-specific values indicating characteristics of respective columns in the multi-column candidate index; generating multi-column values indicating combined characteristics of the respective columns in the multi-column candidate index; and combining the column-specific values and the multi-column values.

[0014] According to an example embodiment of the present disclosure, the plurality of candidate indexes includes a first subset and a second subset, the first subset includes one or more single-column candidate indexes, and the second subset includes one or more multi-column candidate indexes.

[0015] According to an example embodiment of the present disclosure, one or more machine learning models include a first machine learning model and at least one second machine learning model, the first machine learning model is trained to process each feature vector of one or more single-column candidate indexes, and the at least one second machine learning model is trained to process each feature vector of one or more multi-column candidate indexes.

[0016] According to an example embodiment of the present disclosure, the first index is selected from the first subset, and the index recommendation further identifies a second index among the plurality of candidate indexes, the second index is selected from the second subset, and is identified in the index recommendation at least in part based on an index recommendation value obtained for the second index.

[0017] According to an example embodiment of the present disclosure, a dataset including sample feature vectors is used to train each of one or more machine learning models, and each sample feature vector is labeled to indicate whether a sample index corresponding to the sample feature vector exists in a sample database.

[0018] According to an example embodiment of the present disclosure, a method is provided, including: identifying, by one or more computing devices, a plurality of candidate indexes associated with a database query classified as a slow query; generating, by one or more computing devices for each candidate index among the plurality of candidate indexes, a feature vector representing statement features and statistical features associated with the candidate index; providing, by one or more computing devices, the feature vectors to one or more machine learning models to obtain an index recommendation value for each candidate index among the plurality of candidate indexes; causing, by one or more computing devices, an index recommendation to be presented that identifies a first index among the plurality of candidate indexes, the first index being identified in the index recommendation at least in part based on the index recommendation value obtained for the first index; receiving, by one or more computing devices, user input indicating a selection of the first index by a user; and in response to receiving the user input, automatically updating, by one or more computing devices, a database schema to include the first index.

[0019] According to an example embodiment of the present disclosure, it further includes: the query execution time of a database query detected by one or more computing devices exceeds a threshold; and in response to detecting that the query execution time associated with the database query exceeds the threshold: designating the database query as a slow query by one or more computing devices, and automatically triggering obtaining index recommendation values by one or more computing devices.

[0020] According to an example embodiment of the present disclosure, it further includes, before presenting the index recommendation: evaluating the performance of a first index by one or more computing devices according to a predetermined performance metric; and selecting the first index to be included in the index recommendation based on the performance of the first index by one or more computing devices.

[0021] According to an example embodiment of the present disclosure, a non-transitory computer-readable medium storing instructions is provided, and the instructions, when executed by one or more processors, cause the one or more processors to perform operations, the operations including: identifying a plurality of candidate indexes associated with a database query classified as a slow query; for each candidate index in the plurality of candidate indexes, generating a feature vector representing the statement features and statistical features associated with the candidate index; providing the feature vector of each candidate index in the plurality of candidate indexes to a machine learning model to obtain an index recommendation value for the candidate index; causing an index recommendation identifying a first index among the plurality of candidate indexes to be presented at a user device, the first index being identified in the index recommendation at least partially based on the index recommendation value obtained for the first index; receiving user input indicating the user's selection of the first index; and in response to receiving the user input, automatically updating the database schema to include the first index.

[0022] According to an example embodiment of the present disclosure, the operations further include: detecting that the query execution time of a database query exceeds a threshold; and in response to detecting that the query execution time associated with the database query exceeds the threshold: designating the database query as a slow query, and automatically triggering obtaining index recommendation values.

[0023] According to an example embodiment of the present disclosure, the operations further include, before presenting the index recommendation at a user device: evaluating the performance of a first index according to a predetermined performance metric; and selecting the first index to be included in the index recommendation based on the performance of the first index. BRIEF DESCRIPTION OF THE DRAWINGS

[0024] For purposes of illustration and not limitation, some examples are shown in the figures of the drawings. In the drawings, which are not necessarily to scale, the same numbers may describe similar components in different views or examples. To more easily identify the discussion of any particular element or action, one or more of the most significant digits in the reference numerals refer to the figure number in which the element was first introduced.

[0025] Figure 1Is a schematic diagram of a network environment including a database management system and an index recommendation system according to some examples.

[0026] Figure 2 Is a block diagram of certain components of a database management system according to some examples.

[0027] Figure 3 Is a block diagram of certain components of an index recommendation system according to some examples.

[0028] Figure 4 Is a flowchart showing operations of a method suitable for training a machine learning model to generate index recommendation values according to some examples.

[0029] Figure 5 Shows a database query and multiple tables according to some examples to illustrate aspects of index vectorization that are performed to generate training data.

[0030] Figure 6 Is a flowchart showing operations of a method suitable for automatically generating index recommendations to address slow queries according to some examples.

[0031] Figure 7 Is a schematic diagram of generating index recommendations according to some examples, where the index recommendation system employs three different machine learning models to generate index recommendation values.

[0032] Figure 8 Schematically shows the training and use of a machine learning program according to some examples.

[0033] Figure 9 Is a block diagram showing a software architecture for a computing device according to some examples.

[0034] Figure 10 Is a block diagram of a machine in the form of a computer system, in which instructions can be executed to cause the machine to perform any one or more of the methods discussed herein. Detailed Description

[0035] Establishing appropriate indexes is an effective way to reduce the occurrence of slow queries in a database system. The systems and methods described herein enable the automatic generation of index recommendations for improved database query performance. The example techniques described herein allow for the automatic analysis of slow queries, the generation of appropriate index recommendations, and the integration of the selected indexes into the database schema, thus providing a targeted technical solution for performance improvement.

[0036] In some examples, slow queries are analyzed by using one or more machine learning models. Vectorized data representing features of an existing or suitable index can be used to train one or more machine learning models. After training, the values generated by the machine learning models during inference can be used to generate an index build plan that may improve database query performance.

[0037] Example methods can include identifying a plurality of candidate indexes associated with a database query classified as a slow query. The terms "database query" and "query" are used interchangeably in the context of the present disclosure.

[0038] Before identifying the plurality of candidate indexes, slow queries can be detected or classified. For example, the system can detect that the query execution time of a database query exceeds a threshold, and in response to detecting that the query execution time associated with the database query exceeds the threshold, designate the database query as a slow query (e.g., by marking a record of the slow query in the database). In some examples, designating a database query as a slow query automatically triggers further operations, such as obtaining index recommendation values for candidate indexes, as described below.

[0039] The database queries classified as slow queries can be among a plurality of database queries monitored by an index recommendation application. For example, the index recommendation application can monitor the query execution time of each database query among the plurality of database queries to determine whether one or more of them are marked as slow queries. In some examples, the index recommendation application is implemented as a microservice that automatically monitors database queries and triggers the generation of index recommendations, where the database queries are found to meet one or more requirements associated with slow queries.

[0040] In some examples, a feature vector is generated for each candidate index associated with a slow query. The feature vector can represent one or both of statement features and statistical features associated with the candidate index. For example, the statement features can indicate the position of each of one or more candidate index columns within the database query. The statement features can encode such positions through a coding scheme indicating the presence or absence of the corresponding query conditions involving the candidate index columns (e.g., using one - hot encoding).

[0041] The statistical features in the feature vector may include at least one of the following: a selectivity value associated with a database query, a base value associated with a database query, the number of null values in each candidate index column among one or more candidate index columns of a candidate index, or the number of rows associated with each candidate index column among one or more candidate index columns. The method may include parsing the database query (e.g., using a Structured Query Language (SQL) parser) to extract information from the database query. A syntactic analysis operation may be performed on the information to obtain statement features. A statistical analysis may be performed on the information to obtain statistical features.

[0042] The method may include providing the feature vector generated for a candidate index to one or more machine learning models to obtain an index recommendation value for each candidate index. The index recommendation value may include, for example, a likelihood score. The system may automatically present the index recommendation at the user device. The index recommendation may identify one or more indexes from multiple multi-column candidate indexes for the user to select, approve, or modify.

[0043] In some examples, the index recommendation identifies at least a first index among the multiple candidate indexes at least in part based on the index recommendation value obtained for the first index. The method may include receiving a user selection of the first index. In response to receiving the user input, the database schema may be automatically updated to include the first index.

[0044] The method may further include, before presenting the index recommendation at the user device, evaluating the performance of the first index according to a predetermined performance metric. The performance metric may relate, for example, to an improvement or expected improvement in the execution time associated with the implementation of the first index.

[0045] Evaluating the performance of the first index may include determining a first query execution time of the database query without applying the first index, determining a second query execution time of the database query by applying the first index, and comparing the first query execution time with the second query execution time. In some examples, to evaluate the performance of a candidate index based on a performance metric, the candidate index is executed against a data subset of the data from the database associated with the database query. Such a data subset is referred to herein as a "sample database". Thus, the first index may be selected at least in part based on the performance of the first index.

[0046] In some examples, the system causes an index recommendation to be presented within a user interface on a user device, the user interface presenting the index recommendation and an indication of the performance of a first index. In this way, automatically generated options can be provided to the user to select an index while enabling the user to view the expected improvement associated with the selection of the index. For example, this can allow the user to make a more informed decision by evaluating the expected improvement against other factors (such as increased overhead) before allowing the integration of the selected index.

[0047] The plurality of candidate indexes can include different types of indexes. For example, in a columnar storage context, at least one of the plurality of candidate indexes can be a multi-column candidate index (e.g., a two-column index or a three-column index), in which case, a feature vector for the multi-column candidate index can be generated by generating column-specific values indicative of the characteristics of the respective columns in the multi-column candidate index, generating multi-column values indicative of the combined characteristics of the respective columns in the multi-column candidate index, and combining the column-specific values and the multi-column values.

[0048] In some examples, the plurality of candidate indexes includes a first subset and a second subset, where the first subset includes one or more single-column candidate indexes and the second subset includes one or more multi-column candidate indexes. As a specific non-limiting example, the plurality of candidate indexes can include a first subset of one or more single-column candidate indexes, a second subset of one or more two-column candidate indexes, and a third subset of one or more three-column candidate indexes.

[0049] One or more machine learning models can include separate machine learning models trained to process the feature vectors of the respective types of candidate indexes. For example, one or more machine learning models can include a first machine learning model trained to process each feature vector of one or more single-column candidate indexes and at least one second machine learning model trained to process each feature vector of one or more multi-column candidate indexes. As a specific non-limiting example, the system can execute a single-column model to process the feature vectors of single-column candidate indexes, execute a two-column model to process the feature vectors of two-column candidate indexes, and execute a three-column model to process the feature vectors of three-column candidate indexes.

[0050] In some examples, the system can automatically recommend at least one candidate index of each type of candidate index. For example, in a case where the candidate indexes include a first subset, a second subset, and a third subset, the system can automatically recommend the highest-scoring candidate index from each subset based on their respective index recommendation values.

[0051] A dataset including sample feature vectors can be used to train each machine learning model, where each sample feature vector is labeled to indicate whether the sample index corresponding to the sample feature vector exists in the sample database. Thus, a positive label can indicate, for example, a desired index or an index associated with performance improvement. The machine learning model can be, for example, a binary classification model trained to predict the existence or non-existence of an index based on the feature vector. In some examples, the machine learning model can output a predicted label and a confidence score (as an example of an index recommendation value).

[0052] The examples described herein can solve or mitigate one or more technical problems. At least some technical problems of a database management system may stem from or involve a reliance on manual index management by a database administrator or developer. Manual index management can include, for example, manually analyzing query patterns or data access paths and manually creating indexes to optimize query performance. These processes are not only time-consuming but also prone to human error, especially in complex databases with large amounts of data and high query diversity.

[0053] Slow queries may involve multiple columns of a database table, and developers may need to analyze and test various possible indexes (e.g., various single-column options and multi-column options) to determine which index may solve the problem. In large, complex, or interconnected database tables, it may be difficult or impractical for developers to identify the best or near-best index without spending excessive time or computational resources.

[0054] Such technical problems can be solved or mitigated by providing an automatic index recommendation system that utilizes machine learning algorithms to efficiently analyze queries and index data. The systems described herein can automatically identify index opportunities and recommend the best or near-best indexes without requiring manual intervention. This can reduce errors and ensure that the database is continuously or periodically optimized for the query workload.

[0055] Technical problems may also stem from inefficient analysis of query performance as part of determining the need for or selecting a suitable index. A database management system may lack the ability to process and analyze large amounts of query execution data in real time, resulting in delayed or suboptimal index tuning. Such inefficiencies can lead to persistent performance bottlenecks as the system may not be able to quickly adapt to changing data access patterns or may overlook subtle but impactful optimization opportunities.

[0056] The examples described herein provide a technical solution to overcome inefficiencies by incorporating one or more performance monitoring components. The performance monitoring components can automatically evaluate query performance or the impact of candidate indexes on query performance. This can allow the system to dynamically recommend and integrate index modifications, thereby increasing database system throughput.

[0057] Index management can also suffer from technical issues when it comes to scalability and adaptability across diverse and evolving data sets. For example, as the volume of data grows and new types of queries are introduced, it can be challenging to scale and tune the index in an efficient manner. For instance, in the context of SQL queries, while indexes can improve the performance of "select" queries, they can introduce overhead for "action" queries because the indexes themselves need to be updated when the data changes. Such problems can gradually get worse as the data set grows or evolves. By providing a technical solution for recommending suitable indexes based on feature vectors that consider various aspects of the query and its performance, more relevant or efficient indexes can be made to emerge. This can in turn lead to a better balance between read and write efficiency (e.g., by avoiding having too many indexes that might slow down "action" queries but still having high-performance queries to speed up "select" queries).

[0058] Another technical challenge can be how to integrate user interactivity and feedback into the index building process while still allowing for substantially automated index recommendation and integration. The examples described herein incorporate user interaction in a seamless or near-seamless manner, reducing the risk of human error or latency in performance improvements while still enabling the user to have control over certain aspects of the process.

[0059] The examples described herein can improve the functionality of a database system by automatically surfacing suitable index recommendations or automatically integrating the selected indexes into the database schema, resulting in improved system performance (e.g., as a result of faster query execution times). When considering the effects in the present disclosure as a whole, one or more of the methods described herein can eliminate the need for certain efforts or resources that would otherwise be involved in a database management system. Computational resources utilized by the system, database, or network can be more efficiently utilized or reduced, e.g., as a result of a reduction in the computational resources required to perform query log analysis or perform near-exhaustive testing of a large number of candidate indexes. Examples of such computational resources can include processor cycles, network traffic, memory usage, data storage capacity, power consumption, and cooling capacity.

[0060] Figure 1 is an illustration of a networked computing environment 100 in which some examples of the present disclosure can be implemented or deployed. One or more servers in the server system 104 provide server-side functionality to networked devices via the network 102, which has an example form of a user device 108 accessed by a user 106. A web client 112 (e.g., a browser) or a programming client 110 (e.g., an "app") can be hosted and executed on the user device 108.

[0061] The Application Programming Interface (API) server 124 and the web server 126 provide corresponding programming and web interfaces to the components of the server system 104. The application server 122 hosts a database management system 128, an index recommendation system 130, and a user interface system 132, each of which includes one or more components, modules, or applications. It should be understood that in other examples, the database management system 128, the index recommendation system 130, and the user interface system 132 may be distributed across multiple application servers.

[0062] The user device 108 can communicate with the application server 122, for example, via the web interface supported by the web server 126 or via the programming interface provided by the API server 124. It should be understood that although Figure 1 only a single user device 108 is shown, in some examples, multiple user devices may be communicatively coupled to the server system 104. Additionally, although certain functions may be described herein as being performed at the user device 108 (e.g., the web client 112 or the programming client 110) or the server system 104, the location of certain functions within the user device 108 or the server system 104 may be a design choice.

[0063] The application server 122 is communicatively coupled to the database server 134 to facilitate access to one or more information repositories, such as the database 136. In some examples, the database 136 includes a storage device that stores information to be processed by the database management system 128, the index recommendation system 130, or the user interface system 132.

[0064] The application server 122 accesses application data (e.g., application data stored by the database server 134) to provide one or more applications or software tools to the user device 108 via the web interface 140 or the app interface 142. As further described below according to examples and with specific reference to Figures 2 - 7 the application server 122 can provide one or more tools or functions for database management, including query processing, using the database management system 128, and can provide one or more tools or functions for automatically generating index recommendations using the index recommendation system 130.

[0065] The database management system 128 is responsible for the storage and organization of data. The database management system 128 can handle multiple tasks, such as data retrieval, insertion, update, and deletion operations. The database management system 128 can be implemented using various database models, including but not limited to relational databases, NoSQL databases, or object-oriented databases. The database management system 128 can ensure data integrity and security while providing efficient access through, for example, Structured Query Language or other database-specific query mechanisms. In some examples, the database management system 128 is designed to handle large amounts of data and support concurrent access by multiple users or systems.

[0066] The index recommendation system 130 is responsible for analyzing database queries and recommending the best or near-optimal index strategies to enhance query performance. Using advanced algorithms, such as machine learning techniques, the index recommendation system 130 can process vectorized data representing the details of slow queries to provide index recommendations. The index recommendation system 130 can work with the database management system 128 to implement or manage the selected indexes, thus playing a role in reducing query latency and improving the overall efficiency of the database management system 128.

[0067] The user interface system 132 enables users, such as user 106 of user device 108, to interact with the database management system 128 or the index recommendation system 130. The user interface system 132 can provide a gateway via the API server 124 or the web server 126 through which database administrators and users can visualize, manage, and manipulate data and system configurations. The user interface system 132 can provide a user interface with an intuitive layout and controls or an instruction interface for technical users (e.g., via the web interface 140 or the app interface 142). For example, the user interface system 132 can display the index recommendations generated by the index recommendation system 130 for user 106 to review or approve. The user interface can include a dedicated section for index management where users can, for example, see active index recommendations, historical decisions, and performance metrics.

[0068] In some examples, user 106 utilizes the database management system 128 to query the database 136. The database management system 128 can implement a query process or a query optimizer to accelerate data retrieval.

[0069] The index recommendation system 130 then operates to generate recommendations for new indexes to integrate with the database 136. In some examples, the index recommendation system 130 acts as Figure 1The microservices within the architecture shown are executed. The index recommendation system 130 can monitor query statistics and detect slow queries (e.g., queries that take more than 1 second to execute). This can automatically trigger the process of generating index recommendations, which are then presented to the user 106 at the user device 108 via the user interface system 132. Additionally, if selected, the index recommendation system 130 can cause the selected index to be automatically integrated into the database schema managed by the database management system 128.

[0070] In some examples, to evaluate the performance of an index or candidate index, the index recommendation system 130 utilizes a sample repository 138. The sample repository 138 is a repository that stores a subset of the data of the actual database involved in the queries (e.g., the database 136). The sample repository 138 can contain representative data samples or synthetic data that mimic the characteristics of the full dataset. For example, the sample repository 138 can provide a "miniature" version of the database 136 that substantially reflects the distribution of values and table relationships. By applying the index to the sample repository 138 and measuring the query performance improvement, the index recommendation system 130 can estimate or predict the potential impact of the index change on the actual database while reducing the computational load associated with performance testing.

[0071] The index recommendation system 130 can work with the machine learning system 144 to perform machine learning model training or inference. In some examples, the machine learning system 144 provides the machine learning pipeline and model for generating index recommendations. The machine learning system 144 can provide various capabilities, such as training models, performing inference, and monitoring performance.

[0072] The machine learning system 144 can provide automated machine learning capabilities that can generate a pipeline customized for a given process or use case. The machine learning system 144 can determine aspects to use, such as appropriate data preprocessing, feature engineering, model algorithms, and hyperparameters. The machine learning system 144 can utilize a training dataset (e.g., a training dataset including samples stored in the database 136) to intelligently build a customized machine learning pipeline. The pipeline provided by the machine learning system 144 can then be used to train, evaluate, and select a model for deployment. This automation can eliminate or reduce the need for time-consuming manual development or refinement of machine learning solutions.

[0073] In some examples, the application server 122 is part of a cloud-based platform provided by a software provider that allows the user 106 to utilize the tools of the database management system 128 and the index recommendation system 130. For example, an account holder such as the user 106 can execute queries, manage data in the database 136, and select indexes to integrate into the database 136.

[0074] One or more of the application server 122, the database server 134, the API server 124, the network server 126, the database management system 128, the index recommendation system 130, and the user interface system 132 may be implemented in whole or in part in a computer system, as described below with reference to Figure 10 that. In some examples, an external application (such as the external application 118 executing on the external server 114 or the external application 120 executing on the external server 116) may communicate with the application server 122 via a programming interface provided by the API server 124. For example, a third - party application may support one or more features or functions on a website or platform hosted by a third - party, or may execute certain methods and provide input or output information to the application server 122 for further processing or publication.

[0075] The network 102 may be any network that enables communication between or among machines, databases, and devices. Thus, the network 102 may be a wired network, a wireless network (e.g., a mobile or cellular network), or any suitable combination thereof. The network 102 may include one or more portions that constitute a private network, a public network (e.g., the Internet), or any suitable combination thereof.

[0076] In some examples, the database 136 is a multi - model database that stores data in main memory rather than secondary memory (such as disk storage devices or solid - state drives). For example, the database management system 128 may provide access to the database 136 with an in - memory database design that is column - oriented. In addition to providing the database 136, the database management system 128 may also enable users to access advanced capabilities, such as search, retrieval, analysis, and data integration capabilities.

[0077] Figure 2 is a block diagram showing certain components of a database management system 128 according to some examples. The database management system 128 is shown to include a transaction processing component 202, a data modeling component 204, a storage management component 206, a data integration and replication component 208, a security and user management component 210, a backup and recovery component 212, a performance optimization component 214, a management and monitoring tool component 216, and an index management component 218. The user 106 may use the user device 108 to access or interact with the components of the database management system 128 via Figure 1 the user interface system 132 of.

[0078] The transaction handling component 202 handles database-related transactions, such as database queries, modifications, and other user requests. The transaction handling component 202 is responsible for ensuring the consistency and reliability of transactions executed within the database 136. The transaction handling component 202 manages the atomicity, consistency, isolation, and durability (ACID) properties of database transactions, ensuring that database operations are handled reliably and are not interfered with by concurrent transactions. For example, the transaction handling component 202 can utilize locking mechanisms or multi-version concurrency control to maintain data integrity.

[0079] During operation, the user 106 can input a database query via the user device 108. The database query is sent via the network 102 and then processed by the transaction handling component 202. The transaction handling component 202 may be responsible for parsing and executing, for example, SQL queries. The transaction handling component 202 can check the correctness of the query, create an execution plan, utilize indexes (if any), and perform the required operations based on the user instructions. As described further below, the transaction handling component 202 can work with the performance optimization component 214 to analyze various execution strategies for the query and select an execution strategy with, for example, the lowest estimated cost based on factors such as data size, indexes, join conditions, and system resources. The transaction handling component 202 is also responsible for processing the results and sending the results back to the user 106.

[0080] The data modeling component 204 enables the user 106 to create and manage data structures, such as tables, views, and schemas. The data modeling component 204 can support various data modeling paradigms, such as relational and dimensional modeling, thereby allowing for an efficient design of data storage consistent with business requirements and query patterns.

[0081] The storage management component 206 handles the allocation, organization, and optimization of data storage. For example, the storage management component 206 can manage storage between on-premises and cloud storage. As described above, in some cases, the database 136 can provide in-memory columnar storage. However, in other cases, the database 136 can provide different types of storage, such as a split between in-memory and secondary storage, which can be facilitated by the storage management component 206. The storage management component 206 is also responsible for data compression, partitioning, and other storage optimization techniques that enhance system performance and reduce storage footprint.

[0082] The data integration and replication component 208 is configured to ensure the synchronization and movement of data within and across different environments. It enables the integration of data from various sources, ensuring that the database 136 reflects the latest and accurate information. The data integration and replication component 208 can facilitate data replication for purposes such as disaster recovery, load balancing, or data warehousing. For example, the data integration and replication component 208 can copy data from an operational database to an analytics system to support real-time business intelligence without affecting the transaction workload.

[0083] The security and user management component 210 operates to manage access control and protect the database management system 128 and the database 136 from unauthorized access. For example, the security and user management component 210 is responsible for one or more of user authentication, authorization, role-based access control, and auditing capabilities.

[0084] The backup and recovery component 212 ensures the durability and recoverability of data. The backup and recovery component 212 can provide the user 106 with mechanisms for creating backups of the database 136 at regular intervals or in response to specific events. For example, it can support point-in-time recovery, allowing the database to be restored to a specific moment before a failure occurred.

[0085] The performance optimization component 214 provides tools for improving the performance of the database 136 (e.g., query execution speed). For example, the performance optimization component 214 can operate with the transaction processing component 202 to perform query optimization, in-memory computing optimization, and automatic adjustment of database parameters. The performance optimization component 214 also manages the indexes created to facilitate data retrieval or otherwise improve query performance.

[0086] The index management component 218 is a sub-component of the performance optimization component 214, as Figure 2 shown, which facilitates the management of indexes. The index management component 218 can allow the user 106 to create new indexes, modify existing indexes, or delete indexes. The index management component 218 can provide tools for analyzing query patterns and automatically adjusting indexes to adapt to changing data access patterns. In some examples, the index management component 218 communicates with the index recommendation system 130 to integrate the recommended indexes presented by the index recommendation system 130 into the database 136.

[0087] The management and monitoring tool component 216 provides tools for database administrators to manage and oversee the database system. For example, the user 106 can utilize the features of the management and monitoring tool component 216 to perform system configuration, performance monitoring, troubleshooting, and maintenance operations. The management and monitoring tool component 216 can work with the user interface system 132 to provide a dashboard for real-time monitoring of system health, thereby warning the administrator of potential problems.

[0088] Figure 3 is a block diagram illustrating certain components of the index recommendation system 130 according to some examples. The index recommendation system 130 is shown to include a data collection component 302, a feature extraction component 304, a vector generation component 306, a model interface component 308, a recommendation analysis component 310, a user interaction component 312, an index integration component 314, and a performance monitoring component 316.

[0089] The data collection component 302 is responsible for collecting data items used to generate index recommendations or data items used to monitor or evaluate performance. For example, the data collection component 302 can collect query logs, execution statistics, or database schema information. In some examples, the data collection component 302 collects data related to columns involved in the query to identify potential indexing opportunities. The data collection component 302 can be used to collect data for training machine learning models (e.g., such as reference Figure 4 or Figure 5 The data collection component 302 may also or alternatively be used to collect data for the trained model to use in performing inference.

[0090] The feature extraction component 304 is configured to extract useful features from the data items. For example, the feature extraction component 304 can obtain the data collected by the data collection component 302 and perform statistical analysis to obtain the value of a statistical feature associated with the index (e.g., cardinality or selectivity) or perform syntactic analysis to obtain the value of a declarative feature associated with the index (e.g., to identify whether a column involves a range condition, an equals condition, or some other query condition).

[0091] The vector generation component 306 is responsible for generating feature vectors. For example, the vector generation component 306 can obtain data collected by the data collection component 302, some of which may have been processed by the feature extraction component 304, and generate vectors in a structured format to represent the features of the candidate index. For example, the vector generation component 306 can encode various aspects of the data into a machine-readable format that can be processed by a machine learning algorithm. In some examples, the vector generation component 306 represents the presence or absence of certain SQL operations within a query as binary vectors or vector components, and also encodes statistical features such as selectivity and cardinality values. Thus, the vector generation component 306 can convert unstructured or raw data into structured data in a format that can be directly used by the machine learning component.

[0092] The model interface component 308 serves as a component of the index recommendation system 130 with one or more machine learning models (eg, by Figure 1interface between the machine learning system 144 and the model. In some examples, the model interface component 308 is used to feed feature vectors into one or more models and retrieve the index recommendation values generated by the one or more models. In some examples, the model interface component 308 manages the input and output data streams of at least one machine learning model trained using a data set including sample feature vectors, each sample feature vector being labeled to indicate whether the sample index corresponding to the sample feature vector exists in the sample database.

[0093] The recommendation analysis component 310 is configured to process the output of the machine learning model and formulate actionable index recommendations. For example, the recommendation analysis component 310 can analyze the index recommendation values (e.g., likelihood scores associated with predicted labels) generated by the machine learning model regarding candidate indexes, and determine whether to suggest the candidate indexes to the user.

[0094] In some examples, the recommendation analysis component 310 evaluates the performance or expected performance of a candidate index before causing the candidate index to be suggested to the user. For example, the recommendation analysis component 310 can determine the expected improvement in query execution time associated with the implementation of the candidate index, and cause the suggestion of the candidate index to be presented only if the expected improvement exceeds a threshold. In this regard, the recommendation analysis component 310 can operate the performance monitoring component 316 or communicate with the performance monitoring component 316.

[0095] The user interaction component 312 communicates with the user interface system 132 to ensure that index recommendations are presented to the user (e.g., presented to the user 106 via the user device 108). For example, the user interaction component 312 can cause the presentation of index recommendations that include one or more candidate indexes, and optionally include an indication of the expected performance or performance improvement associated with each candidate index. The user interaction component 312 can receive user input and automatically trigger the implementation or adjustment of an index based on the user input.

[0096] For example, the user interaction component 312 can cause the user interface (e.g., the web interface 140) at the user device 108 to present three recommended indexes. Then, the user 106 can select one of the indexes. In response to receiving the user selection, the user interaction component 312 triggers the integration of the index into the database schema as described elsewhere herein.

[0097] The index integration component 314 can automate the implementation of the approved or selected index. For example, the index integration component 314 can automatically update the database schema to include the selected index based on detecting user approval. For example, the index integration component 314 can automatically execute the necessary SQL commands to create a new index or adjust the attributes of an existing index.

[0098] The performance monitoring component 316 is configured to evaluate the impact of indexes on the performance of the database system. For example, the performance monitoring component 316 can be designed to monitor performance metrics or indicators to evaluate the effectiveness of indexes and provide feedback on the success of recommendations. The performance monitoring component 316 can provide a feedback loop to facilitate the continuous improvement of the index recommendation system 130.

[0099] In some examples, Figure 2 or Figure 3 at least some of the components shown in are configured to communicate with each other to implement the aspects described herein. One or more of the components described herein can be implemented using hardware (e.g., one or more processors of one or more machines) or a combination of hardware and software. For example, the components described herein can be implemented by a processor configured to perform the operations described herein for that component. Additionally, two or more of these components can be combined into a single component, or the functionality described herein for a single component can be subdivided among multiple components. Further, according to various examples, the components described herein can be implemented using a single machine, database, or device, or distributed across multiple machines, databases, or devices.

[0100] Figure 4 is a flowchart showing the operations of a method 400 suitable for training a machine learning model to generate index recommendation values. By way of example and not limitation, aspects of method 400 can be performed by Figure 1 , Figure 2 or Figure 3 one or more of the components, devices, systems, networks, or databases shown in.

[0101] Indexes are generally used to improve database query performance. For example, an SQL query (e.g., a "select" query that retrieves data from a database using one or more query conditions) can retrieve data more quickly by scanning an index tree rather than performing a full table scan, thereby reducing query execution time and computational resource consumption. At a high level, method 400 aims to automate the generation of index recommendations based on the observation that slow queries (possibly due to a lack of appropriate indexes) can exhibit patterns that are not specific to a single database but are common across different databases. Thus, a successful index strategy applied to one database can potentially be replicated in other databases to achieve performance improvements. In other words, if certain queries are slow in one context due to a lack of indexes, similar queries in another context (e.g., queries with similar statement characteristics or statistical characteristics) may also benefit from the creation of comparable indexes.

[0102] Method 400 begins at open-loop unit 402 and proceeds to operation 404, where index recommendation system 130 collects data related to various queries, existing indexes, and potential indexes. Operation 404 can involve collecting data by data collection component 302 from various databases or other sources. The data can include query logs as well as metadata of existing indexes, such as, for example, one or more tables to which an index belongs, one or more columns it includes, and statistical data (e.g., row count, data distribution, selectivity, cardinality, or presence of null values). In some examples, index recommendation system 130 obtains data about existing indexes from index management component 218 of database management system 128.

[0103] Method 400 proceeds to operation 406, where index recommendation system 130 performs index vectorization (e.g., using vector generation component 306). Index vectorization can be performed to allow a machine learning model to identify characteristics of existing indexes and learn the relationship between those characteristics and the expected performance improvement resulting from the indexes.

[0104] In some examples, feature extraction component 304 of index recommendation system 130 extracts values of declarative features and statistical features for each index to be included in a training data set, and vector generation component 306 constructs a feature vector for the index based on the declarative features and statistical features. Indexes to be included in the training data set can include real indexes (e.g., indexes that exist in one or more databases) and non-existent indexes (e.g., any index that could be created but has not yet been applied to any of the databases from which data items are collected). Since the indexes form part of the training data set, they can be referred to as sample indexes.

[0105] Queries can include query conditions for filtering, grouping, aggregating, or ordering data items. For each sample index, declarative features can include characteristics that describe the syntactic role of one or more columns within the corresponding query. For example, declarative features include information about how columns are used within various SQL clauses and how they are combined with aggregate functions. Declarative features can provide insight into the context in which columns are queried, which can influence the decision of whether and how to index them. For example, in the case where a sample index is a single-column index, its declarative features describe the position of the column within the query, such as whether the column is part of a "JOIN" clause.

[0106] Statistical features can be quantitative attributes that describe, for example, the data distribution and usage statistics of one or more columns within a database. Statistical features can provide insight into the potential effectiveness of an index on query performance by reflecting the underlying data characteristics. Thus, statistical features can provide a useful indication of what the impact of an index on a particular column or combination of columns in a database can be.

[0107] Table 1 below shows and describes non-limiting examples of statement features and statistical features. For example, selectivity as described in Table 1 can be an important statistical feature. Selectivity provides a measure of the "uniqueness" of column values, where higher selectivity potentially indicates a greater potential for performance gain from an index. For example, a column that contains only unique values (such as a primary key) has a selectivity of 1 (or 100%), which is the highest possible selectivity. On the other hand, a column where many rows have the same value will have a selectivity closer to 0 (or closer to 0%), indicating that an index on that column may not be as beneficial to query performance. In the latter case, the index may still provide some benefit, but the performance gain may not be as significant.

[0108]

[0109]

[0110] Table 1: Examples of Statement Features and Statistical Features

[0111] Feature vectors can be generated based on statement features and statistical features associated with an index for a sample query. For example, consider a sample query involving columns A, B, and C of a table, referred to as COLUMN_A, COLUMN_B, and COLUMN_C respectively in this example. To generate a feature vector for each possible single-column index, the statement features associated with the corresponding column can be encoded (e.g., using one-hot encoding), and the statistical feature values can be added to obtain the feature vector. In some examples, the statistical features can be normalized so that each value in the feature vector is, for example, not less than 0 and not greater than 1.

[0112] The features of a two-column index such as index(COLUMN_A, COLUMN_B) can be formed by concatenating the features of the single-column indexes index(COLUMN_A) and index(COLUMN_B). Additionally, common or combined statistical feature values (such as common or combined base values or common or combined selectivity values when considering two columns) can be added as one or more additional statistical features so that the feature vector of the two-column index more comprehensively describes its underlying statistics. A similar approach can be followed to construct a feature vector for a three-column candidate index index(COLUMN_A, COLUMN_B, COLUMN_C).

[0113] Labels can be added to each feature vector to allow supervised learning. For example, if index(COLUMN_A) already exists in the operational database, a positive label can be added. On the other hand, if for example index(COLUMN_B) does not exist, it can be labeled with a negative label. In other words, the feature vector of index(COLUMN_A) can be a positive vector (+1), while the feature vector of index(COLUMN_B) can be a negative vector (-1). In other examples, other criteria can be used for labeling. For example, if a sample index has been shown to improve query performance beyond a predetermined threshold, its feature vector can be positively labeled, while other samples can be negatively labeled.

[0114] Figure 5 FIG. 500 is a diagram showing an example form of a database query and multiple tables of a sample query 502 according to some examples to illustrate aspects of index vectorization performed to generate training data. The sample query 502 is an SQL statement involving two tables (CUSTOMER and VENDOR) and five columns, as Figure 5 shown.

[0115] In Figure 4 method 400 and Figure 5 FIG. 500, single-column indexes, two-column indexes, and three-column indexes are considered. However, it should be understood that in other examples, fewer types of indexes (e.g., only single-column indexes or only single-column and two-column indexes) can be considered, or a greater number of index types (e.g., four-column indexes can also be considered) can be considered.

[0116] To perform index vectorization in the case of Figure 5 , a feature vector is generated for each possible index of each of the three types. This includes Figure 5 the five single-column indexes (for C_A, C_B, C_C, C_D, and V_A) shown in Figure 5 , and all possible two-column index combinations (not shown in Figure 5 ), and all possible three-column indexes. Only two examples of three-column indexes are shown in Figure 5 (for the combination of C_A, C_B, and C_C, and the combination of C_A, C_B, and C_D). Thus,

[0117] For each feature vector, the statement features are encoded using one-hot encoding. For example, for a single-column index on column C_A, one-hot encoding is used to indicate that the column is involved in an "EQUAL" query condition but not in a "JOIN" or "RANGE" query condition. It should be understood that Figure 5The illustrative features shown are non-limiting examples and may include additional or alternative illustrative features.

[0118] In addition, for each feature vector, statistical features are added to the feature vector. For example, for a single-column index on column C_A, its cardinality and selectivity values with respect to sample query 502 are added. It will be understood again that Figure 5 the statistical features in are non-limiting examples and may include additional or alternative statistical features.

[0119] As described above, for a multi-column index, the feature vector can be extended to include not only the individual illustrative and statistical feature values of the constituent columns, but also values indicating combined features. As Figure 5 shown, the individual illustrative feature values can be concatenated and the statistical feature values can be added to the feature vector. For example, for a three-column index, the selectivity value can be based on the combined uniqueness of the three columns together.

[0120] The feature vectors are also labeled as positive samples (+1) or negative samples (-1) based on whether the sample index corresponds to an actual or existing index in the collected data. Thus, the full feature vector for sample index index(C_A) is [0, 1, 0, 500, 0.8, -1], and the full feature vector for sample index index(C_A,C_B,C_C) is [0, 1, 0, 0, 1, 0, 0, 0, 0, 1000, 0.95, +1]. The positive label for sample index index(C_A,C_B,C_C) may indicate that the index is beneficial to query performance, while the negative label for sample index index(C_A) may indicate that it is not (or not sufficiently) beneficial to query performance.

[0121] Referring again to Figure 4 , the feature vectors obtained during operation 406 are then used as training data to train one or more machine learning models. By including both positive and negative examples in the training data, the machine learning models can learn to distinguish between valid and invalid indexes.

[0122] Method 400 thus proceeds to operation 408, which includes selecting a machine learning algorithm. After sufficient training data has been collected (by way of example and not limitation, each machine learning model to be trained can obtain 10,000 or 15,000 training samples), it is necessary to select a suitable machine learning algorithm for training. In Figure 4In such a case, the Categorical Boosting (CATBoost) algorithm is selected. CATBoost is based on gradient boosting on decision trees. Note that this algorithm is a non-limiting example, and other algorithms can also be adopted. For example, another algorithm for training a binary classification model, such as Extreme Gradient Boosting (XGBoost), can be used.

[0123] At operation 410, a machine learning model is trained on the training data. In some examples, Figure 1 the machine learning system 144 is used to perform or facilitate the training. In Figure 4 such a case, a separate binary classification model is trained for each type of index. In other words, a first machine learning model is trained to handle single-column feature vectors, a second machine learning model is trained to handle two-column feature vectors, and a third machine learning model is trained to handle three-column feature vectors.

[0124] During the training process for each model, the input data is the feature vector of the sample index, and the predicted value is the label corresponding to that feature vector. Thus, the machine learning model is trained to predict whether the feature vector has a positive label or a negative label, and outputs a probability score as well as the predicted label. The model learns by adjusting its internal parameters to minimize the difference between its prediction and the actual label. Through iterative training using a large and diverse set of feature vectors, the model becomes capable of generalizing from the training data to predict the utility of new, unseen indexes.

[0125] Then, each trained model can output an index recommendation value for the candidate index. The index recommendation value (e.g., the probability score) can indicate the likelihood that the index will improve the performance of slow queries or the likelihood that the index will not improve the performance of slow queries. In some examples, method 400 includes validating the machine learning model at operation 412. This can ensure that the model performs well or satisfactorily on unseen data. Common validation metrics include precision, recall, and the F1 score (the latter provides a single score that balances precision and recall).

[0126] At operation 414, the trained machine learning model is integrated into the index recommendation system 130. For example, the model interface component 308 of the index recommendation system 130 can be communicatively coupled to the machine learning system 144 such that the feature vectors can be automatically communicated to the machine learning system 144 for inference through the appropriate trained model. Method 400 ends at the closed-loop element 416.

[0127] Thus, in some examples, the index recommendation system 130 can be deployed not only to identify when a query is performing poorly, but also to recommend appropriate indexes based on patterns learned from a large database. The index recommendation system 130 can leverage insights from "big data" and the predictive power of machine learning to recommend indexes that are customized to enhance the speed or efficiency of database queries.

[0128] Figure 6 is a flowchart showing the operation of a method 600 for automatically generating index recommendations to address slow queries. By way of example and not limitation, aspects of the method 600 can be performed by one or more of the components, devices, systems, networks, or databases shown in Figure 1 , Figure 2 or Figure 3 In addition, method 600 is described with reference to the non-limiting examples depicted in Figure 7 . Figure 7 is a diagram 700 showing the generation of index recommendations according to some examples, where the index recommendation system 130 employs three different machine learning models to generate index recommendation values (e.g., as described in method 400 of Figure 4 ).

[0129] Method 600 begins with an open-loop element 602 and proceeds to operation 604, where the index recommendation system 130 detects that the query execution time of a database query exceeds a threshold. For example, the index recommendation system 130 can use one or both of the data collection component 302 and the performance monitoring component 316 to examine database queries made by a user (e.g., user 106 of Figure 1 ) and determine whether the database query is being executed fast enough.

[0130] For example, the threshold can be 1 second, and the index recommendation system 130 marks database queries with a query execution time exceeding 1 second as slow queries. In some cases, the index recommendation system 130 can examine the average query execution time for this determination.

[0131] In some examples, user 106 or an administrator enables the feature for index recommendation in the context of the database management system 128 and the database 136. The index recommendation system 130 can run as a microservice that monitors the database queries of user 106 and automatically triggers index recommendations for detected slow queries to improve the functionality of the database management system 128 and the database 136. The index recommendation system 130 can also automatically present its index recommendations for the user to consider or approve, as described in more detail below.

[0132] Refer to Figure 7, the slow query 702 is shown only as an example. The slow query 702 involves three columns (A, B, and C) from a table (TEST (test)). The index recommendation system 130 can detect the slow query 702 and automatically trigger obtaining index recommendation values to address the slow query 702.

[0133] At Figure 6 operation 606 of, method 600 includes identifying candidate indexes associated with the slow query 702. The index recommendation system 130 automatically identifies that three candidate indexes can be generated for the respective columns (A, B, and C), three additional candidate indexes can be generated for the respective column pairs (A - B, B - C, and A - C), and another candidate index can be generated for the combination of all three columns (A - B - C). Thus, in the case of FIG. 700, operation 606 involves identifying seven different candidate indexes associated with the slow query 702.

[0134] Then, at operation 608, the index recommendation system 130 uses the vector generation component 306 to generate feature vectors for each of the candidate indexes. For example, using the feature engineering process described as in reference Figure 4 and Figure 5 , the index recommendation system 130 can perform vectorization 704 to obtain three single - column feature vectors 706, three two - column feature vectors 708, and one three - column feature vector 710, as Figure 7 shown.

[0135] In some examples, each feature vector represents both the declarative features and one or more statistical features of the corresponding candidate index. For example, the single - column feature vectors 706 can each have the format [JOIN, EQUAL, RANGE, ORDER_BY, CARDINALITY, SELECTIVITY], where the feature vector of candidate_index(A) is [0, 1, 0, 0, 500, 0.6]. In this case, one - hot encoding is used to encode the declarative features to encode the presence of the equality query condition in the slow query 702, and the declarative features are added as the last two vector values.

[0136] As explained elsewhere, for the two-column feature vectors 708 and the three-column feature vectors 710, each feature vector can include both the individual feature vector values of the respective columns and the values representing the combined characteristics of the columns. Thus, the index recommendation system 130 can generate column-specific values (e.g., individual statement feature values) indicating the characteristics of each column in the multi-column candidate indexes and multi-column values (e.g., statistical feature values of the combined columns) indicating the combined characteristics of each column in the multi-column candidate indexes, and combine or concatenate the column-specific values and the multi-column values to form feature vectors. Only as an example, the three-column feature vector 710 for candidate_index(A-B-C) can have the following format: [JOIN_A,EQUAL_A,RANGE_A,ORDER_BY_A,JOIN_B,EQUAL_B,RANGE_B,ORDER_BY_B,JOIN_C,EQUAL_C,RANGE_C,ORDER_BY_C,COMBINED_CARDINALITY_A_B_C,COMBINED_SELECTIVITY_A_B_C].

[0137] Then, at operation 610, the feature vectors are provided to a machine learning model to obtain index recommendation values. In Figure 7 the case of, the index recommendation system 130 utilizes separate machine learning models for each type of candidate index. Specifically, and as Figure 7 shown, during inference 712, the single-column feature vectors 706 are fed to the single-column model 714, the two-column feature vectors 708 are fed to the two-column model 716, and the three-column feature vectors 710 are fed to the three-column model 718.

[0138] For each candidate index, the output of the inference 712 is one or more index recommendation values. For example, for each candidate index, the index recommendation system 130 can receive a predicted label and a probability value or a confidence score from the machine learning system 144. As referenced Figure 4 described, the training process can involve using labeled data, where each feature vector in the training dataset is associated with a label indicating whether the associated index is positive (e.g., beneficial to query performance) or negative (e.g., not beneficial or not achieved).

[0139] Analyze the outputs of the single-column model 714, the two-column model 716, and the three-column model 718. As Figure 7As shown, the analysis 720 is performed by the index recommendation system 130 (e.g., using the recommendation analysis component 310) on the single-column recommendation values 722 generated for the corresponding single-column feature vectors 706, the two-column recommendation values 724 generated for the corresponding two-column feature vectors 708, and the three-column recommendation values 726 generated for the three-column feature vectors 710. For example, the index recommendation system 130 can analyze the index recommendation values to determine whether the predicted label is positive, and for candidate indexes with a positive predicted label, determine what the probability value or confidence score is.

[0140] In Figure 7 this case, the index recommendation system 130 selects one candidate index corresponding to one of the single-column feature vectors 706 (e.g., the positive-labeled candidate index with the highest score), one candidate index corresponding to one of the two-column feature vectors 708 (e.g., the positive-labeled candidate index with the highest score), and the candidate index corresponding to the three-column feature vector 710 (e.g., assuming it has a positive predicted label). Thus, the index recommendation system 130 generates a recommended index 728, a recommended index 730, and a recommended index 732, corresponding to the single-column index, the two-column index, and the three-column index, respectively.

[0141] Table 2 below provides illustrative examples of the single-column recommendation values 722, the two-column recommendation values 724, and the three-column recommendation values 726. Based on the values in Table 2, the index recommendation system 130 can, for example, select the candidate index candidate_index(A) as the recommended index 728, select the candidate index candidate_index(A-C) as the recommended index 730, and select the candidate index candidate_index(A-B-C) as the recommended index 732. For example, if the model output of the candidate index candidate_index(A-B-C) has indicated a negative label or a probability score less than 50%, the index recommendation system 130 might have selected only the recommended index 728 and the recommended index 730.

[0142]

[0143]

[0144] Table 2: Examples of index recommendation values for various candidate indexes

[0145] The index recommendation values generated by the trained models 714, 716, and 718 are thus used by the index recommendation system 130 to make decisions about recommendations. As described above, each model can predict labels and probability scores. However, it should be understood that in other examples, the models can be trained to output only probability scores. For example, a threshold (such as 0.5 or 50%) can be set to classify candidate indexes with a probability higher than the threshold as positive (recommended), and candidate indexes with a probability lower than the threshold as negative (not recommended).

[0146] In some examples, and referring again to Figure 6 , method 600 includes evaluating at least a subset of candidate indexes at operation 612. Although more indexes generally may result in better performance when it comes to slow queries, there may also be drawbacks or problems associated with increasing the number of indexes linked to a database. For example, more indexes may result in increased overhead and storage space requirements. Additionally, the indexes recommended by index recommendation system 130 may be ineffective or slow.

[0147] To this end, before presenting the final recommendations, index recommendation system 130 may evaluate the performance of one or more candidate indexes. For example, index recommendation system 130 may use recommendation analysis component 310 or performance monitoring component 316 to test recommended index 728, recommended index 730, and recommended index 732 against Figure 1 the sample library 138 of

[0148] For each candidate index to be evaluated, index recommendation system 130 may check whether the candidate index is expected to result in an improvement in query execution time based on querying sample library 138. For example, index recommendation system 130 may compare a first query execution time measured without using the candidate index with a second query execution time measured when using the candidate index (both against sample library 138 in both cases) to verify that the response time will improve or is expected to improve.

[0149] Note that query execution time or an improvement in query execution time is one example of a performance metric that can be used in operation 612. Other performance metrics or factors may also be considered, such as changes in storage or cost associated with the integration of the index.

[0150] Index recommendation system 130 may select at least one of the candidate indexes, for example, based on the index recommendation values and the results of the performance evaluation. For example, index recommendation system 130 may test the performance of recommended index 728, recommended index 730, and recommended index 732, and determine that recommended index 728 and recommended index 730 (but not recommended index 732) should be recommended to the user. For example, index recommendation system 130 may determine that recommended index 732 is ineffective or will not result in a sufficient improvement in query execution time, and then select only recommended index 728 and recommended index 730 as its final recommendations. In other words, index recommendation system 130 may use the outputs of training models 714, 716, and 718 to perform a first filtering process to arrive at an initial set of recommendations, and then perform a performance evaluation as a second filtering process to arrive at the final set of recommendations that are presented.

[0151] Referring again to Figure 6, method 600 proceeds to operation 614, where the index recommendation system 130 (e.g., using the user interaction component 312 and via Figure 1 's user interface system 132) causes one or more of the candidate indexes to be presented at the user device (e.g., the user device 108 of user 106) in operation 614 as part of the index recommendation. For example, as referenced Figure 7 above, the index recommendation system 130 can present the recommended indexes 728 and 730 in the user interface of the database management system 128 or the index recommendation system 130 at the user device 108.

[0152] In some examples, the recommended indexes are presented with additional data (e.g., in the Figure 1 web interface 140 or application interface 142), such as performance evaluation results (e.g., "We recommend index [INDEX_NAME]. This index is estimated to result in a 35% improvement in query execution time.") or potential resource impacts (e.g., an indication of the storage space requirements associated with the recommendation). This allows user 106 to make an informed decision regarding the recommended indexes.

[0153] In operation 616, the index recommendation system 130 receives the user's selection of a candidate index. For example, user 106 can review the recommendations and use the user device 108 to select the recommended index 730 (which corresponds to one of the two-column candidate indexes) for implementation. In response to the user selection, the index recommendation system 130 automatically updates the database schema (e.g., the Figure 1 schema of the database 136) to include the selected index.

[0154] As part of operation 618, the index recommendation system 130 can work with the index management component 218 of the database management system 128 to update the database schema to reflect the presence or details of the new index. For example, the index management component 218 can update the database schema to include or update the index definition. The index definition can indicate the name of the index, one or more of the tables to which it belongs, and one or more of the columns it covers. It can also specify the type of the index (such as B-tree, hash, or full-text) and whether it is unique or non-unique. Then, the database management system 128 can use the updated database schema to modify the database structure, which can involve automatically executing data definition language (DDL) statements, such as "CREATE INDEX", to physically implement the index in the database. Method 600 ends at the closed-loop element 620.

[0155] In view of the above-described implementations of the subject matter, the present application discloses the following list of examples, where more than one feature of an example taken alone or in combination and optionally in combination with one or more features of one or more additional examples is also an additional example that falls within the disclosure of the present application.

[0156] Example 1 is a system, including: at least one memory storing instructions; and one or more processors configured by the instructions to perform operations, the operations including: identifying a plurality of candidate indexes associated with a database query classified as a slow query; for each candidate index of the plurality of candidate indexes, generating a feature vector representing the statement features and statistical features associated with the candidate index; providing the feature vectors to one or more machine learning models to obtain index recommendation values for each candidate index of the plurality of candidate indexes; causing an index recommendation identifying a first index of the plurality of candidate indexes to be presented at a user device, the first index being identified in the index recommendation based at least in part on the index recommendation value obtained for the first index; receiving user input indicating the user's selection of the first index; and in response to receiving the user input, automatically updating the database schema to include the first index.

[0157] In Example 2, the subject matter of Example 1 includes that the operations further include: detecting that the query execution time of a database query exceeds a threshold; and in response to detecting that the query execution time associated with the database query exceeds the threshold: designating the database query as a slow query and automatically triggering obtaining an index recommendation value.

[0158] In Example 3, the subject matter of Example 2 includes that the database query is one of a plurality of database queries, and the operations further include: using an index recommendation application to monitor the query execution time of each database query of the plurality of database queries, the database query being designated from the plurality of database queries.

[0159] In Example 4, the subject matter of any one of Examples 1-3 includes that the operations further include, before presenting the index recommendation at the user device: evaluating the performance of the first index according to a predetermined performance metric; and based on the performance of the first index, selecting the first index to be included in the index recommendation.

[0160] In Example 5, the subject matter of Example 4 includes that evaluating the performance of the first index includes: determining a first query execution time of the database query when executed for a data subset of the database without applying the first index; determining a second query execution time of the database query when executed for the data subset of the database by applying the first index; and comparing the first query execution time with the second query execution time.

[0161] In Example 6, the subject matter of any one of Examples 4-5 includes, wherein causing an index recommendation to be presented at a user device includes causing a user interface to be generated, the user interface presenting the index recommendation along with an indication of the performance of a first index.

[0162] In Example 7, the subject matter of any one of Examples 1-6 includes, wherein each of a plurality of candidate indexes has one or more candidate index columns, and a statement feature indicates the position of each of the one or more candidate index columns within a database query.

[0163] In Example 8, the subject matter of Example 7 includes, wherein, for each of the one or more candidate index columns, the position is encoded in a feature vector by an encoding scheme indicating the presence or absence of a corresponding query condition involving the candidate index column.

[0164] In Example 9, the subject matter of any one of Examples 1-8 includes, wherein each of a plurality of candidate indexes has one or more candidate index columns, and a statistical feature includes at least one of the following: at least one selectivity value associated with a database query, at least one cardinality value associated with a database query, the number of null values in each of the one or more candidate index columns, or the number of rows associated with each of the one or more candidate index columns.

[0165] In Example 10, the subject matter of any one of Examples 1-9 includes, wherein at least one of the plurality of candidate indexes is a multi-column candidate index, and a feature vector of the multi-column candidate index is generated by: generating column-specific values indicating characteristics of respective columns in the multi-column candidate index; generating multi-column values indicating combined characteristics of respective columns in the multi-column candidate index; and combining the column-specific values and the multi-column values.

[0166] In Example 11, the subject matter of any one of Examples 1-10 includes, wherein the plurality of candidate indexes includes a first subset and a second subset, the first subset including one or more single-column candidate indexes, and the second subset including one or more multi-column candidate indexes.

[0167] In Example 12, the subject matter of Example 11 includes, wherein one or more machine learning models include a first machine learning model trained to process each feature vector of the one or more single-column candidate indexes and at least one second machine learning model trained to process each feature vector of the one or more multi-column candidate indexes.

[0168] In Example 13, the subject matter of any of Examples 11 - 12 includes that a first index is selected from a first subset, and the index recommendation also identifies a second index among a plurality of candidate indexes, where the second index is selected from a second subset and is identified in the index recommendation at least partially based on an index recommendation value obtained for the second index.

[0169] In Example 14, the subject matter of any of Examples 1 - 13 includes that a dataset including sample feature vectors is used to train each of one or more machine learning models, and each sample feature vector is labeled to indicate whether a sample index corresponding to the sample feature vector exists in a sample database.

[0170] Example 15 is a method that includes: identifying, by one or more computing devices, a plurality of candidate indexes associated with a database query classified as a slow query; generating, by one or more computing devices for each candidate index among the plurality of candidate indexes, a feature vector representing statement features and statistical features associated with the candidate index; providing, by one or more computing devices, the feature vectors to one or more machine learning models to obtain an index recommendation value for each candidate index among the plurality of candidate indexes; causing, by one or more computing devices, an index recommendation identifying a first index among the plurality of candidate indexes to be presented, where the first index is identified in the index recommendation at least partially based on the index recommendation value obtained for the first index; receiving, by one or more computing devices, a user input indicating a user's selection of the first index; and automatically updating, in response to receiving the user input, a database schema by one or more computing devices to include the first index.

[0171] In Example 16, the subject matter of Example 15 includes detecting that a query execution time of a database query exceeds a threshold; and in response to detecting that the query execution time associated with the database query exceeds the threshold: designating the database query as a slow query and automatically triggering obtaining an index recommendation value.

[0172] In Example 17, the subject matter of any of Examples 15 - 16 includes, before presenting the index recommendation: evaluating the performance of the first index according to a predetermined performance metric; and selecting the first index to be included in the index recommendation based on the performance of the first index.

[0173] Example 18 is a non-transitory computer-readable medium storing instructions that, when executed by one or more processors, cause the one or more processors to perform operations including: identifying a plurality of candidate indexes associated with a database query classified as a slow query; for each candidate index of the plurality of candidate indexes, generating a feature vector representing statement features and statistical features associated with the candidate index; providing the feature vector of each candidate index of the plurality of candidate indexes to a machine learning model to obtain an index recommendation value for the candidate index; causing an index recommendation identifying a first index of the plurality of candidate indexes to be presented at a user device, the first index being identified in the index recommendation at least in part based on the index recommendation value obtained for the first index; receiving user input indicating the user's selection of the first index; and in response to receiving the user input, automatically updating the database schema to include the first index.

[0174] In Example 19, the subject matter of Example 18 includes that the operations further include: detecting that a query execution time of a database query exceeds a threshold; and in response to detecting that the query execution time associated with the database query exceeds the threshold: designating the database query as a slow query and automatically triggering obtaining an index recommendation value.

[0175] In Example 20, the subject matter of any one of Examples 18 - 19 includes that the operations further include, before presenting the index recommendation at the user device: evaluating the performance of the first index according to a predetermined performance metric; and based on the performance of the first index, selecting the first index to be included in the index recommendation.

[0176] Example 21 is at least one machine-readable medium including instructions that, when executed by a processing circuit, cause the processing circuit to perform operations to implement any one of Examples 1 - 20.

[0177] Example 22 is an apparatus including components for implementing any one of Examples 1 - 20.

[0178] Example 23 is a system implementing any one of Examples 1 - 20.

[0179] Example 24 is a method implementing any one of Examples 1 - 20.

[0180] Figure 8 is a block diagram showing a machine learning program 800 according to some examples. A machine learning program (also referred to as a machine learning algorithm or tool) can be used as part of the systems described herein to perform one or more operations, e.g., identifying relationships between reported problems and context data, identifying context dependencies, extracting or inferring additional problem metadata using initial problem metadata, generating priority ratings, generating estimated impacts, or combinations thereof.

[0181] Machine learning is a field of study that gives computers the ability to learn without being explicitly programmed. Machine learning explores the research and construction of algorithms (also referred to as tools in this document) that can learn from existing data or be trained using existing data and make predictions about new data or based on new data. Such machine learning tools operate by building a model from example training data 808 in order to make data-driven predictions or decisions expressed as outputs or evaluations (e.g., evaluation 816). Although examples are presented for several machine learning tools, the principles presented in this document can be applied to other machine learning tools.

[0182] In some examples, different machine learning tools can be used. For example, logistic regression (LR), naive Bayes, random forest (RF), neural network (NN), matrix factorization, and support vector machine (SVM) tools can be used.

[0183] Two common types of problems in machine learning are classification problems and regression problems. Classification problems, also known as categorical problems, aim to classify items into one of several categorical values (e.g., is the object an apple or an orange?). Regression algorithms aim to quantify some item (e.g., by providing a value as a real number).

[0184] Machine learning program 800 supports two types of phases, namely a training phase 802 and a prediction phase 804. In the training phase 802, supervised learning, unsupervised, or reinforcement learning can be used. For example, machine learning program 800 (1) receives features 806 (e.g., as structured or labeled data in supervised learning) and / or (2) identifies features 806 in training data 808 (e.g., unstructured or unlabeled data for unsupervised learning). In the prediction phase 804, machine learning program 800 uses features 806 to analyze input data 812 to generate a result or prediction, as an example of evaluation 816.

[0185] In the training phase 802, feature engineering is used to identify features 806 and can include identifying informative, discriminative, and independent features for the effective operation of machine learning program 800 in pattern recognition, classification, and regression. In some examples, training data 808 includes labeled data, which is known data of pre-identified features 806 and one or more results. Each of the features 806 can be a variable or an attribute, such as a single measurable attribute of a process, an item, a system, or a phenomenon represented by a data set (e.g., training data 808). Features 806 can also have different types, such as numerical features, strings, and graphics, and can include one or more of content 818, concepts 820, attributes 822, historical data 824, and / or user data 826, by way of example only.

[0186] The concept of features in this context is related to the concept of explanatory variables used in statistical techniques such as linear regression. Selecting informative, discriminative, and independent features is important for the effective operation of machine learning program 800 in pattern recognition, classification, and regression. Features can be of different types, such as numerical features, strings, and graphics.

[0187] During training phase 802, machine learning program 800 uses training data 808 to find correlations between features 806 that affect the prediction result or evaluation 816. Using training data 808 and the identified features 806, machine learning program 800 is trained during training phase 802 at machine learning program training 810. Machine learning program 800 evaluates the values of features 806 when the features 806 are relevant to training data 808. The result of training is a trained machine learning program 814 (e.g., a trained or learned model).

[0188] In addition, training phase 802 can involve machine learning where training data 808 is structured (e.g., labeled during preprocessing operations), and the trained machine learning program 814 implements a relatively simple neural network 828 capable of performing operations such as classification and clustering. In other examples, training phase 802 can involve deep learning where training data 808 is unstructured, and the trained machine learning program 814 implements a deep neural network 828 capable of performing both feature extraction and classification / clustering operations.

[0189] The neural network 828 generated during training phase 802 and implemented within the trained machine learning program 814 can include a hierarchical (e.g., layered) organization of neurons. For example, neurons (or nodes) can be hierarchically arranged into multiple layers, including an input layer, an output layer, and multiple hidden layers. Each layer within neural network 828 can have one or more neurons, and each of these neurons operationally computes a small function (e.g., an activation function). For example, if the activation function generates a result that exceeds a particular threshold, the output can be communicated from that neuron (e.g., the sending neuron) to a connected neuron (e.g., the receiving neuron) in a successive layer. The connections between neurons also have associated weights that define the influence of the input from the sending neuron to the receiving neuron.

[0190] In some examples, neural network 828 can also be one of a variety of different types of neural networks, by way of example only, including a single-layer feedforward network, an artificial neural network (ANN), a recurrent neural network (RNN), a symmetrically connected neural network, and an unsupervised pre-trained network, a transformer network, a convolutional neural network (CNN), or a recurrent neural network (RNN).

[0191] During the prediction phase 804, the trained machine learning program 814 is used to perform an evaluation. Input data 812 is provided as an input to the trained machine learning program 814, and the trained machine learning program 814 generates an evaluation 816 as an output in response to receiving the input data 812.

[0192] Figure 9 is a block diagram 900 showing a software architecture 902 for a computing device according to some examples. The software architecture 902 can be used in conjunction with various hardware architectures, for example, as described herein. Figure 9 This is merely a non - limiting illustration of a software architecture, and many other architectures can be implemented to facilitate the functions described herein. A representative hardware layer 904 is shown and can represent, for example, any of the computing devices referenced above. In some examples, the hardware layer 904 can be implemented according to Figure 10 the architecture of a computer system.

[0193] The representative hardware layer 904 includes one or more processing units 906 with associated executable instructions 908. The executable instructions 908 represent the executable instructions of the software architecture 902, including the implementation of the methods, modules, subsystems, components, etc. described herein, and can also include a memory and / or storage module 910, which also has executable instructions 908. The hardware layer 904 can also include other hardware indicated by other hardware 912 and other hardware 922, where other hardware 912 and other hardware 922 represent any other hardware of the hardware layer 904, such as other hardware shown as part of the software architecture 902.

[0194] In Figure 9 the architecture, the software architecture 902 can be conceptualized as a stack of layers, where each layer provides a specific function. For example, the software architecture 902 can include layers such as an operating system 914, libraries 916, a framework / middleware layer 918, applications 920, and a presentation layer 944. In operation, an application 920 or other component within a layer can make an API call 924 through the software stack, and in response to the API call 924, access a response, return value, etc. shown as a message 926. The layers shown are representative in nature, and not all software architectures have all layers. For example, some mobile or specialized operating systems may not provide a framework / middleware layer 918, while other operating systems may provide such a layer. Other software architectures can include additional or different layers.

[0195] The operating system 914 can manage hardware resources and provide common services. The operating system 914 can include, for example, a kernel 928, services 930, and drivers 932. The kernel 928 can act as an abstraction layer between the hardware and other software layers. For example, the kernel 928 can be responsible for memory management, processor management (e.g., scheduling), component management, networking, security settings, etc. The services 930 can provide other common services for other software layers. In some examples, the services 930 include interrupt services. The interrupt services can detect the receipt of an interrupt and, in response, cause the software architecture 902 to pause its current processing and execute an interrupt service routine (ISR) when the interrupt is accessed.

[0196] The drivers 932 can be responsible for controlling or interfacing with the underlying hardware. For example, depending on the hardware configuration, the drivers 932 can include a display driver, a camera driver, a driver, a flash drive, a serial communication driver (e.g., a Universal Serial Bus (USB) driver), a driver, a Near Field Communication (NFC) driver, an audio driver, a power management driver, etc.

[0197] The library 916 can provide a common infrastructure that can be used by the application 920 or other components or layers. The library 916 generally provides functions that allow other software modules to perform tasks in a way that is easier than directly interfacing with the underlying operating system 914 functions (e.g., the kernel 928, the services 930, or the drivers 932). The library 916 can include a system library 934 (e.g., a C standard library), which can provide functions such as memory allocation functions, string manipulation functions, mathematical functions, etc. Additionally, the library 916 can include an API library 936, such as a media library (e.g., a library for supporting the presentation and manipulation of various media formats such as MPEG4, H.264, MP3, AAC, AMR, JPG, PNG), a graphics library (e.g., an OpenGL framework that can be used to render two - dimensional and three - dimensional graphics content on a display), a database library (e.g., SQLite that can provide various relational database functions), a web library (e.g., WebKit that can provide web browsing functions), etc. The library 916 can also include a variety of other libraries 938 to provide many other APIs to the application 920 and other software components / modules.

[0198] The framework / middleware layer 918 can provide a higher-level common infrastructure that can be utilized by the application 920 or other software components / modules. For example, the framework / middleware layer 918 can provide various graphical user interface (GUI) functions, advanced resource management, advanced location services, etc. The framework / middleware layer 918 can provide a wide range of other APIs that can be utilized by the application 920 or other software components / modules, some of which may be specific to a particular operating system or platform.

[0199] The application 920 includes built-in applications 940 or third-party applications 942. Examples of representative built-in applications 940 can include, but are not limited to, a contacts application, a browser application, a book reader application, a location application, a media application, a messaging application, or a gaming application. The third-party applications 942 can include any built-in applications as well as a wide variety of other applications. In a specific example, the third-party applications 942 (e.g., applications developed using an Android TM or iOS TM software development kit (SDK)) can be mobile software running on a mobile operating system (such as iOS TM or Android TM or a phone or other mobile computing device operating system). In this example, the third-party applications 942 can call API calls 924 provided by a mobile operating system such as the operating system 914 to facilitate the functions described herein.

[0200] The application 920 can utilize built-in operating system functions (e.g., the kernel 928, services 930, or drivers 932), libraries (e.g., system libraries 934, API libraries 936, and other libraries 938), and the framework / middleware layer 918 to create a user interface to interact with the user of the system. Alternatively or additionally, in some systems, the interaction with the user can occur through a presentation layer (such as the presentation layer 944). In these systems, the application / module "logic" can be separated from the aspects of the application / module that interact with the user.

[0201] Some software architectures utilize virtual machines. In Figure 9In the example of, this is illustrated by virtual machine 948. The virtual machine creates a software environment in which applications / modules can execute as if they were executing on a hardware computing device. The virtual machine is hosted by a host operating system (operating system 914) and typically but not always has a virtual machine monitor 946 that manages the operation of the virtual machine and its interface with the host operating system (e.g., operating system 914). Software architectures execute within the virtual machine 948, such as operating system 950, libraries 952, frameworks / middleware 954, applications 956, or presentation layer 958. These software architecture layers that execute within the virtual machine 948 may be the same as or different from the corresponding layers described previously.

[0202] Certain examples are described herein as including logic or multiple components, modules, or mechanisms. A module or component can constitute a software module / component (e.g., (1) code embodied on a non-transitory machine-readable medium or (2) code embodied in a transmitted signal) or a hardware-implemented module / component. A hardware-implemented module / component is a tangible unit capable of performing certain operations and can be configured or arranged in a certain manner. In an example, one or more computer systems (e.g., stand-alone, client, or server computer systems) or one or more hardware processors can be configured by software (e.g., an application or an application portion) to operate as a hardware-implemented module / component that performs certain operations as described herein.

[0203] In various examples, a hardware-implemented module / component can be implemented mechanically or electronically. For example, a hardware-implemented module / component can include dedicated circuitry or logic that is permanently configured to perform certain operations (e.g., as a dedicated processor, such as a field programmable gate array (FPGA) or an application specific integrated circuit (ASIC)). A hardware-implemented module / component can also include programmable logic or circuitry that is temporarily configured by software to perform certain operations (e.g., included within a general-purpose processor or another programmable processor). It should be understood that the decision to implement a hardware-implemented module / component mechanically in dedicated and permanently configured circuitry or in temporarily configured circuitry (e.g., configured by software) can be driven by cost and time considerations.

[0204] Accordingly, the terms "hardware-implemented module" or "hardware-implemented component" should be understood to encompass a tangible entity, i.e., a physical structure, a permanent configuration (e.g., hardwired), or a temporary or transient configuration (e.g., programmed) to operate or perform certain operations described herein in a certain manner. Considering an example where a hardware-implemented module / component is temporarily configured (e.g., programmed), each of the hardware-implemented modules / components does not need to be configured or instantiated at any given moment. For example, in the case where a hardware-implemented module / component includes a general-purpose processor configured by software, the general-purpose processor can be configured as a corresponding different hardware-implemented module / component at different times. The software can accordingly configure the processor, e.g., to constitute a specific hardware-implemented module / component at one moment and a different hardware-implemented module / component at a different moment.

[0205] Hardware-implemented modules / components can provide information to and receive information from other hardware-implemented modules / components. Accordingly, the described hardware-implemented modules / components can be considered to be communicatively coupled. In the case where multiple such hardware-implemented modules / components exist simultaneously, communication can be achieved through signal transmission (e.g., via appropriate circuitry and buses connecting the hardware-implemented modules / components). In an example where multiple hardware-implemented modules / components are configured or instantiated at different times, communication between such hardware-implemented modules / components can be achieved, for example, by storing and retrieving information in a memory structure accessible to the multiple hardware-implemented modules / components. For example, one hardware-implemented module / component can perform an operation and store the output of the operation in a memory device communicatively coupled thereto. Then, another hardware-implemented module / component can later access the memory device to retrieve and process the stored output. Hardware-implemented modules / components can also initiate communication with input or output devices and can operate on resources (e.g., a collection of information).

[0206] The various operations of the example methods described herein can be performed, at least in part, by one or more processors temporarily configured (e.g., by software) or permanently configured to perform the relevant operations. Whether temporarily or permanently configured, such processors can constitute processor-implemented modules / components that operate to perform one or more operations or functions. In some examples, the modules / components referred to herein can include processor-implemented modules / components.

[0207] Similarly, the methods described herein can be implemented, at least in part, by a processor. For example, at least some of the operations of the method can be performed by one or more processors or modules / components implemented by a processor. The execution of certain operations can be distributed among one or more processors, not only residing within a single machine but also deployed across multiple machines. In some examples, one or more processors can be located in a single location (e.g., within a home environment, an office environment, or a server farm), while in other examples, the processors can be distributed across multiple locations.

[0208] One or more processors can also operate to support the performance of related operations in a “cloud computing” environment or as “software as a service (SaaS)”. For example, at least some of the operations can be performed by a group of computers (as an example of machines including processors), and these operations can be accessed via a network (e.g., the Internet) and via one or more appropriate interfaces (e.g., APIs).

[0209] Examples can be implemented in digital electronic circuitry, or in computer hardware, firmware, or software, or in combinations thereof. Examples can be implemented using a computer program product, e.g., a computer program tangibly embodied in an information carrier, e.g., in a machine-readable medium, for execution by, or to control the operation of, a data processing apparatus (e.g., a programmable processor, a computer, or multiple computers).

[0210] The computer program can be written in any form of programming language, including compiled or interpreted languages, and it can be deployed in any form, including as a stand-alone program or as a module, subroutine, or other unit suitable for use in a computing environment. The computer program can be deployed to execute on one computer or on a single site or across multiple sites distributed and interconnected by a communication network.

[0211] Figure 10A block diagram of a machine in an example form of a computer system 1000 within which instructions 1024 can be executed to cause the machine to perform any one or more of the methods discussed herein. In an alternative example, the machine operates as a stand-alone device or can be connected (e.g., networked) to other machines. In a networked deployment, the machine can operate in the capacity of a server or a client machine in a server-client network environment, or as a peer machine in a peer-to-peer (or distributed) network environment. The machine can be a personal computer (PC), tablet PC, set top box (STB), personal digital assistant (PDA), cellular telephone, web appliance, network router, switch or bridge, or any machine capable of executing instructions (sequential or otherwise) that specify actions to be taken by that machine. Further, while only a single machine is illustrated, the term "machine" shall also be taken to include any collection of machines that individually or jointly execute a set (or multiple sets) of instructions to perform any one or more of the methods discussed herein.

[0212] The example computer system 1000 includes a processor 1002 (e.g., a central processing unit (CPU), a graphics processing unit (GPU), or both), a main or primary memory 1004, and a static memory 1006, which communicate with each other via a bus 1008. The computer system 1000 may also include a video display unit 1010 (e.g., a liquid crystal display (LCD) or a cathode ray tube (CRT)). The computer system 1000 also includes an alphanumeric input device 1012 (e.g., a keyboard or a touch-sensitive display screen), a UI navigation (or cursor control) device 1014 (e.g., a mouse), a storage unit 1016, a signal generation device 1018 (e.g., a speaker), and a network interface device 1020.

[0213] As used herein, the term "processor" may refer to any one or more circuits or virtual circuits (e.g., physical circuits emulated by logic executed on an actual processor) that manipulate data values in accordance with control signals (e.g., commands, opcodes, machine code, control words, macroinstructions, etc.) and produce corresponding output signals applied to operate a machine. A processor may include, for example, at least one of a central processing unit (CPU), a reduced instruction set computing (RISC) processor, a complex instruction set computing (CISC) processor, a graphics processing unit (GPU), a digital signal processor (DSP), a tensor processing unit (TPU), a neural processing unit (NPU), a vision processing unit (VPU), a machine learning accelerator, an artificial intelligence accelerator, an application specific integrated circuit (ASIC), a field programmable gate array (FPGA), a radio frequency integrated circuit (RFIC), a neuromorphic processor, a quantum processor, or any combination thereof. A processor may be a multi-core processor having two or more independent processors (sometimes referred to as "cores") that can execute instructions simultaneously. A multi-core processor may include multiple computing cores on a single integrated circuit die, each of which may independently and concurrently execute program instructions. Parallel processing on a multi-core processor may be implemented via architectures such as superscalar, VLIW, vector processing, or SIMD, which allow each core to run separate instruction streams simultaneously. A processor may be emulated in software, run on a physical processor, or be a virtual processor or virtual circuit. A virtual processor may behave like an independent processor but is implemented in software rather than hardware.

[0214] The storage unit 1016 includes a machine-readable medium 1022 having stored thereon one or more sets of data structures and instructions 1024 (e.g., software) that embody or are utilized by any one or more of the methods or functions described herein. The instructions 1024 may also reside, completely or at least partially, within the main memory 1004 or within the processor 1002 during execution by the computer system 1000, where the main memory 1004 and the processor 1002 also each constitute a machine-readable medium 1022.

[0215] Although machine-readable medium 1022 is shown as a single medium in accordance with some examples, the term "machine-readable medium" can include a single medium or multiple media (e.g., a centralized or distributed database, or associated caches and servers) that store one or more instructions 1024 or data structures. The term "machine-readable medium" should also be regarded as including any tangible medium that is capable of storing, encoding, or carrying instructions 1024 for execution by a machine and that causes the machine to perform any one or more of the methods of the present disclosure, or any tangible medium that is capable of storing, encoding, or carrying data structures used by or associated with such instructions 1024. Thus, the term "machine-readable medium" should be regarded as including, but not limited to, solid-state memories as well as optical and magnetic media. Specific examples of machine-readable medium 1022 include non-volatile memories, including, for example, semiconductor memory devices such as erasable programmable read-only memory (EPROM), electrically erasable programmable read-only memory (EEPROM), and flash memory devices; magnetic disks such as internal hard disks and removable disks; magneto-optical disks; and compact disk read-only memory (CD-ROM) and digital versatile disk read-only memory (DVD-ROM) disks. A machine-readable medium is not a transmission medium.

[0216] Instructions 1024 can also be sent or received over a communication network 1026 using a transmission medium. Instructions 1024 can be sent using a network interface device 1020 and any one of a number of well-known transmission protocols (e.g., hypertext transfer protocol (HTTP)). Examples of communication networks include local area networks (LANs), wide area networks (WANs), the Internet, mobile telephone networks, plain old telephone (POTS) networks, and wireless data networks (e.g., Wi-Fi and Wi-Max networks). The term "transmission medium" should be regarded as including any intangible medium that is capable of storing, encoding, or carrying instructions 1024 for execution by a machine, and includes digital or analog communication signals or other intangible media to facilitate the communication of such software.

[0217] Although specific examples have been described herein, it will be apparent that various modifications and changes can be made to these examples without departing from the broader spirit and scope of the present disclosure. Accordingly, the specification and drawings are to be regarded as illustrative rather than restrictive. The accompanying drawings, which form a part hereof, illustrate specific examples in which the subject matter can be practiced by way of illustration and not by way of limitation. The examples shown have been described in sufficient detail to enable those skilled in the art to practice the teachings disclosed herein. Other examples can be utilized and other examples can be derived therefrom, such that structural and logical substitutions and changes can be made without departing from the scope of the present disclosure. Accordingly, this detailed description is not to be taken in a limiting sense, and the scope of the various examples is defined only by the appended claims and the full scope of equivalents to which such claims are entitled.

[0218] These examples of the subject matter of the present invention may be referred to herein individually or collectively by the term "example" merely for convenience and are not intended to voluntarily limit the scope of the present application to any single example or concept if in fact more than one example or concept is disclosed. Thus, although specific examples have been shown and described herein, it should be understood that any arrangement calculated to achieve the same purpose may be substituted for the specific examples shown. The present disclosure is intended to cover any and all adaptations or variations of various examples. Combinations of the above examples and other examples not specifically described herein will be apparent to those skilled in the art after reading the above description.

[0219] Some portions of the subject matter discussed herein may be presented in terms of algorithms or symbolic representations of operations on data stored as bits or binary digital signals within a machine memory (e.g., computer memory). Such algorithms or symbolic representations are examples of techniques used by ordinary technicians in the data processing field to convey the substance of their work to other technicians in the field. As used herein, an "algorithm" is a self-consistent sequence of operations or similar processing that results in a desired outcome. In this context, algorithms and operations involve the physical manipulation of physical quantities. Usually, but not necessarily, such quantities may take the form of electrical, magnetic, or optical signals capable of being stored, accessed, transmitted, combined, compared, or otherwise manipulated by a machine. Sometimes, mainly for general reasons, it is convenient to use words such as "data", "content", "bit", "value", "element", "symbol", "character", "term", "digit", "number" to refer to such signals. However, these words are merely convenient labels and are associated with appropriate physical quantities.

[0220] Unless otherwise specifically stated, discussions herein using words such as "processing", "computing", "operating", "determining", "presenting", "displaying", etc. may refer to actions or processes of a machine (e.g., a computer) that manipulates or transforms data represented as physical (e.g., electronic, magnetic, or optical) quantities within one or more memories (e.g., volatile memory, non-volatile memory, or any suitable combination thereof), registers, or other machine components that receive, store, transmit, or display information. Additionally, unless otherwise specifically stated, as is common in patent literature, the terms "a" and "an" are used herein to include one or more than one instance.

[0221] Unless the context clearly requires otherwise, throughout the specification and claims, the words "comprise", "comprising", and the like shall be construed in an inclusive sense as opposed to an exclusive or exhaustive sense; that is to say, in the sense of "including, but not limited to". As used herein, the term "connected", "coupled", or any variant thereof means any direct or indirect connection or coupling between two or more elements; the coupling or connection between elements may be physical, logical, or a combination thereof. Additionally, when used in this application, the words "herein", "above", "below", and words of similar import refer to the application as a whole and not to any particular part of the application. Where the context permits, words in the singular or plural may also include the plural or singular respectively. Unless otherwise specified, the word "or" in reference to a list of two or more items covers all of the following interpretations of that word: any of the items in the list, all of the items in the list, and any combination of the items in the list.

[0222] Although some examples (e.g., those depicted in the figures) include a particular sequence of operations, the sequence may be changed without departing from the scope of the disclosure. For example, some of the operations depicted may be performed in parallel or in a different order that does not materially affect the functions described in the example. In other examples, different components of an example device or system implementing the example method may perform functions substantially simultaneously or in a particular order. For ease of reference, the term "operation" is used to refer to an element in the figures of the disclosure, and it should be understood that each "operation" may identify one or more operations, processes, actions, or steps and may be performed by one or more components.

Claims

1. A system, comprising: at least one memory storing instructions; and one or more processors configured by the instructions to perform operations, the operations including: identifying a plurality of candidate indexes associated with a database query classified as a slow query; for each candidate index of the plurality of candidate indexes, generating a feature vector representing statement features and statistical features associated with the candidate index; providing the feature vector to one or more machine learning models to obtain an index recommendation value for each candidate index of the plurality of candidate indexes; causing an index recommendation identifying a first index of the plurality of candidate indexes to be presented at a user device, the first index being identified in the index recommendation based at least in part on the index recommendation value obtained for the first index; receiving user input indicating a selection of the first index by the user; and in response to receiving the user input, automatically updating a database schema to include the first index.

2. The system according to claim 1, wherein the operations further include: detecting that a query execution time of the database query exceeds a threshold; and in response to detecting that the query execution time associated with the database query exceeds the threshold: designating the database query as a slow query, and automatically triggering obtaining the index recommendation value.

3. The system according to claim 2, wherein, The database query is one of a plurality of database queries, and the operations further include: using an index recommendation application to monitor a query execution time of each database query of the plurality of database queries, the database query being designated from the plurality of database queries.

4. The system according to claim 1, wherein the operations further include, before presenting the index recommendation at the user device: evaluating a performance of the first index according to a predetermined performance metric; and selecting the first index for inclusion in the index recommendation based on the performance of the first index.

5. The system according to claim 4, wherein evaluating the performance of the first index includes: determining a first query execution time of the database query when executed against a data subset of a database without applying the first index; determining a second query execution time of the database query when executed against the data subset of the database by applying the first index; and comparing the first query execution time with the second query execution time.

6. The system according to claim 4, wherein causing the index recommendation to be presented at the user device includes causing a user interface to be generated that presents the index recommendation along with an indication of the performance of the first index.

7. The system according to claim 1, wherein each candidate index of the plurality of candidate indexes has one or more candidate index columns, and the statement features indicate a position of each candidate index column of the one or more candidate index columns within the database query.

8. The system according to claim 7, wherein, For each of the one or more candidate index columns, the position is encoded in the feature vector by an encoding scheme that indicates the presence or absence of a corresponding query condition involving the candidate index column.

9. The system according to claim 1, wherein, Each of the plurality of candidate indexes has one or more candidate index columns, and the statistical features include at least one of the following: at least one selectivity value associated with the database query, at least one cardinality value associated with the database query, the number of null values in each of the one or more candidate index columns, or the number of rows associated with each of the one or more candidate index columns.

10. The system of claim 1, wherein at least one of the plurality of candidate indexes is a multi-column candidate index, and the feature vector of the multi-column candidate index is generated by: Generating column-specific values indicative of characteristics of respective columns in the multi-column candidate index; Generating multi-column values indicative of combined characteristics of respective columns in the multi-column candidate index; and Combining the column-specific values and the multi-column values.

11. The system of claim 1, wherein the plurality of candidate indexes includes a first subset and a second subset, the first subset includes one or more single-column candidate indexes, and the second subset includes one or more multi-column candidate indexes.

12. The system according to claim 11, wherein, The one or more machine learning models include a first machine learning model and at least one second machine learning model, the first machine learning model being trained to process each feature vector of the one or more single-column candidate indexes, and the at least one second machine learning model being trained to process each feature vector of the one or more multi-column candidate indexes.

13. The system of claim 11, wherein the first index is selected from the first subset, and the index recommendation further identifies a second index among the plurality of candidate indexes, the second index being selected from the second subset and being identified in the index recommendation at least in part based on an index recommendation value obtained for the second index.

14. The system of claim 1, wherein each of the one or more machine learning models is trained using a data set including sample feature vectors, and each sample feature vector is labeled to indicate whether a sample index corresponding to the sample feature vector exists in a sample database.

15. A method, comprising: Identifying, by one or more computing devices, a plurality of candidate indexes associated with a database query classified as a slow query; Generating, by the one or more computing devices, for each of the plurality of candidate indexes, a feature vector representing statement features and statistical features associated with the candidate index; Providing, by the one or more computing devices, the feature vectors to one or more machine learning models to obtain an index recommendation value for each of the plurality of candidate indexes; Cause an index recommendation identifying a first index among the plurality of candidate indexes to be presented by the one or more computing devices, the first index being identified in the index recommendation based at least in part on an index recommendation value obtained for the first index; Receive, by the one or more computing devices, user input indicating a user's selection of the first index; and In response to receiving the user input, automatically update, by the one or more computing devices, a database schema to include the first index.

16. The method according to claim 15, further comprising: Detect, by the one or more computing devices, that a query execution time of the database query exceeds a threshold; and In response to detecting that the query execution time associated with the database query exceeds the threshold: Designate, by the one or more computing devices, the database query as a slow query, and Automatically trigger, by the one or more computing devices, obtaining the index recommendation value.

17. The method according to claim 15, further comprising, before presenting the index recommendation: Evaluate, by the one or more computing devices, a performance of the first index according to a predetermined performance metric; and Based on the performance of the first index, select, by the one or more computing devices, the first index to be included in the index recommendation.

18. A non-transitory computer-readable medium storing instructions that, when executed by one or more processors, cause the one or more processors to perform operations, the operations including: Identify a plurality of candidate indexes associated with a database query classified as a slow query; For each candidate index among the plurality of candidate indexes, generate a feature vector representing statement features and statistical features associated with the candidate index; Provide the feature vector of each candidate index among the plurality of candidate indexes to a machine learning model to obtain an index recommendation value for the candidate index; Cause an index recommendation identifying a first index among the plurality of candidate indexes to be presented at a user device, the first index being identified in the index recommendation based at least in part on an index recommendation value obtained for the first index; Receive user input indicating a user's selection of the first index; and In response to receiving the user input, automatically update a database schema to include the first index.

19. The non-transitory computer-readable medium according to claim 18, the operations further comprising: Detect that a query execution time of the database query exceeds a threshold; and In response to detecting that the query execution time associated with the database query exceeds the threshold: Designate the database query as a slow query, and Automatically trigger obtaining the index recommendation value.

20. The non-transitory computer-readable medium according to claim 18, the operations further comprising, before presenting the index recommendation at the user device: Evaluate a performance of the first index according to a predetermined performance metric; and Based on the performance of the first index, select the first index to be included in the index recommendation.

Images