An Index Recommendation Method and System Based on Cache and Deep Learning

Abstract

An index recommendation method and system based on caching and deep learning, specifically relating to an index recommendation method and system for a computer database based on a caching database and deep learning. To solve the problem that the index recommendation method of a computer database during data query does not utilize historical data, and the index recommendation algorithm cannot determine whether an index needs to be created for a specific column in a certain list of the computer, resulting in a relatively low query accuracy rate. The workload and index configuration of the encoded computer database are merged to obtain a feature vector; the columns and data corresponding to the computer database are inserted into the established caching database for storage; the feature vector is input into a database tuning model established based on a long short-term memory network, and in combination with the caching database, vector B is output. If the i-th element in vector B is 1, an index is created for the i-th column in the computer database list, otherwise no index is created, and data query is completed based on the columns for which indexes are created. It belongs to the field of index recommendation.

Description

Technical Field

[0001] The present invention relates to an index recommendation method and system, and particularly to an index recommendation method and system for a computer database based on a cache database and deep learning, belonging to the field of database index recommendation. Background Art

[0002] In the field of computers, there are currently many tools and research methods that can recommend indexes for databases to quickly find corresponding data in the databases. In a database with a large amount of data, without appropriate indexes for optimized searching, not only the time for executing operations is wasted, but also a large amount of computing resources are wasted. At the same time, unreasonable indexes will also greatly increase the cost of inserting or deleting data in the database. In an ideal state, all columns that are frequently queried should be indexed to optimize query performance. However, when data is inserted or deleted in the database, indexes will bring additional overhead, and creating and maintaining indexes will both incur storage and computing costs. Therefore, simply creating indexes cannot improve the overall efficiency of the database. On the contrary, a set of index combinations that achieve a balance between query performance and maintenance cost needs to be selected.

[0003] An effective database index recommendation system can greatly reduce the work difficulty of database administrators and effectively improve the overall work efficiency of the database. However, when querying computer data, existing computer database index recommendation algorithms fail to make full use of historical data, and existing index recommendation algorithms are relatively complex. There is no relatively perfect index selection algorithm to determine whether a specific column in a certain table of a computer needs to create an index, resulting in the query effect not meeting expectations and the query accuracy being relatively low. Summary of the Invention

[0004] In order to solve the problems that when querying computer data, the index recommendation method of a computer database fails to make full use of historical data, and existing index recommendation algorithms cannot determine whether a specific column in a certain list of a computer needs to create an index, resulting in the query effect not meeting expectations and the query accuracy being relatively low, the present invention further provides an index recommendation method and system based on caching and deep learning.

[0005] The technical solution adopted by the present invention is as follows:

[0006] It includes the following steps:

[0007] S1. Obtain the workload and all index configurations of a computer database at a certain moment a, and encode the workload and index configurations respectively to obtain a workload vector and an index configuration vector;

[0008] S2. Establish a cache database, and insert the columns and data corresponding to the 1000 queries before time a in the computer database into the cache database for storage;

[0009] S3. Merge the workload vector and the index configuration vector to obtain a feature vector;

[0010] S4. Establish a database tuning model based on a long short-term memory network. Input the feature vector into the database tuning model, and combine the cache database in S2 to train the database tuning model, and output vector B, |B| = n. If the i-th element in vector B is 1, 0 ≤ i ≤ n, it means to create an index in the i-th column of the computer database list; otherwise, do not create an index. Train until the upper limit of the number of iterations is reached or the loss remains unchanged to obtain a trained database tuning model;

[0011] S5. Obtain the computer data to be queried, the current workload of the computer database, and all the index configurations of the current computer database. According to S3, obtain the feature vector, input the feature vector into the trained database tuning model in S4, and output vector B. For each element in vector B, if the i-th bit in vector B is 1, 0 ≤ i ≤ n, it means to create an index in the i-th column of the computer database list; otherwise, do not create an index. Complete the data query according to the columns where the indexes are created.

[0012] Further, the encoding method in S1 is one-hot encoding.

[0013] Further, the specific process of S1 is as follows:

[0014] If the computer database has not been queried within seven days before time a, the current workload vector of the computer database is I = [0, …, 0], where |I| = n, and n is the total number of columns of all tables in the computer database; if the first column after sorting all tables has been queried in the most recent k queries, regardless of the number of queries, the workload vector I = [1, 0, …, 0], k < 1000.

