An online index selection method and device based on a neural network and a decay strategy

Abstract

The application discloses an online index selection method and device based on a neural network and an attenuation strategy, and relates to the field of databases.The method comprises the following steps: training a tree convolutional neural network, so that the tree convolutional neural network outputs corresponding execution costs based on a query execution plan; constructing a hash table; obtaining a new value of a value at each position of each array in the hash table based on an attenuation parameter; disabling existing indexes in a database, and obtaining a default execution cost of a new query under a default index configuration based on the tree convolutional neural network; calculating the benefits of indexes on each column in the database to the new query based on the default execution cost of the new query, and adding the benefits to the tail of a corresponding array in the hash table; enabling the existing indexes based on index instructions in the database, and sorting the indexes according to the total benefits, so that a preset number of indexes are selected as new index configurations from high to low. The application can improve the accuracy when calculating the query execution cost.

Description

TECHNICAL FIELD

[0001] The present application relates to the field of database, in particular to an online index selection method and device based on neural network and decay strategy. BACKGROUND

[0002] In recent years, with the entering of big data era, the database system is becoming more and more complex, and the amount of data in it is also increasing. The running database needs to continuously process the queries sent by users, but when there is a large amount of data in the database, it is not easy to ensure that it can have a faster query processing speed.

[0003] Generally, in order to reduce the execution time of queries and improve the running speed of the database, a suitable index configuration can be selected for the database. Index is a physical structure established on columns in the database, which occupies a certain storage space, but can improve the execution efficiency of queries in the database. Index configuration is a set of some indexes. However, it is difficult for database administrators and users to select a better index configuration, because in a larger database, there are usually hundreds of tables and thousands of columns, due to the limitation of storage space, only a small part of columns can be selected to establish indexes, and it is very difficult to select appropriate columns to establish indexes in so many columns. Moreover, since new queries are continuously sent to the database, the database administrator needs to continuously adjust the index configuration for these new queries, and it is more troublesome to select an effective index configuration in such a dynamic environment.

[0004] Index selection problem has always been a hot issue in the field of database, in recent decades, there have been many solutions to deal with this problem, most of these methods use database optimizer to generate the execution plan of the query, and use the estimated execution cost of the query in the execution plan to calculate the benefit of the index. However, the database optimizer cannot accurately estimate the execution cost of the query, which leads to inaccurate estimation of the benefit of the index. Moreover, in the existing index selection methods, most of the methods are used to solve the offline index selection problem. In the offline index selection problem, the environment faced by the method is static, that is, the method needs to optimize a fixed query set and select a suitable index configuration for it. However, as mentioned above, in fact, since queries are continuously coming, there is no fixed query set, so the offline index selection method cannot be used in the real environment. Therefore, how to design an online index selection method which can accurately calculate the benefit of the index and continuously optimize new queries is a problem that needs to be solved at present. SUMMARY

[0005] In view of the defects in the prior art, the purpose of the present application is to provide an online index selection method and device based on a neural network and a decay strategy, so that the accuracy of calculating the query execution cost is higher.

[0006] To achieve the above purpose, the present application provides an online index selection method based on a neural network and a decay strategy, which specifically comprises the following steps:

[0007] Training a tree convolutional neural network so that the tree convolutional neural network outputs the corresponding execution cost based on the query execution plan;

[0008] A hash table is constructed, and the key of the constructed hash table is each indexable column in the database, and the value is an array used to save the benefits of the index on the corresponding column to past queries;

[0009] An attenuation strategy is adopted to obtain a new value of the value at each position of each array in the hash table based on an attenuation parameter;

[0010] The existing indexes in the database are disabled, and the default execution cost of the new query under the default index configuration is obtained based on the tree convolutional neural network;

[0011] Based on the default execution cost of the new query, the benefits of the index on each column in the database to the new query are calculated and added to the tail of the corresponding array in the hash table;

[0012] Based on the index instructions in the database, the existing indexes are enabled, and the indexes are sorted according to the total benefits from high to low, and a preset number of indexes are selected as the new index configuration.

[0013] On the basis of the above technical solution, the tree convolutional neural network is trained so that the tree convolutional neural network outputs the corresponding execution cost based on the query execution plan, and the specific steps include:

