An index recommendation method and system based on transfer learning

Abstract

The application discloses an index recommendation method and system based on transfer learning, and relates to the technical field of reinforcement learning, which comprises the following steps: segmenting and encoding a workload stream; training a workload representation model based on self-supervised contrast learning to obtain a workload representation; combining the workload representation based on self-supervised contrast learning and other database meta-information to form a state feature of reinforcement learning, and training an index recommendation model based on a policy gradient algorithm; training a transfer selection model to continuously select an optimal transfer source model in the current environment to guide the training of the index recommendation model; and using the trained index recommendation model to perform index recommendation to obtain an index suitable for the current workload. The generated index set is more accurate, and the introduction of transfer learning reduces the retraining cost of the learning-based index recommendation under dynamic workloads.

Description

Technical Field

[0001] This invention relates to the fields of reinforcement learning and transfer learning, and in particular to an index recommendation method and system based on transfer learning. Background Technology

[0002] With the increasing popularity of database technology, the volume of data in database applications is constantly increasing. During the operation of database applications, a large number of SQL (Structured Query Language) queries are often required. When the data volume is large, a large number of queries will lead to a large number of I / O operations. Creating indexes in the database can significantly alleviate the query efficiency problem.

[0003] Carefully selected indexes can significantly improve database performance. Heuristic-based indexing advisors typically analyze query workload patterns and select index configurations based on predefined algorithms. These methods often result in excessively long runtimes because multiple iterations are required to meet the specified budget.

[0004] Recently, learning-based methods have been proposed and have shown great potential in terms of efficiency and accuracy. Most learning-based indexing advisors are based on reinforcement learning. However, in real-world scenarios, workloads change over time. Current indexing advisors, when handling dynamic workloads, fail to adequately consider the temporal attributes of the workload, leading to an inability to balance efficiency and effectiveness. Summary of the Invention

[0005] The purpose of this invention is to overcome the problems of the prior art and propose an index recommendation method and system based on transfer learning, which generates a more accurate index set. At the same time, due to the introduction of transfer learning, the retraining cost of learning-based index recommendation under dynamic workloads is reduced.

[0006] The present invention adopts the following technical solution:

[0007] On the one hand, an index recommendation method based on transfer learning includes:

[0008] S101, segment and encode the workload stream;

[0009] S102, Train a workload representation model based on self-supervised contrastive learning to obtain the workload representation;

[0010] S103 combines the workload representation based on self-supervised contrastive learning with database metadata to form the state features of reinforcement learning, and trains an index recommendation model based on the policy gradient algorithm; the reinforcement learning state features include database metadata, index information and workload information; the index recommendation model contains two layers of neural network and adopts the policy gradient algorithm;

[0011] S104. During the training of the index recommendation model, a transfer selection model is trained to continuously select the optimal transfer source model in the current environment to guide the training of the index recommendation model. The transfer selection model includes two neural networks: a termination network and an option network. Each selection corresponds to a transfer source model. The termination network is used to obtain the termination probability of the current selection, i.e., whether to switch to another selection. After the current selection terminates, the new selection is obtained through the output of the option network. The transfer source model guides the training of the index recommendation model through transfer learning.

[0012] S105, use the trained index recommendation model to recommend indexes and obtain indexes suitable for the current workload.

[0013] Preferably, S101 specifically includes:

[0014] S1011, the workload is divided according to time;

[0015] S1012, After the segmentation is complete, perform workload encoding according to the following steps:

[0016] S10121 encodes the operator information for the workload; it then calls the database optimizer to generate an execution plan for each query in the workload.

[0017] S10122, Encode the operations in the query plan; first, convert each plan operation related to index selection into a text representation, then construct an operation dictionary to store all text representations; when the size of the operation dictionary is K, for each query i, use a binary vector to encode the operation, b i = i,1 ,…,b i,K >, where b i,k =1 indicates that query i contains the k-th operation in the operation dictionary; a Latent Semantic Index (LSI) model is constructed for dimensionality reduction, and the output is denoted as l. i ;

[0018] S10123, Encode the value of each query; Extract the value corresponding to the operation by parsing the query plan; The value can be divided into two types, including numeric and string values;

[0019] For numerical values, normalization is performed, that is... ​Where val represents the value in the query, min represents the minimum value, and max represents the maximum value of each column in the training data. i,k =1 means that the normalized result of the value corresponding to the k-th operation of the i-th query is 1;

[0020] For string values, binning is used for encoding; for columns of string type, the H-size value binding boundary is represented as u = <u0,…,u H >;where, u h This indicates that the h / H values ​​in this column are less than u in lexicographical order. h ; This method is used to locate h:u h-1 <val≤u h The string value is encoded as

[0021] For v i,k If the k-th operator does not correspond to any value, then v i,k Fill with 0; since the number of operations k is different for each query i, PCA is used to reduce v. i,k The dimension; finally, the value is encoded as c. i ;