[0015] Further, the cache database in S2 is used to store the corresponding lists and data of the query data in the computer database in real time.

[0016] Further, the specific process of S3 is as follows:

[0017] If the computer database has no index configuration currently and has not been queried within seven days before time a, the feature vector V = [0, …, 0], |V| = 2n, where the first n bits represent whether an index is established for each column of the computer list, initially a zero vector, and the last n columns represent the current workload vector I;

[0018] If a query is performed on the first column of the computer database list and an index is established on the first column, then V = [1, 0, …, 0, 1, 0, …, 0], that is, the 1st bit and the (n + 1)th bit are marked as 1.

[0019] Further, the database tuning model in S4 sequentially includes an input layer, a hidden layer, and an output layer.

[0020] An index recommendation system based on caching and deep learning includes a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, it implements any step of an index recommendation method based on caching and deep learning.

[0021] A computer-readable storage medium stores a computer program. When the computer program is executed by a processor, it implements any step of an index recommendation method based on caching and deep learning.

[0022] Beneficial effects:

[0023] The present invention uses a cache database to store in real time the list and data corresponding to the query data in the computer database, that is, integrates all historical data; encodes the workload and all index configurations of the computer database at a certain moment to obtain a workload vector and an index configuration vector, combines the workload vector and the index configuration vector to obtain a feature vector; establishes a database tuning model based on a long short-term memory network, uses deep learning technology for online index recommendation, enables the performance of the computer database to dynamically maintain an optimal state, inputs the feature vector into the database tuning model, uses the cache database to train the database tuning model, queries the computer data together with the database tuning model and historical execution data, outputs a vector B, |B| = n. If the i-th element in the vector B is 1, 0 ≤ i ≤ n, it means to create an index in the i-th column of the computer database list, otherwise, do not create an index, and query data in units of the columns where indexes are created, which can obtain query results more quickly and accurately. Description of the drawings

[0024] Figure 1 is a flowchart of the method of the present invention; Detailed implementation manners

[0025] Detailed implementation manner one: Combine Figure 1 To illustrate this implementation manner, an index recommendation method based on caching and deep learning described in this implementation manner includes the following steps:

[0026] S1. Obtain the workload and all index configurations of a computer database at a certain moment a, encode the workload and index configurations respectively, with the encoding method being one-hot encoding, to obtain a workload vector and an index configuration vector.

[0027] If the computer database has not performed data queries within seven days before moment a, the current workload vector of the computer database is I = [0, …, 0], where |I| = n, and n is the total number of columns of all tables in the computer database; if the first column after sorting all tables (i.e., merging all tables in the computer database into one table) has been queried in the most recent k queries, regardless of the number of queries, then I = [1, 0, …, 0], that is, the first bit is marked as 1, and k < 1000.

[0028] S2. Establish a cache database, insert the columns and data corresponding to the 1000 queries of the computer database before moment a into the cache database for storage. The cache database is used to store the lists and data corresponding to the query data in the computer database in real time. Subsequently, the deep learning model learns and trains through the data in the cache database, and online adjusts the current index configuration.

[0029] S3. Merge the workload vector and the index configuration vector to obtain a feature vector V, |V| = 2n, where the first n bits represent whether an index is established for each column of the computer list, initially being a zero vector, and the last n columns represent the current workload vector I.

[0030] The present invention characterizes (encodes) the environment of the computer database, and at the same time generates continuous data as the computer database is used, and stores these data in the established cache database.

[0031] If the computer database currently has no index configuration and has not been queried within seven days before moment a, the feature vector is V = [0, …, 0], where |V| = 2n; if the first column of the computer database table has been queried and an index has been established for the first column, then V = [1, 0, …, 0, 1, 0, …, 0], that is, the first bit and the (n + 1)-th bit are marked as 1.

[0032] S4. Establish a database tuning model based on a long short-term memory network. The database tuning model sequentially includes an input layer, a hidden layer, and an output layer. Input the feature vector V into the database tuning model, train the database tuning model in combination with the cache database in S2, and output a vector B, |B| = n. If the i-th element in the vector B is 1, 0 ≤ i ≤ n, it means to create an index for the i-th column in the computer database list; otherwise, do not create an index. Train until the upper limit of the number of iterations is reached or the loss remains unchanged to obtain a trained database tuning model.