[0014] Obtain the executed query execution plan and the corresponding execution cost in the database;

[0015] The obtained query execution plan and execution cost form a data set, and the data in the data set is divided into a training set and a test set according to a preset ratio;

[0016] The training set is used to train the tree convolutional neural network, and the test set is used to judge the training result of the tree convolutional neural network, and when the tree convolutional neural network converges, the trained tree convolutional neural network is obtained.

[0017] On the basis of the above technical solution, the hash table is constructed, and the specific construction process is:

[0018] Traverse all columns and indexes of each table in the database, and take the column without index as the column that can be indexed;

[0019] Assign the column that can be indexed and an empty array to the hash table as a key-value pair one by one to realize the construction of the hash table.

[0020] On the basis of the above technical scheme, the decay strategy is adopted, and a new value of the value at each position of each array in the hash table is obtained based on a decay parameter, and the specific steps include:

[0021] Establish two layers of for loops, the first layer of for loop is used to loop through each array in the hash table, and the second layer of for loop is used to loop through each position in the array;

[0022] Multiply the value of each position of each array in the hash table by the decay parameter, and take the obtained new value as the value of the current position.

[0023] On the basis of the above technical scheme, the default execution cost of the new-to-query under the default index configuration is obtained based on the tree convolutional neural network, and the specific steps include:

[0024] Call the database optimizer to obtain the query execution plan of the new-to-query;

[0025] Input the query execution plan of the new-to-query into the tree convolutional neural network, and the output is the execution cost of the new-to-query under the default index configuration, i.e. the default execution cost.

[0026] On the basis of the above technical scheme, the index benefit of each column in the database on the new-to-query is calculated based on the default execution cost of the new-to-query, and the specific steps include:

[0027] For each column appearing in the new-to-query, a virtual index is established on the current column, and the query execution cost of the current column at this time is calculated based on the database optimizer and the tree convolutional neural network;

[0028] Subtract the query execution cost of the current column at this time from the default execution cost of the new-to-query to obtain the index benefit of the current column on the new-to-query.

[0029] On the basis of the above technical scheme, the benefit is added to the tail of the corresponding array in the hash table, and the specific steps include:

[0030] The benefit of the new-to-query is added to the tail of the corresponding array in the hash table, and the virtual index is deleted, and for the column not appearing in the new-to-query, 0 is added to the tail of the corresponding array in the hash table.

[0031] On the basis of the above technical scheme, after the benefit of the new-to-query is added to the tail of the corresponding array in the hash table, it further includes:

[0032] Judge whether the length of the current array is greater than the preset length, if yes, delete the head element of the current array, if not, do not process.

[0033] On the basis of the above technical scheme, the indexes are sorted according to the total revenue, and a preset number of indexes are selected as new index configurations from high to low, and the specific steps include:

[0034] The sum of each array in the hash table is calculated as the total revenue of the index on the corresponding column;

[0035] The indexes are sorted according to the total revenue, and a preset number of indexes are selected as new index configurations from high to low, and the existing index configuration in the database is replaced with the new index configuration.

[0036] The application provides an online index selection device based on a neural network and a decay strategy, which comprises:

[0037] A training module is used to train a tree convolutional neural network, so that the tree convolutional neural network outputs a corresponding execution cost based on a query execution plan;

[0038] A construction module is used to construct a hash table, and the keys of the constructed hash table are each indexable column in a database, and the values are arrays, which are used to save the revenue of the index on the corresponding column to past queries;

[0039] A first calculation module is used to obtain a new value of the value at each position of each array in the hash table based on a decay parameter by using a decay strategy;

[0040] A disabling module is used to disable the existing index in the database, and obtain a default execution cost of a new query under a default index configuration based on the tree convolutional neural network;

[0041] A second calculation module is used to calculate the revenue of the index on each column in the database to the new query based on the default execution cost of the new query, and add the revenue to the tail of the corresponding array in the hash table;

[0042] An enabling module is used to enable the existing index based on the index instruction in the database, and sort the indexes according to the total revenue, and select a preset number of indexes as new index configurations from high to low.

[0043] Compared with the prior art, the application has the following advantages:

[0044] (1) The application uses a tree convolutional neural network to calculate the execution cost of a query, which uses a detector capable of identifying a specific tree structure, and can capture the special tree structure existing in the query plan, so that the accuracy of calculating the query execution cost is higher;