[0022] S10124, obtained l i With c i Next, the operation code, value code, and query frequency of each query in a workload are concatenated to obtain the workload's encoding. Where w represents the workload, and |w| represents the number of queries in the workload.

[0023] Preferably, in step S102, the workload representation model is trained according to the following steps:

[0024] S1021, Construct the training set; First, randomly generate a set of workloads, then for each batch of workloads, construct contrastive learning samples and identify positive and negative examples; when a set of workloads is generated... At that time, for each workload w, the relevant columns are extracted and a set of candidate indexes is derived. The maximum index width is d. max ;

[0025] S1022 measures the similarity between workloads by index performance and uses index ranking to reflect the index preferences of the workloads; the database optimizer evaluates and ranks candidate indexes based on the estimated cost of the workloads; for each workload w, an index ranking R is defined. w :

[0026]

[0027]

[0028] in, The index space representing the workload w. n w express Size; The workload w in the index The estimated cost below; The workload w in the index The estimated cost below;

[0029] S1023, combine set similarity and index sorting to determine the similarity α(w,w′) of workloads:

[0030]

[0031] in, R represents w The set of all elements from position 1 to position t; t is the minimum length of all index rankings in the training set. Calculate the set similarity between two workloads at each position t of its index ranking, and sum the similarities over all positions.

[0032] S1024, determine positive and negative examples in the training set based on similarity; for any given workload w, the similarity value is α(w,w′)>τ. + The set of workloads w′ is its set of positive examples e. + (w), the others are negative example sets e - (w), where τ + For the threshold;

[0033] S1025, encode the workload z w Input to a multilayer perceptron (MLP) to obtain a workload representation p w =MLP(z w The parameters of the MLP are optimized by contrastive loss, i.e., minimizing e. + The distance between the positive examples is maximized by e. - Distance between negative examples, loss function It is expressed as follows:

[0034]

[0035] in, p represents the dataset for a single training batch; w′ P represents the workload that is a positive example of w. w″ This represents the workload that is negative of w.

[0036] Preferably, in step S103, the index recommendation model is trained according to the following steps:

[0037] S1031, Construct a training set and train using randomly generated workloads;

[0038] S1032, Constructing reinforcement learning states Status includes database metadata. Index information Workload information The three parts are implemented as follows:

[0039] (a) Construct workload information; after encoding the workload w, input it into the workload representation model based on self-supervised contrastive learning in S102 to obtain p. w ;definition in It is the frequency of each query j; defined in This represents the estimated execution cost of each query under the current index configuration I; these costs are then concatenated to form the workload information s. W =[p w ,s F ,s E ];

[0040] (b) Constructing meta-information; meta-information is a fixed-length vector. It includes four scalar features: the specified storage budget, the current storage consumption, the workload execution cost without any indexes, and the workload cost estimated by the database optimizer under the current index configuration;

[0041] (c) Construct the index information; the index information is a vector. It encodes the current operation for each indexable column, where D I This represents the number of indexable columns; for multi-column indexes, the value of each column decreases according to its position in the index; the formula is expressed as... Where k(j,a) i ) is action a i The position of the j-th column;

[0042] (d) Concatenate the workload information vector, meta-information vector, and index information vector to obtain the reinforcement learning state, i.e., s = [s W ,s M ,s I ];

[0043] S1033, Construct the policy model π; the policy model is a 2-layer feedforward network FFN, which reinforces the learning state s. i As input, output the probability π(a) of selecting each action. i,s i The index recommendation system is based on π(a) i ,s i The value of ) is used to select an action a. i That is, select the action with the highest probability; a i These are discrete actions, each corresponding to selecting an index from the candidate indices; for each workload, this is based on the specified maximum index width d. max Generate all grammar-related candidate indexes;

[0044] S1034, Construct action masking; Use action masking to control the size of the action space before each action selection, as follows:

[0045] Block indexes that exceed the storage budget; before each step, block actions based on the current storage consumption to ensure that the next operation will not exceed the specified budget;

[0046] Delete multi-level indexes with invalid preconditions; for each multi-level index, if its primary column is not selected in existing actions, then disable the index.

[0047] S1035, Construct the action reward; the policy model is updated based on the gradient direction that maximizes the action reward; the relative cost is used as the reward, and the ratio to the initial cost is calculated; the impact on index storage is considered, and the storage increment before and after index selection is included in the reward at each step, encouraging the agent to consider storage consumption while striving to reduce workload costs. The reward design formula is expressed as follows:

[0048]

[0049] in, This represents the cost of the workload in the first i-1 steps; This represents the cost of the workload in step i; This represents the cost in the initial state before any indexes are created; This indicates the storage usage of the workload in step i; This indicates the storage usage of the workload in the first i-1 steps;

