Index recommendation method and apparatus, electronic device, and storage medium

Abstract

Embodiments of the present application disclose an index recommendation method, an index recommendation device, an electronic device, a chip and a computer readable storage medium, wherein the method comprises: constructing a DQN model; the DQN model comprises: a first channel and a second channel; training the DQN model based on first information and second information of a workload of a database; wherein the workload comprises a plurality of historical query statements; the first information represents column selection degrees in the workload; the second information represents column query types of the workload; accordingly, the first information is used to train the first channel, and the second information is used to train the second channel; and index recommendation is performed based on the trained DQN model.

Description

Technical Field

[0001] This application relates to the field of database technology, and in particular to an index recommendation method, an index recommendation device, an electronic device, a chip, and a computer-readable storage medium. Background Technology

[0002] An index is a data structure designed to improve database system performance. Index recommendation is one of the most important topics in database tuning, primarily studying how to select and recommend a set of indexes to optimize database performance given constraints such as SQL query traffic, database size, and the number of indexes. While index building brings benefits, it also incurs additional disk overhead and index maintenance costs. Poorly constructed indexes not only fail to improve query speed but also increase costs. Therefore, this task is currently mostly performed by database administrators (DBAs). How to automate index recommendation using models has been a hot research topic in the industry. Summary of the Invention

[0003] This application provides an indexing recommendation method, apparatus, electronic device, chip, and computer-readable storage medium.

[0004] The technical solution of this application embodiment is implemented as follows:

[0005] In a first aspect, embodiments of this application provide an index recommendation method, including:

[0006] Construct a DQN model; the DQN model includes: a first channel and a second channel;

[0007] The DQN model is trained based on first and second information about the workload of the database; wherein the workload includes multiple historical query statements; the first information represents the column selectivity in the workload; the second information represents the column query types of the workload; correspondingly, the first information is used to train the first channel, and the second information is used to train the second channel.

[0008] Index recommendation is based on the trained DQN model.

[0009] Secondly, embodiments of this application provide an index recommendation device, comprising:

[0010] Building unit: used to build a DQN model; the DQN model includes: a first channel and a second channel;

[0011] Training unit: used to train the DQN model based on first and second information of the workload of the database; wherein, the workload includes multiple historical query statements; the first information represents the column selectivity in the workload; the second information represents the column query type of the workload; correspondingly, the first information is used to train the first channel, and the second information is used to train the second channel.

[0012] Recommendation Unit: Used for index recommendation based on the trained DQN model.

[0013] Thirdly, this application provides an electronic device, including: a processor and a memory, the memory being used to store a computer program, and the processor being used to call and run the computer program stored in the memory to execute any of the indexing recommendation methods provided in the embodiments of this application.

[0014] Fourthly, this application provides a chip, including: a processor, for calling and running a computer program from a memory, causing a device equipped with the chip to execute any of the indexing recommendation methods provided in the embodiments of this application.

[0015] Fifthly, this application provides a computer-readable storage medium for storing a computer program that causes a computer to execute any of the indexing recommendation methods provided in the embodiments of this application.

[0016] The index recommendation method provided in this application takes into account the impact of different query types and selectivity on index selection in the WHERE condition. It features the selectivity and query type of candidate columns in the database query statement, trains and guides a deep reinforcement learning model to find the optimal index recommendation, and can explore more globally optimal combinations. Attached Figure Description

[0017] Figure 1 This is the architecture diagram for Solution A;

[0018] Figure 2 This is the architecture diagram for Solution B;

[0019] Figure 3 A schematic diagram illustrating the implementation flow of the index recommendation method provided in this application embodiment;

[0020] Figure 4 A schematic diagram of the framework of the DQN model provided in the embodiments of this application;

[0021] Figure 5 A schematic diagram of the structure of the index recommendation device 500 provided in the embodiments of this application;

[0022] Figure 6 A schematic structural diagram of an electronic device provided in the embodiments of this application;