[0045] (2) The application uses a decay strategy to calculate the total revenue of the index, which ensures that the newer queries have a greater weight in calculating the total revenue of the index, and ultimately a more reasonable total revenue of the index is obtained;

[0046] (3) Compared with the existing index selection method, the application can perform well in a dynamic environment with continuous queries. BRIEF DESCRIPTION OF DRAWINGS

[0047] In order to more clearly illustrate the technical solutions in the embodiments of the present application, the drawings needed in the embodiment description will be briefly introduced. Obviously, the drawings in the following description are only some embodiments of the present application, and other drawings can be obtained by those skilled in the art without creative labor.

[0048] Figure 1 A flowchart of an online index selection method based on a neural network and a decay strategy in an embodiment of the present application;

[0049] Figure 2 A structural schematic diagram of an online index selection device based on a neural network and a decay strategy in an embodiment of the present application. DETAILED DESCRIPTION

[0050] In order to make the purposes, technical solutions and advantages of the embodiments of the present application more clear, the technical solutions in the embodiments of the present application will be described clearly and completely in the following with reference to the drawings in the embodiments of the present application. Obviously, the described embodiments are only some embodiments of the present application, not all embodiments.

[0051] The application uses a tree convolutional neural network to estimate the execution cost of a query. The tree convolutional neural network is developed from a traditional convolutional neural network, which uses a detector that can identify a specific tree structure. Since the execution plan generated by the database optimizer has a tree structure, the tree convolutional neural network can capture some specific structures and obtain more accurate values than the execution cost contained in the execution plan. In a dynamic environment with continuous queries, it is considered that queries close in time have certain relevance, and older queries have less relevance to the latest queries, so the weight of old queries in calculating the index revenue needs to be reduced. Therefore, the application designs a decay strategy for calculating the total revenue of the index in a dynamic environment, which ensures that newer queries have a greater impact on the index revenue, and can more effectively adjust the existing index configuration.

[0052] Referring to Figure 1 The embodiment of the present application provides an online index selection method based on a neural network and a decay strategy, which specifically comprises the following steps:

[0053] S1: training a tree convolutional neural network, so that the tree convolutional neural network outputs a corresponding execution cost, i.e., execution time, based on a query execution plan;

[0054] That is, the execution time of the queries executed by the database is collected, the queries are input into the database optimizer, the execution plans of the queries can be obtained, and the tree convolutional neural network is trained with the execution plans of the queries and the actual execution time. After being trained, the tree convolutional neural network can output an accurate estimated execution cost with the execution plan of the query as input. In the following steps, the tree convolutional neural network will replace the database optimizer to calculate the execution cost of the query.

[0055] In the present application, the tree convolutional neural network is trained so that the tree convolutional neural network outputs a corresponding execution cost based on a query execution plan, and the specific steps include:

[0056] S101: obtaining a query execution plan and a corresponding execution cost in a database;

[0057] S102: forming a data set with the obtained query execution plan and execution cost, and dividing the data in the data set into a training set and a test set according to a preset ratio;

[0058] S103: training the tree convolutional neural network with the training set, and judging the training result of the tree convolutional neural network with the test set, and when the tree convolutional neural network converges, the trained tree convolutional neural network can be obtained.

[0059] In the actual application process, the programming language is Python, and the database is PostgreSQL. The executed queries and the corresponding execution costs in the database can be obtained by searching the log files of the database, and the execution plans of the queries can be obtained by the EXPLAIN command. The data set is formed with the query execution plan and the corresponding execution cost, the data in the data set is divided into a training set and a test set according to a ratio of 9:1, the tree convolutional neural network is trained with the training set, and the training result of the tree convolutional neural network is judged with the test set, and when the tree convolutional neural network converges, the training can be stopped.

[0060] S2: constructing a hash table, and the key of the constructed hash table is each indexable column in the database, and the value is an array, and the array is used to save the benefits of the index on the corresponding column to past queries;

[0061] That is, the preparation work before processing the query is performed, and an empty hash table is created, and the key of the hash table is each indexable column in the database, and the value is an array. For example, the maximum value of the length of each array is w, that is, the array retains the benefits of the index on the corresponding column to the past w queries at most.