[0050] S1036, Define the loss; in the source policy π T and the current strategy π o Between, define migration loss Where H(·∣∣·) is the cross-entropy loss; combined with the reward and transition loss, it is determined by a decay factor δ. t Weighted:

[0051]

[0052] Where, r iπ represents the reward at step i; T (a i ,s i ) indicates that the source strategy selects action a. i The probability of π; o (a i ,s i ) indicates that the current strategy selects action a. i The probability of.

[0053] Preferably, in step S104, the transfer selection model is trained according to the following steps:

[0054] S1041, a value-based method is used to construct and update the migration selection model; the migration selection model includes an option network and a termination network; wherein the option network Q... o (s i ,o i The input to ) is the current state s i And the current option o i The output is the estimated value of each option, with each option corresponding to a source policy.

[0055] S1042, for updating options network Q o (s,o), first sample a batch of historical records (s) from the playback buffer D. i ,a i ,r i ,s i+1 ,o i This buffer records the state, action, reward, next state, and current option at step i, where step i corresponds to the historical execution steps of reinforcement learning; then, the current option o is used. i Corresponding source policy π o In state s i Select an action If the source policy output is the same as the operation record in the buffer, that is... Then use the expected reward to update the option network.

[0056]

[0057] Among them, g i The expected return in step i is defined as:

[0058] g i =r i +γU(s i+1 ,o)

[0059] Where, r i The reward is the historical value; γ is the decay factor; the source policy is π. oIn this case, the expected reward of the next state is U(s). i+1 ,o), is defined as:

[0060]