[0023] Figure 7 This is a schematic structural diagram of the chip provided in an embodiment of this application. Detailed Implementation

[0024] The technical solutions of the embodiments of this application will now be described with reference to the accompanying drawings. Obviously, the described embodiments are only a part of the embodiments of this application, and not all of them. All other embodiments obtained by those skilled in the art based on the embodiments of this application without creative effort are within the scope of protection of this application.

[0025] It should be noted that, in the embodiments of this application, the term "and / or" is merely a description of the relationship between related objects, indicating that three relationships can exist. For example, A and / or B can represent: A existing alone, A and B existing simultaneously, and B existing alone. Additionally, in the embodiments of this application, the character " / " generally indicates that the preceding and following related objects have an "or" relationship.

[0026] In the description of the embodiments of this application, the term "correspondence" may indicate that there is a direct or indirect correspondence between two things, or that there is an association between two things, or that there is a relationship of instruction and being instructed, configuration and being configured, etc.

[0027] To facilitate understanding of the technical solutions of the embodiments of this application, the relevant technologies of the embodiments of this application are described below. The following relevant technologies are optional solutions and can be combined with the technical solutions of the embodiments of this application in any way, and they all fall within the protection scope of the embodiments of this application.

[0028] Option A: Figure 1 As shown, Figure 1 The architecture diagram for Solution A shows the database workload as input. In the first iteration, only single-column index search is considered. In subsequent iterations, single-column and multi-column searches are considered sequentially. Each iteration involves three steps: candidate index generation, optimal index selection, and multi-column index generation. Candidate index generation selects all indexable items for a specific workload as candidate indexes. This avoids adding false indexes to the candidate set. For example, in "select * from t where ta = 1", 'a' is a candidate index, while 'b' and 'c', which do not appear, are not candidate indexes. A "Whatif" index creation and cost evaluation are used, and a greedy algorithm is selected to optimally choose the index from all candidate indexes, using the index with the greatest cost improvement as the target index. Multi-column index generation generates composite indexes such as (a, b) and (a, c).

[0029] Option B: As Figure 2 As shown, Figure 2The architecture diagram for Solution B consists of four modules: an SQL collection module (for collecting database SQL load and SQL call frequency information); a rule parsing module (for parsing SQL statements using database expert experience rules (i.e., preset rules) to generate an index candidate set); an index recommendation parameter energy consumption reward calculation module (for calculating the merits of the currently generated index recommendation control exploration parameters on the database workload); and an index recommendation exploration optimization module (for interacting with the database, exploring the index candidate set, and minimizing the database workload cost corresponding to the recommended index under certain constraints). The steps for generating the recommended index are as follows: Step 1: Collect user SQL queries and the frequency of each SQL query from the database; Step 2: Based on the SQL queries in Step 1, use the rule parsing module to generate an index candidate set; Step 3: Convert the index candidate set from Step 2 into a database index recommendation state parameter vector; Step 4: Input the database index recommendation state parameter vector from Step 3 into the index recommendation exploration and optimization module; Step 5: The Rainbow algorithm in the index recommendation exploration and optimization module receives the state parameter vector, outputs an action, and converts this action into an item in the index candidate set that needs to be indexed; Step 6: Based on the index item selected in Step 5, create an index for this item in the data indexing plugin; Step 7: Call the reward calculation module to calculate the reward R for this action and store it as a (S,A,R,S) tuple; Step 8: Optimize the Rainbow control parameter exploration and optimization model based on accumulated interaction experience. Scheme B is a two-stage method based on a deep reinforcement learning model. First, five heuristic rules are designed to generate index candidates. These rules can reduce the dimensionality of the action space and state space in the deep reinforcement learning model and support the simultaneous recommendation of single-attribute and multi-attribute indexes. Secondly, based on the workload candidate index set, a deep reinforcement learning model is trained to select a suitable candidate subset as the recommended index.