[0062] In the present application, a hash table is constructed, and the specific construction process is as follows:

[0063] S201: All columns and indexes of each table in the database are traversed, and the column without index is taken as the column on which the index can be established;

[0064] S202: The column on which the index can be established and an empty array are assigned to the hash table as a key-value pair one by one to realize the construction of the hash table.

[0065] In actual application process, \d+ command is used to view all columns and index conditions of each table in the database one by one, the column without index is taken as the column on which the index can be established, in Python, the hash table is realized by using dict data structure, and the column on which the index can be established and an empty array are assigned to the hash table as a key-value pair one by one to realize the construction of the hash table.

[0066] S3: Adopting decay strategy, the new value of the value at each position of each array in the hash table is obtained based on the decay parameter;

[0067] In the present application, the decay strategy is adopted, and the new value of the value at each position of each array in the hash table is obtained based on the decay parameter, and the specific steps include:

[0068] S301: A two-layer for loop is established, the first layer for loop is used to loop through each array in the hash table, and the second layer for loop is used to loop through each position in the array;

[0069] S302: The value of each position of each array in the hash table is multiplied by the decay parameter, and the obtained new value is taken as the value of the current position.

[0070] That is, the value at each position of each array in the hash table is multiplied by the decay parameter as its new value, and the decay parameter is a value between 0 and 1, and in actual application, the value of the decay parameter can be continuously adjusted according to the actual environment and the effect of the method.

[0071] S4: Disable the existing index in the database, and obtain the default execution cost of the new query under the default index configuration based on the tree convolutional neural network;

[0072] Since the index configuration recommended before exists in the database, and the index configuration will affect the estimation of the execution cost of the query, it is necessary to disable the existing index by calling the instruction in the database. Specifically, the instruction update pg_index set indisvalid=false can be used to disable the index in the database.

[0073] In the present application, the default execution cost of the new query under the default index configuration is obtained based on the tree convolutional neural network, and the specific steps include:

[0074] S401: calling a database optimizer to obtain a query execution plan of the new query;

[0075] S402: inputting the query execution plan of the new query into a tree convolutional neural network, and outputting an execution cost of the new query under a default index configuration, i.e., a default execution cost.

[0076] That is, the execution plan of the new query is obtained by calling the database optimizer using the EXPLAIN command, the execution plan is input into the tree convolutional neural network, and finally the execution cost of the new query under the default index configuration is obtained.

[0077] S5: based on the default execution cost of the new query, calculating the benefit of the index on each column in the database to the new query, and adding the benefit to the tail of the corresponding array in the hash table.

[0078] In the present application, based on the default execution cost of the new query, the benefit of the index on each column in the database to the new query is calculated, and the specific steps include:

[0079] S501: for each column appearing in the new query, a virtual index is established on the current column, and the query execution cost of the current column at this time is calculated based on the database optimizer and the tree convolutional neural network.

[0080] S502: subtracting the query execution cost of the current column at this time from the default execution cost of the new query to obtain the benefit of the index on the current column to the new query.

[0081] In actual application process, for each column appearing in the new query, a virtual index is established on this column using HypoPG (https: / / github.com / HypoPG / hypopg) plug-in, and then the execution cost of the new query under the condition that the index exists is obtained according to step S4, and then the value obtained in step S4 is subtracted from the value obtained in this step, which is the benefit of the index to the new query.

[0082] In the present application, the benefit is added to the tail of the corresponding array in the hash table, and the specific steps are as follows:

[0083] The benefit of the new query is added to the tail of the corresponding array in the hash table, and the virtual index is deleted, and for the column not appearing in the new query, 0 is added to the tail of the corresponding array in the hash table.

[0084] The benefit of the new query can be added to the tail of the corresponding array in the hash table using the append method of the list in Python.

[0085] In a possible implementation, after adding the benefit of the new query into the tail of the corresponding array in the hash table, the following is further included:

[0086] It is judged whether the length of the current array is greater than the preset length, if yes, the head element of the current array is deleted, if not, no processing is performed.

[0087] Since new elements are added into each array of the hash table, the length of the current array can be greater than w, therefore, if the length of the current array is greater than w, the head element of the array is deleted. The head element is deleted without deleting other elements because the head element is the index pair of the oldest query benefit.