[0061] Wherein, β(s) i+1 ,o) denotes the terminating network, which gives the probability of terminating the current option o; if β(s i+1 If (o) = 1, then the expected return of the next choice will be determined by... The decision is made at o′, which represents the new choice; otherwise, the current choice is retained and the expected return Q is output. o (s i+1 ,o);

[0062] S1043, Update the terminating network. The optimization objective of the terminating network is to make the output termination probability inversely proportional to the expected reward. The loss function is... Where δ is the regularization term and o is the current option. If the current option is not the one with the highest estimated value among all options, then the loss function will increase the termination probability of terminating the network output.

[0063] On the other hand, an index recommendation system based on transfer learning includes:

[0064] The workload stream processing module is used to segment and encode the workload stream;

[0065] The workload representation model training module is used to train a workload representation model based on self-supervised contrastive learning to obtain the workload representation.

[0066] The index recommendation model training module is used to combine the workload representation based on self-supervised contrastive learning and database metadata to form the state features of reinforcement learning, and train an index recommendation model based on the policy gradient algorithm. The reinforcement learning state features include database metadata, index information and workload information. The index recommendation model contains two layers of neural network and adopts the policy gradient algorithm.

[0067] The transfer selection model training module is used to train a transfer selection model during the training process of the index recommendation model. It continuously selects the optimal transfer source model in the current environment to guide the training of the index recommendation model. The transfer selection model consists of two neural networks: a termination network and a selection network. Each selection corresponds to a transfer source model. The termination network is used to obtain the termination probability of the current selection, i.e., whether to switch to another selection. After the current selection terminates, a new selection is obtained through the output of the selection network. The transfer source model guides the training of the index recommendation model through transfer learning.

[0068] The index recommendation module is used to recommend indexes using a trained index recommendation model to obtain indexes suitable for the current workload.

[0069] Compared with the prior art, the beneficial effects of the present invention are as follows:

[0070] The index set generated by this invention is more accurate, and the introduction of transfer learning reduces the retraining cost of learning-based index recommendation under dynamic workloads. Attached Figure Description

[0071] Figure 1 This is a flowchart of an index recommendation method based on transfer learning according to an embodiment of the present invention;

[0072] Figure 2 This is a schematic diagram of the workload representation model according to an embodiment of the present invention;

[0073] Figure 3 This is a schematic diagram illustrating the comparative learning positive and negative example construction process in an embodiment of the present invention;

[0074] Figure 4 This is a schematic diagram of an index recommendation model based on reinforcement learning and transfer learning according to an embodiment of the present invention;

[0075] Figure 5 This is a structural block diagram of an index recommendation system based on transfer learning according to an embodiment of the present invention. Detailed Implementation

[0076] The present invention will be further illustrated below with reference to specific embodiments. It should be understood that these embodiments are for illustrative purposes only and are not intended to limit the scope of the invention. Furthermore, it should be understood that after reading the teachings of this invention, those skilled in the art can make various alterations or modifications to the invention, and these equivalent forms also fall within the scope defined by the appended claims.

[0077] The core idea of ​​this invention is to combine a representation model based on self-supervised contrastive learning and an index recommendation model based on reinforcement learning, supplemented by a transfer learning module to transfer knowledge between dynamic workloads, such as... Figure 4 As shown.

[0078] See Figure 1The diagram shows a flowchart of an index recommendation method based on transfer learning according to an embodiment of the present invention. S101: The workload stream is divided into time segments and then encoded. S102 to 104: A workload representation model based on self-supervised contrastive learning, an index recommendation model based on reinforcement learning, and a transfer learning model are trained, respectively. The training of the two models in S103-104 is performed synchronously and influences each other. In S105: The workload model and index recommendation model from S102 and 103 are used to process the workload, outputting a recommended set of indexes.

[0079] The following combination Figure 1 Specific embodiments of the present invention will be described in detail below. As described above, the method may include steps S101 to S105.

[0080] S101: Segment and encode the workload stream. The workload stream is first segmented according to the time granularity required by the user. Except for S102, the training process and output in subsequent steps all take the workload within a certain time period as input. The workload encoding process mainly consists of three steps: generating the execution plan, encoding the plan operation, and encoding the query value, as detailed below.

[0081] S10121 encodes the operator information for the workload. It then invokes the database optimizer to generate an execution plan for each query in the workload.

[0082] S10122, Encode the operations in the query plan. Specifically, each plan operation related to index selection will be converted into a text representation. For example, the conditional filter T1.COL2<5 will be converted to TabScan_T1_COL2_Pred<. Then, an operation dictionary is constructed to store all the text representations. Assuming the size of the operation dictionary is K, for each query i, a binary vector is used to encode the operation, b i = i,1 ,…,b i，K >, where b i，k =1 indicates that query i contains the k-th operation in the operation dictionary. To prevent the generated binary vector from being too sparse, a Latent Semantic Index (LSI) model is constructed for dimensionality reduction, and the output is denoted as l. i .

[0083] S10123, Encode the value for each query. Similar to operation encoding, extract the value corresponding to the operation by parsing the query plan. For example, in the conditional filter T1.COL2<5, there is a value 5 corresponding to the operation TabScan_T1_COL2_Pred<. Construct a value encoding vector v. i = <v i，1 ,…,v i，K >, where v​i，k It corresponds to the value of the k-th operation in the operation dictionary. The value can be of two types: numeric and string.

[0084] (a) For numerical values, normalize them, i.e. Where val is the value in the query, min is the minimum value, and max is the maximum value of each column in the training data.

[0085] (b) For string values, binning is used for encoding. For columns of string type, the H-size value binding boundary is represented as u = <u0,…,u H >, where u h This indicates that the h / H values ​​in this column are less than u in lexicographical order. h This is how h:u is located. h-1 <val≤u h The string value is encoded as

[0086] For v i,k If the k-th operator does not correspond to any value, then v i,k Fill with 0. To prevent excessively high dimensionality, use PCA to reduce v. i,k The dimension; finally, the value is encoded as c. i ;

[0087] S10124, obtained l i With c i Next, the operation code, value code, and query frequency of each query in a workload are concatenated to obtain the workload's encoding.

[0088] The following example uses a workload stream generated from TPCH, which is segmented according to the time granularity required by the user, resulting in a workload containing 14 queries:

[0089] {{"Q1":"select sum(l_extendedprice*l_discount)as revenue fromlineitem where l_shipdate>=date'1993-01-01'and l_shipdate <date'1993-01-01'+interval'1'year and l_discount between 0.07-0.01and 0.07+0.01and l_quantity<25;","frequency":"207"}

[0090] {"Q2":"select ps_partkey,sum(ps_supplycost*ps_availqty)as value frompartsupp,supplier,nation where ps_suppkey=s_suppkey and s_nationkey=n_nationkey and n_name='JAPAN'group by ps_partkey having sum(ps_supplycost*ps_availqty)>(select sum(ps_supplycost*ps_availqty)*0.0000100000from partsupp,supplier,nation where ps_suppkey=s_suppkey and s_nationkey=n_nationkey andn_name='JAPAN')order by value desc;","frequency":"718"},…,"budget":"3500"}

[0091] Next, the workload is encoded. First, the database optimizer is called to generate an execution plan for each query. Then, all operations in the query plans are converted into text and a dictionary is generated, {0: 'SeqScan_lineitem_l_shipdate<=::timestamp

[0092] withouttimezone', 1: 'Sort_SortKey_l_returnflag_l_linestatus_', 2: 'Sort_SortKey_l_returnfl ag_l_linestatus_',…,121: 'IndexOnlyScan_partsupp_ps_partkey=public.partsupp.ps_partkeyANDps_supplycost=minpublic.partsupp.ps_supplycost'}. After obtaining the dictionary, each query operation is encoded. For example, {(10,3),(26,2),(72,5)} represents that the operations at positions 10, 26, and 72 in the dictionary appear 3, 2, and 5 times in the query. This encoding will be converted into a vector of the same length as the dictionary, and then compressed to 40 dimensions using the LSI method.

[0093] The operation values ​​are encoded, and the operations in the execution plan are parsed to extract the values ​​involved in the operation. If the values ​​are numeric, they are normalized; if they are character, they are binned. For example, if operation A in the execution plan involves the value "B", and the column containing "B" contains data {"A","B","C"}, and the data is divided into 3 bins, then "B" will be encoded as 2 / 3. This encoding will be converted into a vector of the same length as the dictionary, and then the dimensionality will be reduced to 10 dimensions using PCA.

[0094] After obtaining the above two codes, each query is represented by 40+10+1=51 dimensions, where 40 dimensions are operation codes, 10 dimensions are operation value codes, and 1 dimension is the query frequency.

[0095] S102, train a workload representation model based on self-supervised contrastive learning to obtain the workload representation.

[0096] See details Figure 2 As shown, the aim is to obtain a more effective workload representation using self-supervised contrastive learning. The workload representation based on self-supervised contrastive learning uses key workload features related to indexing performance as the basis for constructing positive and negative examples, enabling the index advisor model to generate more reliable and accurate indexing recommendations.

[0097] First, workloads are randomly generated as training samples for comparative learning. Figure 3 The document describes the process of constructing positive and negative examples for contrastive learning. Assume there are three workloads in the training data: w1, w2, and w3. First, for each workload, relevant columns are extracted and a set of candidate indexes is generated. Then, the database optimizer is called to obtain the optimization effect of the candidate indexes on the workload, and the indexes are ranked based on the effect. Assuming the index of w1 is sorted as {'A', 'B', 'C'}, and the index of w2 is sorted as {'E', 'B', 'A'}, then their similarity a(w1, w2) = 0 + 0.5 + 0.66 = 1.16. 0 represents no duplicate elements in a set of length 1, 0.5 represents 50% duplicate elements in a set of length 2, and so on. Given a threshold, similarities greater than the threshold are considered positive examples, and similarities less than the threshold are considered negative examples.

[0098] After constructing the training samples, training is performed using contrastive learning loss. The input for training is the workload encoding, which is passed through a two-layer perceptron (MLP) model to output the workload representation. Table 1 shows a comparison of index recommendation performance under different representations. Simply using workload encoding results in encoding that is only sensitive to queries within the workload. However, workloads with similar content may have drastically different indexing requirements. The workload representation obtained through contrastive learning captures more information beyond semantics, resulting in a more accurate set of index recommendations.

[0099] Table 1 Comparison of index recommendation performance under different characteristics.

[0100] Relative cost (compared to cost without index) Convergence time Workload coding 0.59 1.18 Workload representation 0.56 1.21

[0101] S103 combines the workload representation based on self-supervised contrastive learning with other database metadata to form the state features for reinforcement learning, and trains an index recommendation model based on the policy gradient algorithm. The reinforcement learning state features mainly include three parts: database metadata, index information, and workload information.

[0102] Constructing workload information: The workload w is encoded and input into S102 to obtain p based on a self-supervised contrastive learning-based workload representation model. w ;definition in It is the frequency of each query j; defined in This represents the estimated execution cost of each query under the current index configuration I; these costs are then concatenated to form the workload information s. W =[p w ,s F ,s E ];

[0103] Constructing meta-information; meta-information is a fixed-length vector. It includes four scalar features: the specified storage budget, the current storage consumption, the workload execution cost without any indexes, and the workload cost estimated by the database optimizer under the current index configuration;

[0104] Construct index information; the index information is a vector. It encodes the current operation for each indexable column, where D I This represents the number of indexable columns; for multi-column indexes, the value of each column decreases according to its position in the index; the formula is expressed as... Where k(j,a) i ) is action a i The position of the j-th column;

[0105] The workload information vector, meta-information vector, and index information vector are concatenated to obtain the reinforcement learning state, i.e., s = [s W ,s M ,s I ].

[0106] Figure 4The specific components of the reinforcement learning state are given. Due to the huge index space, the current index information is represented by a vector of length A, where A is the number of first-level indices. A compression method is used to represent selected indices; for example, if a multi-level index (A, B, C) is selected, the vector values ​​corresponding to A, B, and C each increase by 0.33; if (A) is selected, the vector value corresponding to A increases by 1. The index recommendation model contains two layers of neural networks and employs the policy gradient algorithm. The model takes reinforcement learning state features as input and outputs the selected action. The reward is calculated based on the action, and the model is updated until training ends.

[0107] This step also involves constructing a policy model. The policy model is a two-layer feedforward network (FFN) with state s i As input. The indexed recommendation system uses the selection probability π(a) as input. i ,s i The value of ) is used to select an action a. i a i These are discrete actions, each corresponding to selecting an index from the candidate indices. For each workload, the maximum index width d is specified. max Generate all syntax-related candidate indexes. For example, for the query "select * from T1 where T1.COL1>3andT1.COL3<5", generate a candidate index set {(T1..COL1),(T1.COL3),(T1.COL1,T1.COL3),(T1.COL3,T1.COL1)}.