[0030] Scheme A is essentially a search framework based on a greedy algorithm. This method has two main drawbacks: First, the greedy algorithm is not an optimal algorithm and is prone to getting trapped in local optima, failing to find the best index. Second, this scheme does not consider the impact of index interactions. For example, the two indexes (a,b) and (a,c) are not independent indexes; they share the common prefix 'a'. Adding (a,b) will affect the cost of (a,c). Scheme B does not consider the selectivity of the candidate index set in the WHERE clause and the impact of query type on index selection. These two pieces of information have a significant impact on index selection. For example, in the query `SELECT * FROM tb1 WHERE a=1 AND b=2 AND c>3`, if 'a' appears the least frequently in tb1 (best selectivity), then building an index on 'a' will yield the highest benefit. If 'c' is a non-equality query and appears at the beginning of the index, then subsequent indexes will not be usable. In other words, the benefit of the (a,c) index is higher than that of the (b,c) and (c,a) indexes.

[0031] The indexing recommendation method provided in this application can be applied to databases such as MySQL, Oracle, SQL Server, SQLite, INFORMIX, Redis, MongoDB, HBase, Neo4J, and CouchDB, but this application does not limit it.

[0032] Figure 3 This is a schematic diagram illustrating the implementation process of the index recommendation method provided in the embodiments of this application, as shown below. Figure 3 As shown in the figure, this application provides an index recommendation method, which includes the following steps:

[0033] Step 301: Construct a DQN model; the DQN model includes: a first channel and a second channel.

[0034] Here, the first channel and the second channel can be independent channels. The first channel is used to extract the column selectivity feature in the workload of the input DQN model, and the second channel is used to extract the column query type feature in the workload of the input DQN model. The features output by the two channels are then fused.

[0035] In practical applications, both the first and second channels can be configured with neural networks to extract features. The parameters of the neural network can be flexibly defined according to the parameters in the database. This application does not impose any restrictions on this. Finally, the output information of the two channels is fused through a fully connected layer.

[0036] For example, refer to Figure 4 , Figure 4 This is a schematic diagram of the framework of the DQN model provided in the embodiments of this application, as shown below. Figure 4As shown, DB represents the database, Env represents the environment, the two NNs represent two neural networks, Dense represents a fully connected layer, and workload represents the workload. The top channel is the first channel, and the bottom channel is the second channel. Column selectivity is the most important basis for evaluating the execution performance of the database cost model. The smaller the selectivity, the greater the discriminative power of the column, and the higher the benefit brought by the index. Therefore, we input the column selectivity information as a parameter into the neural network, which then uses the neural network as the basis for selecting the index. The column selectivity information of the workload can be obtained through the following formula: in, The first channel extracts features from column selectivity information using a neural network, outputting a feature vector A of column selectivity. sel =(Index1(C0), Index1(C1),..., Index1(Cn)).

[0037] The query type of a column in a query statement directly affects whether that column can use an index for speed-up, and how good the speed-up effect is. The column query type information for the workload can be obtained using the following formula: The value of V(Qi, Ci) is determined based on the query type in statement Qi targeting column Ci in the database.

[0038] For example, using an SQL database, refer to Table 1.

[0039] Table 1

[0040] type meaning value Like The column in the query uses the LIKE syntax. 0，1 Range The column in the query is a range syntax. 0，1 In The column in the query is an IN clause. 0，1 Equal The column in the query uses the equality syntax. 0，1 Join This column is a related field in the query. 0，1 GroupOrder The column in the query is either a group by or order by syntax. 0，1 Select The column in the query is a select column syntax. 0，1 other Other grammar not listed in the above 7 items 0，1

[0041] Table 1 defines the meaning and values ​​of eight query types. V(Qi, Ci) can be obtained using the following formula: V (Qi，Cj) = (Other, Like, Range, In, Equal, Join, GroupOrder, Select). When the query type of a column in the query statement is Like, the value of Like in V(Qi, Ci) is 1, otherwise the value is 0.

[0042] According to the Samsung index theory, equality indexes have greater benefits, while range indexes have relatively smaller benefits. In indexes can be converted into equality combinations to utilize the index. In composite indexes, the index after a range query cannot be used to speed up access.