[0033] The recurrent neural network (RNN) is a neural network for processing sequential data, generally consisting of an input layer, a hidden layer, and an output layer. However, there are problems of vanishing gradients or exploding gradients in RNN, so there are many variants of RNN. The long short-term memory network (LSTM) is one of them. The most commonly used LSTM structure consists of an input gate, neurons, a forget gate, and an output gate. Therefore, the present invention uses the long short-term memory network to construct a database tuning model.

[0034] The database tuning model is trained using a cache database and feature vectors. The database tuning model and historical execution data are used together for querying computer data. Finally, querying data in units of the columns for creating indexes can obtain query results more quickly and accurately.

[0035] S5. When applied to the computer data query task, obtain the computer data to be queried, the current workload of the computer database, and all the index configurations of the current computer database. According to S3, obtain the feature vectors, input the feature vectors into the database tuning model trained in S4, and output vector B. For each element in vector B, if the i-th bit in vector B is 1, 0 ≤ i ≤ n, it means to create an index in the i-th column of the computer database list; otherwise, do not create an index. Complete the data query according to the columns for creating indexes. Optimize the database query performance according to the index configuration recommended by output vector B to improve the efficiency in the actual data query application scenario.

[0036] Specific Embodiment 2: Combine Figure 1 To illustrate this embodiment, the indexing recommendation system based on cache and deep learning described in this embodiment includes a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, it implements any step of the indexing recommendation method based on cache and deep learning.

[0037] Specific Embodiment 3: Combine Figure 1 To illustrate this embodiment, the computer-readable storage medium described in this embodiment stores a computer program. When the computer program is executed by a processor, it implements any step of the indexing recommendation method based on cache and deep learning.

Claims

1. An index recommendation method based on caching and deep learning, characterized in that: It includes the following steps: S1. Obtain the workload and all index configurations of a computer database at a certain moment a, and encode the workload and index configurations respectively to obtain a workload vector and an index configuration vector; S2. Establish a cache database, and insert the columns and data corresponding to the 1000 queries before the moment a of the computer database into the cache database for storage; S3. Merge the workload vector and the index configuration vector to obtain a feature vector. The specific process is as follows: If there is no index configuration in the computer database currently and no query has been made within seven days before the moment a, then the feature vector V = [0,..., 0], |V| = 2n, where the first n bits represent whether each column of the computer list has an index established, initially a zero vector, and the last n columns represent the current workload vector I; If a query has been made in the first column of the computer database list and an index has been established in the first column, then V = [1, 0,..., 0, 1, 0,..., 0], that is, the 1st bit and the (n + 1)th bit are marked as 1; S4. Establish a database tuning model based on a long short-term memory network, input the feature vector into the database tuning model, and train the database tuning model in combination with the cache database in S2, and output a vector B, |B| = n. If the i-th element in the vector B is 1, 0 ≤ i ≤ n, it means to create an index in the i-th column of the computer database list, otherwise, do not create an index. Train until the upper limit of the iteration times is met or the loss remains unchanged to obtain a trained database tuning model; S5. Obtain the computer data to be queried, the current workload of the computer database, and all the index configurations of the current computer database. According to S3, obtain the feature vector, input the feature vector into the trained database tuning model in S4, and output a vector B. For each element in the vector B, if the i-th bit in the vector B is 1, 0 ≤ i ≤ n, it means to create an index in the i-th column of the computer database list, otherwise, do not create an index. Complete the data query according to the columns where the indexes are created.

2. The index recommendation method based on caching and deep learning according to claim 1, characterized in that: The encoding method in S1 is one-hot encoding.

3. The index recommendation method based on caching and deep learning according to claim 2, characterized in that: The specific process of S1 is as follows: If no data query has been made in the computer database within seven days before the moment a, then the current workload vector of the computer database is I = [0,..., 0], where |I| = n, and n is the total number of columns of all tables in the computer database; if a query has been made in the first column after sorting all tables in the recent k queries, regardless of the number of queries, then the workload vector I = [1, 0,..., 0], k < 1000.

4. The index recommendation method based on caching and deep learning according to claim 3, characterized in that: The cache database in S2 is used to store the corresponding lists and data of the query data in the computer database in real time.

5. A method for index recommendation based on caching and deep learning according to claim 4, characterized in that: The database tuning model in S4 successively includes an input layer, a hidden layer and an output layer.

6. An index recommendation system based on caching and deep learning, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the computer program, the steps of the method according to any one of claims 1-5 are implemented.

7. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by the processor, the steps of the method according to any one of claims 1-5 are implemented.

Images