[0108] Step 104: Train a transfer selection model to continuously select the optimal transfer source model in the current environment to guide the training of the index recommendation model.

[0109] During the training of the index recommendation model, a transfer selection model is trained to continuously select the optimal transfer source model for the current environment, which then guides the training of the index recommendation model. The transfer selection model mainly consists of two neural networks: a termination network and a selection network. Each selection corresponds to a transfer source model. The termination network obtains the termination probability of the current selection, i.e., whether to switch to another selection. After the current selection terminates, a new selection is obtained through the output of the selection network. The transfer source models guide the training of the index recommendation model through transfer learning.

[0110] In this embodiment, a value-based approach is used to construct and update the migration selection model because the number of source strategies in the migration selection model is significantly smaller than the number of index operations, and the value-based approach is faster.

[0111] S105, use the trained index recommendation model to recommend an index and obtain an index suitable for the current workload.

[0112] This embodiment compares the present invention with various existing methods. The comparison methods are: (1) DQN: using the DQN algorithm in reinforcement learning for index recommendation. (2) DRLindex: also based on the DQN algorithm, and directly outputs index suggestions through training the network. (3) SWIRL: based on policy gradient model, it can provide index recommendations for workloads containing invisible queries. (4) Extend: based on complex heuristics, it obtains near-optimal index recommendations through long-term operation.