[0088] The length of the array can be obtained by using the len method, and if the length is greater than w, the head element of the array is deleted by using the del statement.

[0089] S6: enabling the existing index based on the index instruction in the database, and sorting the index according to the total benefit, and selecting a preset number of indexes from high to low as a new index configuration.

[0090] Since the benefit of the index on each column to the newly arrived query has been calculated, the instruction in the database can be called to enable the existing index. Specifically, the update pg_index set indisvalid=true instruction can be used to enable the existing index in the database.

[0091] In the present application, the index is sorted according to the total benefit, and a preset number of indexes from high to low are selected as a new index configuration, and the specific steps include:

[0092] S601: calculating the sum of each array in the hash table as the total benefit of the index on the corresponding column;

[0093] S602: sorting the index according to the total benefit, selecting a preset number of indexes from high to low as a new index configuration, and replacing the existing index configuration in the database with the new index configuration.

[0094] In actual application process, the sum of each array in the hash table is calculated by using the sum statement, and then sorted by using the sort statement, and then a preset number of indexes from high to low are selected as a new index configuration. Compared with the old index configuration, the CREATE statement and the DELETE statement of the database are used to change the existing index configuration, so as to transfer the index configuration in the database to the new index configuration.

[0095] It should be noted that when a new query needs to be processed, steps S3-S6 are repeated.

[0096] The online index selection method based on the neural network and the attenuation strategy of the application comprehensively considers the collection of data, the training of the tree convolutional neural network, the construction, the calculation of the index benefit using the attenuation strategy, the recommendation of the new index configuration and the change of the index configuration.

[0097] In the application, the tree convolutional neural network has a detector capable of recognizing a structural pattern and can observe a special structure in a query execution plan, and has higher accuracy compared with a method of calculating a query cost using a database optimizer.

[0098] The attenuation parameter is manually set, and the value is between 0 and 1; when calculating the total benefit of the index, the benefit of the index obtained from past queries is multiplied by the attenuation parameter; the older the query, the more times the attenuation parameter is multiplied, so that the influence of the newer query on the total benefit of the index is greater, and the change trend of future queries can be captured.

[0099] Data is collected from a log file of a database to train the tree convolutional neural network; a hash table is used to save the benefit of the index to past queries, and provides a data structure required by the attenuation strategy; before calculating the index benefit, the existing index configuration in the database is disabled to avoid its influence on the calculation of the index benefit, and the index configuration is enabled after the calculation is completed.

[0100] For the online index selection method based on the neural network and the attenuation strategy of the application, first, the application proposes a method of using a tree convolutional neural network to calculate a query execution cost, and compared with a traditional convolutional neural network, the tree convolutional neural network uses a detector capable of recognizing a specific tree structure. Therefore, it can capture some specific tree structures in a query plan, and finally has higher accuracy in calculating the execution cost of the query; second, the application designs an attenuation strategy for calculating the total benefit of the index in a dynamic environment, and the dynamic environment is an environment in which queries continuously arrive, and in this environment, queries arriving at similar times are often similar, so the weight of old queries in calculating the index benefit needs to be reduced, and the attenuation strategy can meet this requirement; finally, the application combines the above two aspects and designs a complete method capable of dynamically adjusting the index configuration according to new queries.

[0101] In a possible implementation, the embodiment of the application further provides a readable storage medium located in a PLC (Programmable Logic Controller, programmable logic controller) controller, and a computer program is stored on the readable storage medium, and the program is executed by a processor to implement the steps of the online index selection method based on the neural network and the attenuation strategy as follows:

[0102] The tree convolutional neural network is trained so that the tree convolutional neural network outputs a corresponding execution cost based on a query execution plan;

[0103] A hash table is constructed, and a key of the constructed hash table is each indexable column in the database, and a value is an array used to save a benefit of an index on the corresponding column to a past query;

[0104] An attenuation strategy is adopted, and a new value of a value at each position of each array in the hash table is obtained based on an attenuation parameter;

[0105] An existing index in the database is disabled, and a default execution cost of a new query under a default index configuration is obtained based on the tree convolutional neural network;

[0106] Based on the default execution cost of the new query, a benefit of an index on each column in the database to the new query is calculated and added to a tail of a corresponding array in the hash table;