[0043] The second channel uses a neural network to extract features from the column query type information, inputting the feature vector A of the column query type. t =(Index2(C0), Index2(C1),..., Index2(Cn)).

[0044] Information from Asel and At is fused using two fully connected layers to obtain the final result A. eval =(Index3(C0),Index3(C1),...,Index3(Cn)), select the index with the highest priority for recommendation.

[0045] The reward ultimately determines the accuracy of DQN network training. This value is strongly correlated with the target database. According to the embodiment of this application, the reward value ranges between (0, 1), which can be directly normalized. If the simulation execution time is smaller under the current index recommendation, then the total reward will be larger. When there is no index recommendation, the reward value is 0.

[0046] In this embodiment of the application, the action is defined as follows: Action = {create_index_on(C j ),delete_index_on(C j In index recommendation, only two operations need to be considered: creating an index on a given column and deleting an index on a given column. This improves the performance of the model in selecting indexes.

[0047] Step 302: Train the DQN model based on the first and second information of the database workload; wherein, the workload includes multiple historical query statements; the first information represents the column selectivity in the workload; the second information represents the column query type of the workload; correspondingly, the first information is used to train the first channel, and the second information is used to train the second channel.

[0048] In this embodiment of the application, before training the DQN model based on the first and second information of the database workload, the method further includes setting the first, second, third, and fourth parameters of the DQN model; wherein...

[0049] The first parameter represents the number of training rounds;

[0050] The second parameter represents the number of indices predicted in each round of training;

[0051] The third parameter represents the number of query statements required in each round of training;

[0052] The fourth parameter represents the number of training iterations in each epoch.

[0053] In this embodiment of the application, training the DQN model based on the first and second information of the database workload includes:

[0054] The DQN model is trained for the number of rounds corresponding to the first parameter; wherein each round of training includes:

[0055] Input the first and second information of the first workload; the first workload includes the number of historical query statements corresponding to the third parameter;

[0056] Based on the first information of the first workload, a first vector is obtained through the first channel, and the first vector represents the column selectivity feature of the database;

[0057] Based on the second information, a second vector is obtained through the second channel, and the second vector represents the column query type characteristics of the database;

[0058] The first vector and the second vector are fused to obtain a third vector, and each element in the third vector represents the index preference of each column in the database.

[0059] Select the column with the highest preference as the candidate index;

[0060] The training round ends when the number of candidate indices reaches the number corresponding to the second parameter.

[0061] It should be noted that the first workload may be the same, partially the same, or completely different in different rounds or in the same round of training, and this application does not limit this.

[0062] In this embodiment of the application, the first information is obtained through the following formula: Among them, I sel For the first information,

[0063] The second information is obtained through the following formula: Here, It represents the second information, and the value of V(Qi, Ci) is determined based on the query type in statement Qi targeting column Ci in the database.

[0064] In this embodiment of the application, after inputting the first and second information of the first workload, the method further includes:

[0065] Preprocessing operations are performed on the first information and the second information of the first workload respectively, processing the first information of the first workload into a two-dimensional matrix and processing the second information into a floating-point number.

[0066] Specifically, Isel has a dimension of (X, features, 1). Flattening I_sel into a two-dimensional matrix results in an output dimension of (X, features). It has a dimension of (X, features, 8). Connecting it to a fully connected layer M_8,1 results in an output dimension of (X, features). This operation compresses the 8-dimensional data into a floating-point number, describing the type characteristics. Here, X represents the input data, which can be any number of query statements.

[0067] In reinforcement learning models, the definition of reward is crucial to recommendation performance. In this application, to achieve optimal index recommendation, the objective is to minimize the total execution time of the target traffic.

[0068] In this embodiment of the application, before training the DQN model based on the first and second information of the database workload, the method further includes:

[0069] Set a sixth parameter, which indicates whether a column in the database has been used as a candidate index;

[0070] Set the reward decay factor e_greedy, defined as:

[0071] e_greedy = min{episode×24÷S+e_greedy,a}, where episode is the current training round number, S is the daily workload of the database, and 0 < a < 1.

[0072] For example, the sixth parameter can be defined as I index = (Index(C0), Index(C1), ..., Index(Cn)), where the value of column Cj in Index is 0 when it is not a candidate index, and 1 when it is a candidate index. By setting the sixth parameter, the model can be guided not to select columns that have already been indexed as the target for the next action, thus improving the efficiency of index recommendation.

[0073] Since the model lacks experience in the early stages, it is prone to getting stuck in local optima, resulting in slow convergence. To mitigate this issue, a reward decay factor is set. Assuming the target database workload is S sql / day, we use a random method related to workload for index selection, which is later updated to a fixed value, corresponding to the definition of e_greedy, where episode is the current training epoch. reward_decay defines the reward decay factor in reinforcement learning; preferably, 'a' can be 0.9.

[0074] Step 303: Perform index recommendation based on the trained DQN model.

[0075] In practical applications, the indexes generated during model training can be used for recommendations, with the recommended indexes using historical data from the database. Alternatively, the current database workload can be used as input to the DQN model, and the trained DQN model can then perform index recommendations based on the current database workload.

[0076] The index recommendation method provided in this application proposes a dual-channel prioritized replay DQN model. This model combines the selectivity of candidate columns in the query statement with the query type, optimizes and adjusts the input content of each channel, and proposes a method for encoding the selectivity of index columns and the query type in the query statement. This encoding method is applied to a deep reinforcement learning model to guide the optimal index recommendation. In addition, this application adjusts the reward according to the index scenario. The smaller the simulation execution time, the larger the total reward. The impact of index interaction is taken into account, unlike greedy algorithms that cannot consider the interaction between indexes. This reward can accurately reflect the cost evaluation of the database environment, and the model can select candidate indexes with higher cost evaluation.

[0077] This application embodiment also provides an index recommendation device 500, for reference... Figure 5 The index recommendation device 500 in this embodiment includes:

[0078] Construction unit 510: used to construct a DQN model; the DQN model includes: a first channel and a second channel;

[0079] Training unit 520: used to train the DQN model based on first and second information of the workload of the database; wherein, the workload includes multiple historical query statements; the first information represents the column selectivity in the workload; the second information represents the column query type of the workload; correspondingly, the first information is used to train the first channel, and the second information is used to train the second channel.

[0080] Recommendation Unit 530: Used for index recommendation based on the trained DQN model.

[0081] In this embodiment of the application, the processing unit 220 is specifically used to encode the first text through a shared encoder to obtain a first vector; and based on the first vector, to obtain an intent recognition result, a slot extraction result, and a text correction result.

[0082] In this embodiment of the application, the Reward function of the DQN model is defined as: Where Cost(Q, Xt) represents the simulated execution time of traffic Q with the current recommended index included, and Cost(Q, X0) represents the simulated execution time of traffic Q without any index.

[0083] In this embodiment of the application, the construction unit 510 is further configured to set a first parameter, a second parameter, a third parameter, and a fourth parameter of the DQN model; wherein, the first parameter represents the number of training epochs; the second parameter represents the number of indexes predicted in each training epoch; the third parameter represents the number of query statements required in each training epoch; and the fourth parameter represents the number of training iterations in each epoch.

[0084] In this embodiment, the training unit 520 is specifically used to train the DQN model in rounds corresponding to the first parameter. Each round of training includes: inputting first and second information of a first workload; the first workload includes a number of historical query statements corresponding to the third parameter; based on the first information of the first workload, obtaining a first vector through the first channel, the first vector representing the column selectivity feature of the database; based on the second information, obtaining a second vector through the second channel, the second vector representing the column query type feature of the database; fusing the first vector and the second vector to obtain a third vector, each element of the third vector representing the index preference of each column of the database; selecting the column with the highest preference as a candidate index; and ending the training round when the number of candidate indexes reaches the number corresponding to the second parameter.

[0085] In this embodiment of the application, the first information is obtained through the following formula: Among them, I sel For the first information, The second information is obtained through the following formula: Here, It represents the second information, and the value of V(Qi, Ci) is determined based on the query type in statement Qi targeting column Ci in the database.

[0086] In this embodiment of the application, the training module 520 is further configured to perform preprocessing operations on the first information and the second information of the first workload respectively, processing the first information of the first workload into a two-dimensional matrix and processing the second information into a floating-point number.

[0087] In this embodiment of the application, the construction unit 510 is further configured to set a sixth parameter, which represents whether a column in the database has been used as a candidate index; and to set a reward decay factor e_greedy, which is defined as: e_greedy=min{episode×24÷S+e_greedy,a}, where episode is the current training round number, S is the daily workload of the database, and 0<a<1.

[0088] Those skilled in the art should understand that Figure 5 The functions of each unit in the index recommendation device 500 shown can be understood by referring to the relevant description of the aforementioned method. Figure 5 The functions of each unit in the index recommendation device 500 shown can be implemented by a program running on a processor or by specific logic circuits.

[0089] Figure 6 This is a schematic structural diagram of an electronic device 600 provided in an embodiment of this application. Figure 6 The illustrated electronic device 600 includes a processor 610, which can call and run computer programs from memory to implement the methods in the embodiments of this application.

[0090] Optionally, such as Figure 6 As shown, the electronic device 600 may further include a memory 620. The processor 610 can retrieve and run computer programs from the memory 620 to implement the methods described in the embodiments of this application.

[0091] The memory 620 can be a separate device independent of the processor 610, or it can be integrated into the processor 610.

[0092] Optionally, such as Figure 6 As shown, the electronic device 600 may also include a transceiver 630, which the processor 610 can control to communicate with other devices. Specifically, it can send information or data to other devices or receive information or data sent by other devices.

[0093] The transceiver 630 may include a transmitter and a receiver. The transceiver 630 may further include antennas, and the number of antennas may be one or more.

[0094] The electronic device 600 may specifically be an index recommendation device in the embodiments of this application, and the electronic device 600 may implement the corresponding processes implemented by the index recommendation device in the various methods of the embodiments of this application. For the sake of brevity, it will not be described in detail here.

[0095] Figure 7 This is a schematic structural diagram of the chip according to an embodiment of this application. Figure 7The chip 700 shown includes a processor 710, which can call and run computer programs from memory to implement the methods in the embodiments of this application.

[0096] Optionally, such as Figure 7 As shown, chip 700 may further include memory 720. Processor 710 can retrieve and run computer programs from memory 720 to implement the methods described in this embodiment.

[0097] The memory 720 can be a separate device independent of the processor 710, or it can be integrated into the processor 710.

[0098] Optionally, the chip 700 may also include an input interface 730. The processor 710 can control the input interface 730 to communicate with other devices or chips; specifically, it can acquire information or data sent by other devices or chips.

[0099] Optionally, the chip 700 may also include an output interface 740. The processor 710 can control the output interface 740 to communicate with other devices or chips, specifically, to output information or data to other devices or chips.

[0100] This chip can be applied to the index recommendation device in the embodiments of this application, and the chip can implement the corresponding processes implemented by the index recommendation device in the various methods of the embodiments of this application. For the sake of brevity, it will not be described in detail here.

[0101] It should be understood that the chip mentioned in the embodiments of this application may also be referred to as a system-on-a-chip, system chip, chip system, or system-on-a-chip, etc.

[0102] It should be understood that the processor in the embodiments of this application may be an integrated circuit chip with signal processing capabilities. In implementation, the steps of the above method embodiments can be completed by integrated logic circuits in the processor's hardware or by instructions in software form. The processor described above can be a general-purpose processor, a digital signal processor (DSP), an application-specific integrated circuit (ASIC), a field-programmable gate array (FPGA), or other programmable logic devices, discrete gate or transistor logic devices, or discrete hardware components. It can implement or execute the methods, steps, and logic block diagrams disclosed in the embodiments of this application. The general-purpose processor can be a microprocessor or any conventional processor. The steps of the methods disclosed in the embodiments of this application can be directly embodied in the execution of a hardware decoding processor, or executed by a combination of hardware and software modules in the decoding processor. The software modules can be located in random access memory, flash memory, read-only memory, programmable read-only memory, electrically erasable programmable memory, registers, or other mature storage media in the art. The storage medium is located in memory, and the processor reads information from the memory and, in conjunction with its hardware, completes the steps of the above method.

[0103] It is understood that the memory in the embodiments of this application can be volatile memory or non-volatile memory, or may include both volatile and non-volatile memory. The non-volatile memory can be read-only memory (ROM), programmable read-only memory (PROM), erasable programmable read-only memory (EPROM), electrically erasable programmable read-only memory (EEPROM), or flash memory. The volatile memory can be random access memory (RAM), which is used as an external cache. By way of example, but not limitation, many forms of RAM are available, such as Static Random Access Memory (SRAM), Dynamic Random Access Memory (DRAM), Synchronous DRAM (SDRAM), Double Data Rate SDRAM (DDR SDRAM), Enhanced Synchronous DRAM (ESDRAM), Synchlink DRAM (SLDRAM), and Direct Rambus RAM (DR RAM). It should be noted that the memory used in the systems and methods described herein is intended to include, but is not limited to, these and any other suitable types of memory.

[0104] It should be understood that the above-described memory is exemplary and not a limiting description. For example, the memory in the embodiments of this application may also be static random access memory (SRAM), dynamic random access memory (DRAM), synchronous dynamic random access memory (SDRAM), double data rate synchronous dynamic random access memory (DDR SDRAM), enhanced synchronous dynamic random access memory (ESDRAM), synchronous link dynamic random access memory (SLDRAM), and direct memory bus RAM (DR RAM), etc. That is to say, the memory in the embodiments of this application is intended to include, but is not limited to, these and any other suitable types of memory.

[0105] This application also provides a computer-readable storage medium for storing a computer program. This computer-readable storage medium can be applied to the indexing and recommendation apparatus in the embodiments of this application, and the computer program causes a computer to execute the corresponding processes implemented by the indexing and recommendation apparatus in the various methods of the embodiments of this application; for brevity, these will not be elaborated further here.

[0106] Those skilled in the art will recognize that the units and algorithm steps of the various examples described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, or a combination of computer software and electronic hardware. Whether these functions are implemented in hardware or software depends on the specific application and design constraints of the technical solution. Those skilled in the art can use different methods to implement the described functions for each specific application, but such implementation should not be considered beyond the scope of this application.

[0107] Those skilled in the art will understand that, for the sake of convenience and brevity, the specific working processes of the systems, devices, and units described above can be referred to the corresponding processes in the foregoing method embodiments, and will not be repeated here.

[0108] In the several embodiments provided in this application, it should be understood that the disclosed systems, apparatuses, and methods can be implemented in other ways. For example, the apparatus embodiments described above are merely illustrative; for instance, the division of units is only a logical functional division, and in actual implementation, there may be other division methods. For example, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the coupling or direct coupling or communication connection shown or discussed may be through some interfaces; the indirect coupling or communication connection between apparatuses or units may be electrical, mechanical, or other forms.

[0109] The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the units can be selected to achieve the purpose of this embodiment according to actual needs.

[0110] In addition, the functional units in the various embodiments of this application can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit.

[0111] If the aforementioned functions are implemented as software functional units and sold or used as independent products, they can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, or a portion of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, a server, or an indexing and recommendation device, etc.) to execute all or part of the steps of the methods described in the various embodiments of this application. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, portable hard drives, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks.

[0112] The above description is merely a specific embodiment of this application, but the scope of protection of this application is not limited thereto. Any variations or substitutions that can be easily conceived by those skilled in the art within the scope of the technology disclosed in this application should be included within the scope of protection of this application. Therefore, the scope of protection of this application should be determined by the scope of the claims.

Claims

1. An index recommendation method, characterized in that, include: Construct a DQN model; The DQN model includes: a first channel and a second channel; The DQN model is configured with a first parameter, a second parameter, a third parameter, and a fourth parameter; wherein the first parameter represents the number of training epochs; the second parameter represents the number of indexes predicted in each training epoch; the third parameter represents the number of query statements required in each training epoch; and the fourth parameter represents the number of training iterations in each epoch. The DQN model is trained based on first and second information about the workload of the database; wherein the workload includes multiple historical query statements; the first information represents the column selectivity in the workload; the second information represents the column query types of the workload; correspondingly, the first information is used to train the first channel, and the second information is used to train the second channel. Index recommendation is based on the trained DQN model.

2. The index recommendation method according to claim 1, characterized in that, The reward function of the DQN model is defined as follows: in, Cost(Q, X) t The value of 'Q' represents the simulated execution time of traffic Q with the current recommended index included, while Cost(Q, X0) represents the simulated execution time of traffic Q without any index.

3. The index recommendation method according to claim 1, characterized in that, The DQN model is trained using the first and second information of the database workload, including: The DQN model is trained for the number of rounds corresponding to the first parameter; wherein each round of training includes: Input the first and second information of the first workload; the first workload includes the number of historical query statements corresponding to the third parameter; Based on the first information of the first workload, a first vector is obtained through the first channel, and the first vector represents the column selectivity feature of the database; Based on the second information, a second vector is obtained through the second channel, and the second vector represents the column query type characteristics of the database; The first vector and the second vector are fused to obtain a third vector, and each element in the third vector represents the index preference of each column in the database. Select the column with the highest preference as the candidate index; The training round ends when the number of candidate indices reaches the number corresponding to the second parameter.

4. The index recommendation method according to claim 3, characterized in that, The first information is obtained using the following formula: Among them, I sel For the first information, The second information is obtained through the following formula: Among them, I t Characterizing the second information, V (Qi，Ci) The value is determined based on the query type in statement Qi targeting column Ci in the database.

5. The index recommendation method according to claim 4, characterized in that, After inputting the first and second information for the first workload, the following is also included: Preprocessing operations are performed on the first information and the second information of the first workload respectively, processing the first information of the first workload into a two-dimensional matrix and processing the second information into a floating-point number.

6. The index recommendation method according to claim 1, characterized in that, Before training the DQN model using the first and second information based on the database workload, the following steps are also included: Set a sixth parameter, which indicates whether a column in the database has been used as a candidate index; Set the reward decay factor e_greedy, defined as: e_greedy = min{episode×24÷S+e_greedy,a}, where episode is the current training round number, S is the daily workload of the database, and 0 < a < 1.

7. An index recommendation device, characterized in that, include: Building blocks: used to build DQN models; The DQN model includes: a first channel and a second channel; and sets a first parameter, a second parameter, a third parameter, and a fourth parameter for the DQN model; wherein, the first parameter represents the number of training epochs; the second parameter represents the number of indexes predicted in each training epoch; the third parameter represents the number of query statements required in each training epoch; and the fourth parameter represents the number of training iterations in each epoch. Training unit: used to train the DQN model based on first and second information of the workload of the database; wherein, the workload includes multiple historical query statements; the first information represents the column selectivity in the workload; the second information represents the column query type of the workload; correspondingly, the first information is used to train the first channel, and the second information is used to train the second channel. Recommendation Unit: Used for index recommendation based on the trained DQN model.

8. An electronic device, characterized in that, include: A processor and a memory, the memory for storing a computer program, the processor for calling and running the computer program stored in the memory to perform the index recommendation method as described in any one of claims 1-6.

9. A computer-readable storage medium, characterized in that, Used to store a computer program that causes a computer to perform the indexing recommendation method as described in any one of claims 1-6.

Images