[0113] Table 2 shows the performance of each model on TPCH. The BALANCE model (the index recommendation model of this invention) achieved better recommendation results in most cases.

[0114] Table 2 Comparison of performance of each model

[0115] DQN DRLindex Swirl Extend Balance TPCH-1G 59.6 62.3 58.8 56.7 57.3 TPCH-10G 68.02 70.6 68.44 68.21 67.61 TPCDS-1G 81.8 86.5 62.34 62.47 59.96 TPCDS-10G 89.6 87.5 73.39 69.62 73.01

[0116] Besides recommendation performance, index recommendation time is equally important. Table 3 shows the average total inference runtime on the TPC-H dataset using different index consultants (using 10 test workloads across three runs). Balance requires the least inference runtime. First, Balance's inference runtime is two orders of magnitude faster than Extend's. This is because Balance directly recommends indexes by feeding workload characteristics to the neural network and performing forward inference in less than a second, while heuristic-based index consultants like Extend require a significant amount of time to iteratively search for satisfactory solutions. Second, due to action masking, Balance is much faster than DQNs like DRLindex and learning-based index consultants.

[0117] Table 3 Comparison of inference time for each model

[0118] DQN DRLindex Swirl Extend Balance TPCH-1G 6.31 31.43 0.42 79.05 0.4 TPCH-10G 8.55 38.65 0.55 90.31 0.51

[0119] See Figure 5 As shown, this embodiment also discloses an index recommendation system based on transfer learning, including:

[0120] The workload stream processing module 501 is used to segment and encode the workload stream;

[0121] The workload representation model training module 502 is used to train a workload representation model based on self-supervised contrastive learning to obtain the workload representation.

[0122] The index recommendation model training module 503 is used to combine the workload representation based on self-supervised contrastive learning and the database metadata to form the state features of reinforcement learning, and train an index recommendation model based on the policy gradient algorithm; the reinforcement learning state features include database metadata, index information and workload information; the index recommendation model contains two layers of neural network and adopts the policy gradient algorithm;

[0123] The transfer selection model training module 504 is used to train a transfer selection model during the training process of the index recommendation model. It continuously selects the optimal transfer source model in the current environment to guide the training of the index recommendation model. The transfer selection model includes two neural networks: a termination network and a selection network. Each selection corresponds to a transfer source model. The termination network is used to obtain the termination probability of the current selection, i.e., whether to switch to another selection. After the current selection terminates, a new selection is obtained through the output of the selection network. The transfer source model guides the training of the index recommendation model through transfer learning.

[0124] The index recommendation module 505 is used to recommend indexes using a trained index recommendation model to obtain indexes suitable for the current workload.

[0125] The specific implementation of an index recommendation system based on transfer learning is the same as that of an index recommendation method based on transfer learning, and will not be described again in this embodiment.

[0126] Obviously, those skilled in the art can make various modifications and variations to this invention without departing from its scope. Therefore, if these modifications and variations fall within the scope of the claims of this invention and their equivalents, this invention also intends to include these modifications and variations.

Claims