[0107] Based on an index instruction in the database, the existing index is enabled, and the indexes are sorted according to the total benefits from high to low, and a preset number of indexes are selected as a new index configuration.

[0108] The storage medium can adopt any combination of one or more computer-readable media. The computer-readable medium can be a computer-readable signal medium or a computer-readable storage medium. The computer-readable storage medium may, for example, but is not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, device, or apparatus, or any combination thereof. More specific examples (a non-exhaustive list) of the computer-readable storage medium include an electrical connection having one or more wires, a portable computer diskette, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the above. In this document, the computer-readable storage medium can be any tangible medium that contains or stores a program that can be used by or in connection with an instruction execution system, device, or apparatus.

[0109] A computer readable signal medium can include a propagated data signal with computer executable prograrn code embodied therein, for example, in baseband or as part of a carrier wave. Such a propagated signal can take any of a variety of forms, including, but not limited to, electro-magnetic, optical, or any suitable combination thereof. A computer readable signal medium can be any computer readable medium that can be

[0110] Computer program code for carrying out operations of the present application can be written in any combination of one or more programming languages, including an object oriented programming language such as Java, Smalltalk, C++ or the like and conventional procedural programming languages, such as the "C" programming language or similar programming languages. The program code can execute entirely on the user's computer, partly on the user's computer, as a stand-alone software package, partly on the user's computer and partly on a remote computer or entirely on the remote computer or server. In the latter scenario, the remote computer can be connected to the user's computer through any type of network, including a local area network (LAN) or a wide area network (WAN), or the connection can be made to an external computer (for example, through the Internet using an Internet Service Provider).

[0111] Referring to Figure 2 As shown in the figure, the embodiment of the present application provides an online index selection device based on neural network and attenuation strategy, which comprises a training module, a construction module, a first calculation module, a disabling module, a second calculation module and an enabling module.

[0112] The training module is configured to train the tree convolutional neural network to output a corresponding execution cost based on a query execution plan; the construction module is configured to construct a hash table, and a key of the constructed hash table is each indexable column in a database, and a value is an array configured to save a benefit of an index on the corresponding column to a past query; the first calculation module is configured to obtain a new value of a value at each position of each array in the hash table based on a decay parameter using a decay strategy; the disabling module is configured to disable an existing index in the database, and obtain a default execution cost of a new query under a default index configuration based on the tree convolutional neural network; the second calculation module is configured to calculate a benefit of an index on each column in the database to the new query based on the default execution cost of the new query, and add the benefit to a tail of a corresponding array in the hash table; and the enabling module is configured to enable the existing index based on an index instruction in the database, and sort the indexes according to a total benefit, and select a preset number of indexes from high to low as a new index configuration.

[0113] The above only describes the specific embodiments of the present application, so that those skilled in the art can understand or implement the present application. Various modifications to these embodiments will be apparent to those skilled in the art, and the general principles defined herein can be implemented in other embodiments without departing from the spirit or scope of the present application. Therefore, the present application will not be limited to these embodiments shown herein, but will conform to the widest scope consistent with the principles and novel features sought to be applied herein.

[0114] The present application is described with reference to flowcharts and / or block diagrams of the method, device (system) and computer program product according to the embodiments of the present application. It should be understood that each flow and / or block in the flowcharts and / or block diagrams, and the combination of the flows and / or blocks in the flowcharts and / or block diagrams can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing apparatus to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing apparatus produce a means for implementing the functions specified in the flowcharts and / or block diagrams. Figure 1 The means for implementing the functions specified in the flowcharts and / or block diagrams Figure 1 The means for implementing the functions specified in the flowcharts and / or block diagrams

Claims