1. An index recommendation method based on transfer learning, characterized in that, include: S101, the workload stream is segmented and encoded; the workload stream is first segmented according to the time granularity required by the user. Except for S102, the training process and output in subsequent steps all take the workload within a time period as input; the workload encoding process is divided into three steps: generating the execution plan, encoding the plan operation, and encoding the query value. S102, train a workload representation model based on self-supervised contrastive learning to obtain the workload representation; the workload representation based on self-supervised contrastive learning uses key workload features related to indexing performance as the basis for constructing positive and negative examples; S103 combines workload representations based on self-supervised contrastive learning with database metadata to form state features for reinforcement learning, training an index recommendation model based on the policy gradient algorithm. The reinforcement learning state features include database metadata, index information, and workload information. The index recommendation model consists of two neural networks employing the policy gradient algorithm. The metadata is a fixed-length vector containing four scalar features: the specified storage budget, current storage consumption, workload execution cost without any indexes, and workload cost estimated by the database optimizer under the current index configuration. The two neural networks train the reinforcement learning state... As input, output the probability of selecting each action. The index recommendation system is based on Use the value to select an action That is, select the action with the highest probability; These are discrete actions, each corresponding to selecting an index from the candidate indices; for each workload, this is based on the specified maximum index width. Generate all grammar-related candidate indexes; S104. During the training of the index recommendation model, a transfer selection model is trained to continuously select the optimal transfer source model in the current environment to guide the training of the index recommendation model. The transfer selection model includes two neural networks: a termination network and an option network. Each selection corresponds to a transfer source model. The termination network is used to obtain the termination probability of the current selection, i.e., whether to switch to another selection. After the current selection terminates, the new selection is obtained through the output of the option network. The source model uses transfer learning to guide the training of the index recommendation model; S105, use the trained index recommendation model to recommend indexes and obtain indexes suitable for the current workload.

2. The index recommendation method based on transfer learning according to claim 1, characterized in that, S101 specifically includes: S1011, the workload is divided according to time; S1012, After the segmentation is complete, perform workload encoding according to the following steps: S10121 encodes the operator information for the workload; it then calls the database optimizer to generate an execution plan for each query in the workload. S10122, Encode the operations in the query plan; first, convert each plan operation related to index selection into a text representation, then construct an operation dictionary to store all text representations; when the size of the operation dictionary is... At that time, for each query It uses a binary vector for encoding and representation. ,in Indicates query Contains the first in the operation dictionary One operation; construct a Latent Semantic Index (LSI) model for dimensionality reduction, and denote the output as... ; S10123, Encode the value of each query; Extract the value corresponding to the operation by parsing the query plan; The value can be divided into two types, including numeric and string values; For numerical values, normalization is performed, that is... ;in, This represents the value in the query. This represents the minimum value. This represents the maximum value of each column in the training data. Representing the The normalized result of the value corresponding to the k-th operation of the query is 1; For string values, binning is used for encoding; for columns of string type, ... The -size value binding boundary is represented as follows: ;in, This indicates the column containing Values ​​are less than in lexicographical order Positioning in this way The string value is encoded as ; for If the first If an operator does not correspond to any value, then... Fill with 0; because each query corresponding operands Different, therefore PCA is used to reduce The dimension; finally, the encoding of this value is represented as ; S10124, obtained and Next, the operation code, value code, and query frequency of each query in a workload are concatenated to obtain the workload's encoding. Where w represents the workload. This represents the number of queries in the workload.

3. The index recommendation method based on transfer learning according to claim 2, characterized in that, In step S102, the workload representation model is trained according to the following steps: S1021, Construct the training set; First, randomly generate a set of workloads, then for each batch of workloads, construct contrastive learning samples and identify positive and negative examples; when a set of workloads is generated... At that time, for each workload Extract relevant columns and derive a set of candidate indexes. The maximum index width is ; S1022 measures the similarity between workloads by index performance and uses index ranking to reflect the index preferences of workloads. The database optimizer evaluates and ranks candidate indexes based on the estimated cost of the workload; for each workload... They all define an index sorting. : ； in, Represents workload index space, , ; , express Size; Represents workload In the index The estimated cost below; Represents workload In the index The estimated cost below; S1023, combine set similarity with index sorting to determine workload similarity. : ； in, express From position 1 to position The set of all elements; To find the minimum length of all index rankings in the training set, compute the index ranking at each position. Find the set similarity between two workloads and sum the similarities over all locations; S1024, determine positive and negative examples in the training set based on similarity; for any given workload The similarity value is workload The set is its set of positive examples. The others are negative examples. ,in For threshold; S1025, Encode the workload Input to a multilayer perceptron (MLP) to obtain a workload representation. The parameters of the MLP are optimized by contrastive loss, i.e., minimizing... Maximize the distance between positive examples Distance between negative examples, loss function It is expressed as follows: ； in, The dataset representing a single training batch; The representation of workload that is positive of w. This represents the workload that is negative of w.

4. The index recommendation method based on transfer learning according to claim 3, characterized in that, In step S103, the index recommendation model is trained according to the following steps: S1031, Construct a training set and train using randomly generated workloads; S1032, Constructing reinforcement learning states The status includes database metadata. Index information Workload information The three parts are implemented as follows: (a) Construct workload information; assign workload After encoding, the data is input into S102 to obtain a workload representation model based on self-supervised contrastive learning. ;definition ,in It is the frequency of each query j; defined ,in In the current index configuration Estimate the execution cost of each query; combine them to form workload information. ; (b) Constructing meta-information; meta-information is a fixed-length vector. ; (c) Construct index information; The index information is a vector It encodes the current operation for each indexable column, where This represents the number of indexable columns; for multi-column indexes, the value of each column decreases according to its position in the index; the formula is expressed as... ,in It is an action The Middle Column position; (d) The workload information vector, meta-information vector, and index information vector are concatenated to obtain the reinforcement learning state, i.e. ; S1033, Constructing a strategy model The policy model is a two-layer feedforward network (FFN). S1034, Construct action masking; Use action masking to control the size of the action space before each action selection, as follows: Block indexes that exceed the storage budget; before each step, block actions based on the current storage consumption to ensure that the next operation will not exceed the specified budget; Delete multi-level indexes with invalid preconditions; for each multi-level index, if its primary column is not selected in existing actions, then disable the index. S1035, Construct the action reward; the policy model is updated based on the gradient direction that maximizes the action reward; the relative cost is used as the reward, and the ratio to the initial cost is calculated; the impact on index storage is considered, and the storage increment before and after index selection is included in the reward at each step, encouraging the agent to consider storage consumption while striving to reduce workload costs. The reward design formula is expressed as follows: ； in, Indicates preceding The cost of workload during the step; Indicates the first The cost of workload during the step; This represents the cost in the initial state before any indexes are created; Indicates the first Storage usage for workloads during the process; Indicates preceding Storage usage for workloads during the process; S1036, Define the loss; in the source policy and current strategy Define migration loss between these points. ,in The loss is cross-entropy; it combines reward and transition losses, and is subject to a decay factor. Weighted: ； in, Indicates the first Rewards for each step; Indicates the source strategy selection action The probability of; Indicates the action selected by the current strategy. The probability of.

5. The index recommendation method based on transfer learning according to claim 4, characterized in that, In step S104, the transfer selection model is trained according to the following steps: S1041, a value-based method is used to construct and update the migration selection model; the migration selection model includes an option network and a termination network; wherein the option network... The input is the current state. and current options The output is the estimated value of each option, with each option corresponding to a source policy. S1042, for updating network options First from the playback buffer A batch of historical records were sampled. The buffer records the steps. The current state, action, reward, next state, and current option, and steps. This corresponds to the historical execution steps of reinforcement learning; then, the current option is used. Corresponding source strategy In state Select an action If the source policy output is the same as the operation record in the buffer, i.e. = Then the expected reward is used to update the option network. : ； in, Steps The expected return value is defined as: ； in, Rewards from historical records; The decay factor is; the source strategy is In this case, the expected reward of the next state is... Defined as: ； in, This indicates that the network should be terminated; it provides the option to terminate the current network. The probability if Then the expected return of the next choice will be determined by Decide, That is, a new choice; otherwise, the current choice will be retained and the expected return will be output. ; S1043, Update the terminating network. The optimization objective of the terminating network is to make the output termination probability inversely proportional to the expected reward. The loss function is... ,in For regularization terms, If the current option is not the one with the highest estimated value among all options, then the loss function will increase the probability of terminating the network output.

6. An index recommendation system based on transfer learning, characterized in that, To implement the method of any one of claims 1-5, comprising: The workload stream processing module is used to segment and encode the workload stream; The workload representation model training module is used to train a workload representation model based on self-supervised contrastive learning to obtain the workload representation. The index recommendation model training module is used to combine the workload representation based on self-supervised contrastive learning and database metadata to form the state features of reinforcement learning, and train an index recommendation model based on the policy gradient algorithm. The reinforcement learning state features include database metadata, index information and workload information. The index recommendation model contains two layers of neural network and adopts the policy gradient algorithm. The transfer selection model training module is used to train a transfer selection model during the training process of the index recommendation model. It continuously selects the optimal transfer source model in the current environment to guide the training of the index recommendation model. The transfer selection model consists of two neural networks: a termination network and a selection network. Each selection corresponds to a transfer source model. The termination network is used to obtain the termination probability of the current selection, i.e., whether to switch to another selection. After the current selection terminates, a new selection is obtained through the output of the selection network. The transfer source model guides the training of the index recommendation model through transfer learning. The index recommendation module is used to recommend indexes using a trained index recommendation model to obtain indexes suitable for the current workload.

Images