1. A neural network and decay policy based online index selection method, characterized in that, Specifically comprising the following steps: training a tree convolutional neural network, so that the tree convolutional neural network outputs a corresponding execution cost based on a query execution plan; constructing a hash table, and the key of the constructed hash table is each indexable column in the database, and the value is an array used to save the benefits of indexes on the corresponding column to past queries; adopting a decay strategy to obtain a new value of the value at each position of each array in the hash table based on a decay parameter; disabling existing indexes in the database, and obtaining a default execution cost of a new query under a default index configuration based on the tree convolutional neural network; based on the default execution cost of the new query, calculating the benefits of indexes on each column in the database to the new query, and adding the benefits to the tail of the corresponding array in the hash table; based on the index instructions in the database, enabling the existing indexes, and sorting the indexes according to the total benefits from high to low, and selecting a preset number of indexes as a new index configuration; wherein the adopting a decay strategy to obtain a new value of the value at each position of each array in the hash table based on a decay parameter, the specific steps comprising: establishing two layers of for loops, the first layer of for loop is used to loop through each array in the hash table, and the second layer of for loop is used to loop through each position in the array; multiplying the value at each position of each array in the hash table by the decay parameter to obtain a new value as the value at the current position.

2. The online index selection method based on neural network and decay strategy according to claim 1, wherein, The training of the tree convolutional neural network, so that the tree convolutional neural network outputs a corresponding execution cost based on a query execution plan, the specific steps comprising: obtaining the query execution plan and the corresponding execution cost of the executed query in the database; composing the obtained query execution plan and execution cost into a data set, and dividing the data in the data set into a training set and a test set according to a preset proportion; training the tree convolutional neural network using the training set, and using the test set to judge the training result of the tree convolutional neural network, and when the tree convolutional neural network converges, the trained tree convolutional neural network is obtained.

3. The method of claim 1, wherein the method further comprises: The specific construction process of the hash table is: traversing all columns and indexes of each table in the database, and taking the column without index as the indexable column; assigning the indexable column and an empty array as a key-value pair to the hash table one by one to realize the construction of the hash table.

4. The method of claim 1, wherein the method further comprises: The specific steps of obtaining the default execution cost of the new query under the default index configuration based on the tree convolutional neural network comprise: calling the database optimizer to obtain the query execution plan of the new query; inputting the query execution plan of the new query into the tree convolutional neural network, and the output is the execution cost of the new query under the default index configuration, i.e. the default execution cost.

5. The method of claim 1, wherein the method further comprises: The specific steps of calculating the benefits of indexes on each column in the database to the new query based on the default execution cost of the new query comprise: for each column appearing in the new query, a virtual index is established on the current column, and the query execution cost of the current column at this time is calculated based on the database optimizer and the tree convolutional neural network; subtracting the query execution cost of the current column at this time from the default execution cost of the new query to obtain the benefits of the index on the current column to the new query.

6. The method of claim 5, wherein the method further comprises: The revenue of the new query is added to the tail of the corresponding array in the hash table, and the virtual index is deleted, and 0 is added to the tail of the corresponding array in the hash table for columns that do not appear in the new query. After adding the revenue of the new query to the tail of the corresponding array in the hash table, the following is also included:

7. The method of claim 6, wherein the method further comprises: Determine whether the length of the current array is greater than the preset length, if yes, delete the head element of the current array, if not, do not process. The total revenue of each array in the hash table is calculated and used as the total revenue of the index on the corresponding column.

8. The method of claim 1, wherein the method further comprises: The total revenue of each array in the hash table is calculated and used as the total revenue of the index on the corresponding column. It includes: A training module for training a tree convolutional neural network to output a corresponding execution cost based on a query execution plan; 9. An online index selection apparatus based on neural networks and decay policies, characterized in that, A construction module for constructing a hash table, and the keys of the constructed hash table are each indexable column in the database, and the values are arrays used to save the revenue of the index on the corresponding column to past queries; A first calculation module for using a decay strategy to obtain a new value of the value at each position in each array in the hash table based on a decay parameter; A disabling module for disabling existing indexes in the database and obtaining a default execution cost of a new query under a default index configuration based on the tree convolutional neural network; A second calculation module for calculating the revenue of the index on each column in the database based on the default execution cost of the new query, and adding the revenue to the tail of the corresponding array in the hash table; An enabling module for enabling existing indexes based on index instructions in the database, and sorting the indexes according to the total revenue from high to low to select a preset number of indexes as a new index configuration; The decay strategy is used to obtain a new value of the value at each position in each array in the hash table based on a decay parameter, and the specific steps include: Establish a two-layer for loop, the first layer for loop is used to loop through each array in the hash table, and the second layer for loop is used to loop through each position in the array; Multiply the value of each position in each array in the hash table by the decay parameter to obtain a new value as the value of the current position. ​ ​

Images