Database cardinal number estimation method based on multi-head attention mechanism

Abstract

The invention provides a database cardinal number estimation method based on a multi-head attention mechanism, and belongs to the technical field of data processing in a database. Comprising the following steps: acquiring a data set and a label; preprocessing the data set; constructing a multi-head network based on an attention mechanism; storing the optimal weight of the aggregation position coding multi-head attention model; and calculating the accuracy of the network on the test set. According to the aggregation position coding multi-head attention model, a lightweight and efficient position coding module is introduced, and the aggregation position coding multi-head attention model fuses multi-head self-attention and multi-head cross-attention which are respectively used for capturing a data relationship and querying dependency. According to the aggregation position coding multi-head attention model, position coding and pooling layers are stacked to adaptively highlight the most unique feature internal information, so that the cardinality of corresponding query is better calculated, and the accuracy of database cardinality estimation is improved.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing in a database, and in particular, to a database cardinality estimation method based on a multi-head attention mechanism. Background Art

[0002] Query optimizer plays an important role in DBMS, and database cardinality estimation is an important part of query optimizer. Query optimizer can generate the optimal execution plan based on the logical structure of the query and the physical organization structure of the database, thereby improving the execution efficiency and performance of database queries. The purpose of cardinality estimation is to obtain the cardinality of the query, which is one of the important parameters for execution plan selection, index selection and query optimization performance.

[0003] In recent years, deep learning cardinality estimation has gradually replaced traditional estimation and absorbed their shortcomings. Database cardinality estimation based on deep learning can be divided into data-driven and query-driven. Data-driven cardinality estimation mainly constructs complex data structures to store information through statistics and analysis of underlying data, and then obtains cardinality by parsing query expressions, but it lacks universality for data sampling and its accuracy is often low when processing large dynamic workloads. Query-driven cardinality estimation uses supervised methods to learn the correspondence between query features and cardinality, thereby performing cardinality estimation. It can use either regression models or graph structures to transform the cardinality estimation problem into a graph problem.

[0004] However, since these methods are trained based on a fixed data distribution, when the underlying data changes, the model cannot effectively update its parameters to reflect the new data features and relationships, and thus the ability to handle dynamic workloads is also limited. Although the ALECE model, which combines data-driven and query-driven, can effectively handle dynamic workloads, it ignores the positional relationships within the data features and cannot adequately handle extreme values. Summary of the invention

[0005] According to the technical problems mentioned in the above background technology, a database cardinality estimation method based on a multi-head attention mechanism is provided, which can obtain a faster training network model and improve the accuracy of the query cardinality.

[0006] The technical means adopted by the present invention are as follows:

[0007] The database cardinality estimation method based on the multi-head attention mechanism includes the following steps:

[0008] Step 1: Obtain a data set and label of preset attributes in the database and randomly divide the data set and label into a training set, a validation set, and a test set according to a ratio of 70%, 15%, and 15%;

[0009] Step 2: Construct an aggregated position coding multi-head attention model based on the attention mechanism; use the aggregated position coding multi-head attention model to extract features from data and structured query language queries, reduce the probability of extreme values ​​appearing in query features based on M-estimator encoding, and control the features to the average value of the probability of extreme values ​​appearing, which is used to predict the accuracy of database cardinality estimation;

[0010] Step 3: Preprocess the data set; divide the underlying database into several histograms according to one attribute and one histogram and equal frequency according to the same number of items in one interval, and perform histogram featurization to preprocess the database table; divide the data in the data set into database status and query partial features according to the position index of the feature dimension and merge them into the two-dimensional array required by the aggregated position encoding multi-head attention model on the column;

[0011] The query features are obtained after encoding from M-estimator and for the queries divided into training part and test part, the mean corresponding to the queries in the training part and the test part is subtracted and divided by the standard deviation to achieve the purpose of logarithmic scaling;

[0012]

[0013] Among them, Q represents the feature extracted from the workload, u represents the mean of the query feature, and σ represents the standard deviation of the query feature. The query feature Q after preprocessing is obtained by calculation. norm ; To ensure that all queries are within the specified range, first norm Merge along the columns, remove the true labels (i.e., 0 in the true cardinality), and then logarithmically scale the remaining true labels;

[0014] The cardinality estimator constructed by connecting multi-head attention, position encoding P and pooling network in the order of position encoding, pooling layer and multi-head attention makes more accurate cardinality prediction for queries in structured query language.

[0015] Step 4: Save the optimal weights of the aggregated position encoding multi-head attention model;

[0016] Step 5: Calculate the accuracy of the aggregated position encoding multi-head attention model on the test set; merge the prediction results of all training data sets into a one-dimensional array by column, and then convert the predicted labels and true labels into a unified cardinality format. Finally, use Q-Error to represent the relative error value between the true cardinality and the predicted cardinality:

[0017]

[0018] Among them, labels represents the true cardinality, and preds represents the predicted cardinality of the model.

[0019] Furthermore, the step 2 comprises the following steps:

[0020] Step 21: Use M-estimator encoding to scale the data using median and interquartile range (IQR);

[0021] Step 22: Take the data slice x and the query Q as the input of the pre-trained aggregated positional encoding multi-head attention model, the shape of the data slice x is x = (batch size, number of attributes, attribute state dimension), the shape of the query Q is Q = (batch size, 1, dimension of query part features), process the input query Q = (batch size, 1, dimension of query part features) for each feature;

[0022] Step 23: Calculate the median of the feature and then calculate the interquartile range (IQR) of the feature:

[0023] Q.med = np.m(Q, axis = 0);

[0024] IQR=np.p(Q,75,axis=0)-np.p(Q,25,axis=0);

[0025] Among them, Q.med represents the median of the query feature, Q represents the original query data, np.m first sorts all the elements in the array, and then takes the average of the two middle elements if it is an even number, and takes the middle element as the median if it is an odd number, axis=0 means calculating the median on the column; IQR represents the interquartile range, np.p means finding the position of the element, if the position is an integer, the element is returned, if it is not an integer, the value is determined by linear interpolation to calculate the percentile, and the formula uses the 75th percentile and the 25th percentile to obtain the interquartile range;

[0026] Step 24: Subtract the median of the feature from the query feature and divide the median-reduced data by the interquartile range (IQR) of the feature:

[0027]

[0028] The query features are added to the predefined feature matrix, that is, each row corresponds to a query or data sample, and each column corresponds to the features encoded by M-estimator and the connection condition attribute features.

[0029] Furthermore, the step 3 comprises the following steps:

[0030] Step 31: Position encoding processing data feature vector x;

[0031] Step 32: Average pooling processes the feature vector after position encoding. The pooling layer receives the feature vector after position encoding processing. The formula is as follows:

[0032] output(d)=[X d-p ,X d-p+1 …,X d-p+k+1 ];

[0033] Among them, output(d) represents the output, p represents the step size, k represents the size of the pooling window, and the pooling window selects some features; then the operation of selecting the maximum value is completed in sequence by sliding on the features through the step size, and finally a new output feature output(d) is formed;

[0034] Step 33: Multi-head attention mechanism processes features; multi-head self-attention and multi-head cross-attention are used to increase the weight of feature vectors, and a multi-head attention mechanism is constructed. According to the data feature vector x in step 32, the attention output is first added to the input after layer normalization and deep residual connection. The final result is passed through a point-wise feedforward network to obtain the output feature vector;

[0035] Step 34: Predict the accuracy of database cardinality estimation; a fully connected network consisting of an exponential linear unit activation function, 128 hidden neurons, and L2 regularization applied to a weight matrix is ​​used to process the input feature vector, and a multilayer perceptron layer is formed by the same multiple fully connected networks and the last layer of fully connected networks without an activation function. The final database cardinality estimation prediction value is obtained by calling the instance reg of the multilayer perceptron layer.

[0036] Furthermore, step 4 includes the following steps:

[0037] Step 41: Create a checkpoint instance containing model parameters, optimizer state and training steps;

[0038] Step 42: Create another checkpoint manager that manages several checkpoints and can specify the maximum number of checkpoints to be saved;

[0039] Step 43: During the training process, if the validation loss of the current training batch is less than the previous loss function value, the smaller loss value replaces the previous loss value as the new updated optimal loss value, and the information of the current model state is recursively identified to save the current checkpoint;

[0040] Step 44: When the aggregated position encoding multi-head attention model is initialized, the model first determines whether there is a saved checkpoint by checking whether there is a file in the specified directory. If so, it is saved in the checkpoint file using the storage method in the checkpoint manager so that when restoring, it knows which specific position to continue training from and thus restores the current training step.

[0041] Furthermore, the step 31 includes the following steps:

[0042] Step 311: receiving input features, i.e., data features x = [X1, ..., X n ], calculate the index related to the input feature;

[0043] Step 312: multiplying the feature index by the initialization matrix to obtain the location information of the feature; the initialization matrix is ​​an all-zero matrix XZ;

[0044] Step 313: Add the position information to the original feature, the formula is:

[0045] positional_encoding[pe i ]=Embedding(i)

[0046] output=input+positional_encoding

[0047] Among them, Embedding() represents the embedding position function, i represents the position index inside the feature, input represents the original input feature plus the obtained position information, and output represents the output feature of the position encoding.

[0048] Furthermore, in step 33, in order to construct a multi-head attention mechanism, the query Q is first passed to the cross-attention mechanism, and the data feature x is used as the key Key and value Value, and the query Q is used as the query Q to generate a cross-attention output; through layer normalization and deep residual connection, the cross-attention output is added to the query, and the result is then passed through a point-wise feedforward network to obtain the output feature.

[0049] Furthermore, in step 3, the specified range is [0, 1].

[0050] Compared with the prior art, the present invention has the following advantages:

[0051] The present invention introduces a lightweight and efficient position encoding and pooling module by aggregating position encoding multi-head attention, so as to better capture the information between features.

[0052] The aggregated position coding multi-head attention in the present invention adopts the M-estimator encoding method to solve the phenomenon of extreme values ​​appearing in dynamic and complex database queries.

[0053] The aggregated position encoding multi-head attention in the present invention stacks multi-head self-attention with multi-head cross-attention to adaptively highlight the most unique features, thereby better calculating the cardinality of database queries and improving the accuracy of cardinality estimation. BRIEF DESCRIPTION OF THE DRAWINGS

[0054] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative labor.

[0055] Figure 1 This is the overall framework diagram of the aggregated position encoding multi-head attention model of the present invention. DETAILED DESCRIPTION

[0056] In order to enable those skilled in the art to better understand the scheme of the present invention, the technical scheme in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work should fall within the scope of protection of the present invention.

[0057] It should be noted that the terms "first", "second", etc. in the specification and claims of the present invention and the above-mentioned drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence. It should be understood that the data used in this way can be interchanged where appropriate, so that the embodiments of the present invention described herein can be implemented in an order other than those illustrated or described herein. In addition, the terms "including" and "having" and any variations thereof are intended to cover non-exclusive inclusions, for example, a process, method, system, product or device that includes a series of steps or units is not necessarily limited to those steps or units that are clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.

[0058] like Figure 1 As shown, the present invention provides a new cardinality estimation model, a database cardinality estimation method based on a multi-head attention mechanism, comprising the following steps:

[0059] Step 1: Obtain a data set and label of preset attributes in the database and randomly divide the data set and label into a training set, a validation set, and a test set according to a ratio of 70%, 15%, and 15%;

[0060] Step 2: Construct an aggregated position coding multi-head attention model based on the attention mechanism; first use the aggregated position coding multi-head attention model to extract features from data and structured query language queries, and reduce the probability of extreme values ​​in query features by encoding based on M-estimator, and control the features to be close to the average value of the query data; and further use it to predict the accuracy of database cardinality estimation. Step 2 includes the following steps:

[0061] Step 21: Use M-estimator encoding to scale the data using median and interquartile range (IQR);

[0062] Step 22: Take the data slice x and the query Q as the input of the pre-trained aggregated positional encoding multi-head attention model, the shape of the data slice x is x = (batch size, number of attributes, attribute state dimension), the shape of the query Q is Q = (batch size, 1, dimension of query part features), process the input query Q = (batch size, 1, dimension of query part features) for each feature;

[0063] Step 23: Calculate the median of the feature and then calculate the interquartile range IQR of the feature, as shown in the following formula, Q.med represents the median of the query feature, Q is the original query data, np.m first sorts all the elements in the array, and then takes the average of the two middle elements if it is an even number, and takes the middle element as the median if it is an odd number, axis=0 means calculating the median on the column. The IQR in the second formula stands for the interquartile range, np.p finds the position of the element, returns the element if the position is an integer, and determines the value through linear interpolation if it is not an integer, thereby calculating the percentile, and the formula uses the 75th percentile and the 25th percentile to obtain the interquartile range.

[0064] Q.med = np.m(Q, axis = 0);

[0065] IQR=np.p(Q,75,axis=0)-np.p(Q,25,axis=0);

[0066] Step 24: Subtract the median of the feature from the query feature, and divide the median-removed data by the interquartile range (IQR) of the feature, as shown in the following formula; add the query feature to the predefined feature matrix (each row corresponds to a query or data sample, and each column corresponds to the feature encoded by M-estimator and the connection condition attribute feature).

[0067]

[0068] As a preferred embodiment, step 3: preprocessing the data set; dividing the underlying database into several histograms according to one attribute one histogram and equal frequency according to one interval containing the same number of histograms and histogram characterization to preprocess the database table; dividing the data in the data set into database state and query part features according to the position index of the feature dimension and merging them into the two-dimensional array required by the aggregated position encoding multi-head attention model on the column;

[0069] The query features are obtained after encoding from M-estimator and for the queries divided into training part and test part, the mean of the queries corresponding to the training part and the test part is subtracted and divided by the standard deviation to achieve the purpose of logarithmic scaling. As shown in the following formula, Q is the feature extracted from the workload, u is the mean of the query feature, σ is the standard deviation of the query feature, and Q is calculated. norm (query features after preprocessing); To ensure that all queries are within the specified range (0-1), first norm Merge along the columns, remove the zeros in the true labels (true cardinality), and then log-scale the remaining true labels (for example, if the input is S, the result of log-scale is logS);

[0070]

[0071] The cardinality estimator constructed by connecting multi-head attention, position encoding P and pooling network in the order of position encoding, pooling layer and multi-head attention makes more accurate cardinality prediction for queries in structured query language.

[0072] Specifically, the step 3 includes the following steps:

[0073] Step 31: Position encoding processing data feature vector x; Step 31 includes the following steps:

[0074] Step 311: Receive input features, i.e., data features X = [X1, ..., X n ], calculate the index related to the input feature;

[0075] Step 312: multiply the feature index by the initialization matrix (pre-defined all-zero matrix XZ) to obtain the feature position information;

[0076] Step 313: Add the position information to the original feature. The formula is as follows:

[0077] positional_encoding[pe i] = Embedding(i);

[0078] output=input+positional_envoding;

[0079] Among them, Embedding() is the embedding position function, i represents the position index inside the feature, and input represents the output feature output obtained by adding the original input feature to the obtained position information.

[0080] Step 32: Average pooling processes the feature vector after position encoding. The pooling layer receives the feature vector after position encoding processing. The formula is as follows:

[0081] output(d)=[X d-p ,X d-p+1 …,X d-p+k+1 ];

[0082] Among them, output(d) represents the output, p represents the step size, k represents the size of the pooling window, and the pooling window selects some features; then the operation of selecting the maximum value is completed in sequence by sliding on the features through the step size, and finally a new output feature output(d) is formed;

[0083] Step 33: multi-head attention mechanism processes features; multi-head self-attention and multi-head cross-attention are used to increase the weight of the feature vector, and a multi-head attention mechanism is constructed. According to the data feature vector x in step 32, the attention output is first added to the input after layer normalization and deep residual connection, and the result is finally obtained through a point-like feedforward network to obtain the output feature vector; in step 33, in order to construct the multi-head attention mechanism, the query Q is first passed to the cross-attention mechanism, and the data feature x is used as the key Key and value Value, and the query Q is used as the query Q to generate a cross-attention output; through layer normalization and deep residual connection, the cross-attention output is added to the query, and the result is then passed through a point-like feedforward network to obtain the output feature.

[0084] Step 34: Predict the accuracy of database cardinality estimation; a fully connected network consisting of an exponential linear unit activation function, 128 hidden neurons, and L2 regularization applied to a weight matrix is ​​used to process the input feature vector, and a multilayer perceptron layer is formed by the same multiple fully connected networks and the last layer of fully connected networks without an activation function. The final database cardinality estimation prediction value is obtained by calling an instance (reg) of the multilayer perceptron layer.

[0085] Step 4: Save the optimal weights of the aggregated position encoding multi-head attention model. Step 4 includes the following steps:

[0086] Step 41: Create a checkpoint instance containing model parameters, optimizer state and training steps;

[0087] Step 42: Create another checkpoint manager that manages several checkpoints and can specify the maximum number of checkpoints to be saved;

[0088] Step 43: During the training process, if the validation loss of the current training batch is less than the previous loss function value, the smaller loss value replaces the previous loss value as the new updated optimal loss value, and the information of the current model state is recursively identified to save the current checkpoint;

[0089] Step 44: When the aggregated position encoding multi-head attention model is initialized, the model first determines whether there is a saved checkpoint by checking whether there is a file in the specified directory. If so, it is saved in the checkpoint file using the storage method in the checkpoint manager so that when restoring, it knows which specific position to continue training from and thus restores the current training step.

[0090] Step 5: Calculate the accuracy of the aggregated position encoding multi-head attention model on the test set; merge the prediction results of all training data sets into a one-dimensional array by column (transpose), then convert the predicted labels and true labels into a unified cardinality format (first remove 0, then perform logarithmic scaling), and finally use Q-Error to represent the relative error value between the true cardinality and the predicted cardinality:

[0091]

[0092] Among them, labels represents the true cardinality, and preds represents the predicted cardinality of the model.

[0093] Example 1

[0094] The experimental data used in the present invention comes from the commonly used dataset STATS in the field of cardinality estimation. This dataset is selected because it contains complex connection relationships. The dataset contains 8 attribute tables, each of which contains four to five attribute types, such as id, postid, score, creationdate and other attributes. The workload used includes two types of loads, static workload and dynamic workload, which are used to evaluate the accuracy of database cardinality estimation when facing different queries.

[0095] In the schematic diagram of the present invention, a database cardinality estimation method based on a multi-head attention mechanism includes the following steps:

[0096] Step 1: Get the dataset and labels of attributes such as id, post ID, voting type ID, creation date, user ID bounty amount reputation, and pageviews in the underlying database and randomly divide the dataset and labels into training set, validation set, and test set in a ratio of 70%, 15%, and 15%;

[0097] Step 2: Construct an aggregated position encoding multi-head attention model based on the attention mechanism; first use the aggregated position encoding multi-head attention model to extract features from data and structured query language queries, and reduce the probability of extreme values ​​in query features by encoding based on M-estimator, so as to control the features near the average value of the query data; further use it to predict the accuracy of database cardinality estimation;

[0098] Step 21: Use M-estimator encoding to scale the data using median and interquartile range (IQR);

[0099] Step 22: Take the data slice x and the query Q as the input of the pre-trained aggregated positional encoding multi-head attention model, the shape of the data slice x is x = (batch size, number of attributes, attribute state dimension), the shape of the query Q is Q = (batch size, 1, dimension of query part features), process the input query Q = (batch size, 1, dimension of query part features) for each feature;

[0100] Step 23: Calculate the median of the feature and then calculate the interquartile range IQR of the feature, as shown in the following formula, Q.med represents the median of the query feature, Q is the original query data, np.m first sorts all the elements in the array, and then takes the average of the two middle elements if it is an even number, and takes the middle element as the median if it is an odd number, axis=0 means calculating the median on the column. The IQR in the second formula stands for the interquartile range, np.p finds the position of the element, returns the element if the position is an integer, and determines the value through linear interpolation if it is not an integer, thereby calculating the percentile, and the formula uses the 75th percentile and the 25th percentile to obtain the interquartile range.

[0101] Q.med = np.m(Q, axis = 0)

[0102] IQR=np.p(Q,75,axis=0)-np.p(Q,25,axis=0)

[0103] Step 24: Subtract the median of the feature from the query feature, and divide the median-removed data by the interquartile range (IQR) of the feature, as shown in the following formula; add the query feature to the predefined feature matrix (each row corresponds to a query or data sample, and each column corresponds to the feature encoded by M-estimator and the connection condition attribute feature).

[0104]

[0105] Step 3: Preprocess the data set; divide the underlying database into several histograms according to one attribute and one histogram and equal frequency according to the same number of items in one interval, and perform histogram featurization to preprocess the database table; divide the data in the data set into database status and query partial features according to the position index of the feature dimension and merge them into the two-dimensional array required by the aggregated position encoding multi-head attention model on the column;

[0106] The query features are obtained after encoding from M-estimator and for the queries divided into training part and test part, the mean corresponding to the query is subtracted and divided by the standard deviation to achieve the purpose of logarithmic scaling. As shown in the following formula, Q is the feature extracted from the workload, u is the mean of the query feature, and σ is the standard deviation of the query feature. After calculation, Q norm (query features after preprocessing); To ensure that all queries are within the specified range (0-1), first norm Merge along the columns, remove the zeros in the true labels (true cardinality), and then log-scale the remaining true labels (for example, if the input is S, the result of log-scale is logS);

[0107]

[0108] The cardinality estimator constructed by connecting multi-head attention with position encoding P and pooling network in the order of position encoding, pooling layer and multi-head attention makes more accurate cardinality prediction for queries of structured query language.

[0109] Step 311: Receive input features, i.e., data features X = [X1, ..., X n ], calculate the index related to the input feature;

[0110] Step 312: multiply the feature index by the initialization matrix (pre-defined all-zero matrix XZ) to obtain the feature position information;

[0111] Step 313: Add the position information to the original feature. The formula is as follows:

[0112] positionl_encoding[pe i ]=Embedding(i)

[0113] output=input+positional_encoding

[0114] Among them, Embedding() is the embedding position function, i represents the position index inside the feature, and input represents the output feature output obtained by adding the original input feature to the obtained position information.

[0115] Step 32: Average pooling processes the feature vector after position encoding. The pooling layer receives the feature vector after position encoding processing. The formula is as follows:

[0116] output(d)=[X d-p ,X d-p+1 …,X d-p+k+1 ]

[0117] Among them, output(d) represents the output, p represents the step size, k represents the size of the pooling window, and the pooling window selects some features; then the operation of selecting the maximum value is completed in sequence by sliding on the features through the step size, and finally a new output feature output(d) is formed;

[0118] Step 33: Multi-head attention mechanism processes features; multi-head self-attention and multi-head cross-attention are used to increase the weight of feature vectors, and a multi-head attention mechanism is constructed. According to the data feature vector x in step 32, the attention output is first added to the input after layer normalization and deep residual connection. The final result is passed through a point-wise feedforward network to obtain the output feature vector;

[0119] Step 34: Predict the accuracy of database cardinality estimation; a fully connected network consisting of an exponential linear unit activation function, 128 hidden neurons, and L2 regularization applied to a weight matrix is ​​used to process the input feature vector, and then a multilayer perceptron layer is composed of multiple fully connected networks and a fully connected network without an activation function in the last layer, and the final database cardinality estimation prediction value is obtained by calling the instance (reg) of the multilayer perceptron layer. Step 35: Predict the accuracy of database cardinality estimation: a fully connected network consisting of an exponential linear unit activation function, 128 hidden neurons, and L2 regularization applied to a weight matrix is ​​used to process the input feature vector, and then a multilayer perceptron layer is composed of multiple fully connected networks and a fully connected network without an activation function in the last layer, and the final cardinality estimation prediction value is obtained by calling the instance (reg) of the multilayer perceptron layer.

[0120] Step 4: Save the optimal weights of the aggregated position encoding multi-head attention model;

[0121] Step 41: Create a checkpoint instance containing model parameters, optimizer state and training steps;

[0122] Step 42: Create another checkpoint manager that manages several checkpoints and can specify the maximum number of checkpoints to be saved;

[0123] Step 43: During the training process, if the validation loss of the current training batch is less than the previous loss function value, the smaller loss value replaces the previous loss value as the new updated optimal loss value, and the information of the current model state is recursively identified to save the current checkpoint;

[0124] Step 44: When the aggregated position encoding multi-head attention model is initialized, the model first determines whether there is a saved checkpoint by checking whether there is a file in the specified directory. If so, it is saved in the checkpoint file using the storage method in the checkpoint manager so that when restoring, it knows which specific position to continue training from and thus restores the current training step.

[0125] Step 5: Calculate the accuracy of the aggregated position encoding multi-head attention model on the test set; merge the prediction results of all training data sets into a one-dimensional array by column (transpose), then convert the predicted labels and true labels into a unified cardinality format (first remove 0, then perform logarithmic scaling), and finally use Q-Error to represent the relative error value between the true cardinality and the predicted cardinality. As shown in the following formula, labels represents the true cardinality and preds represents the predicted cardinality of the model.

[0126]

[0127] According to the above steps, the present invention proposes a new attention mechanism model for performing the cardinality estimation task of database data and queries, namely, the aggregated position encoding multi-head attention model. The aggregated position encoding multi-head attention uses the position encoding module to identify the internal information of the feature, and uses multi-head self-attention and multi-head cross-attention to perform cardinality estimation, thereby well exploring the advantages of the attention mechanism in solving the database cardinality estimation problem. In addition, the experimental results show that the accuracy is improved by 6% compared with the most advanced attention method, and it performs well on the STATS dataset, with the highest accuracy of 0.9358%.

[0128] The serial numbers of the above embodiments of the present invention are only for description and do not represent the advantages or disadvantages of the embodiments.

[0129] In the above embodiments of the present invention, the description of each embodiment has its own emphasis. For parts that are not described in detail in a certain embodiment, reference can be made to the relevant descriptions of other embodiments.

[0130] In the several embodiments provided in this application, it should be understood that the disclosed technical content can be implemented in other ways. Among them, the device embodiments described above are only schematic. For example, the division of the units can be a logical function division. There may be other division methods in actual implementation. For example, multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be through some interfaces, indirect coupling or communication connection of units or modules, which can be electrical or other forms.

[0131] The units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed on multiple units. Some or all of the units may be selected according to actual needs to achieve the purpose of the present embodiment.

[0132] In addition, each functional unit in each embodiment of the present invention may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit. The above-mentioned integrated unit may be implemented in the form of hardware or in the form of software functional units.

[0133] If the integrated unit is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention is essentially or the part that contributes to the prior art or all or part of the technical solution can be embodied in the form of a software product, and the computer software product is stored in a storage medium, including a number of instructions to enable a computer device (which can be a personal computer, server or network device, etc.) to perform all or part of the steps of the method described in each embodiment of the present invention. The aforementioned storage medium includes: U disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), mobile hard disk, disk or optical disk and other media that can store program codes.

[0134] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or replace some or all of the technical features therein with equivalents. However, these modifications or replacements do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present invention.

Claims

1. A database cardinality estimation method based on a multi-head attention mechanism, characterized in that: The following steps are involved: Step 1: Obtain a data set and label of preset attributes in the database and randomly divide the data set and label into a training set, a validation set, and a test set according to a ratio of 70%, 15%, and 15%; Step 2: Construct an aggregated position encoding multi-head attention model based on the attention mechanism; The aggregated position encoding multi-head attention model is used to extract features from data and structured query language queries. The probability of extreme values ​​appearing in query features is reduced by M-estimator encoding, and the features are controlled at the average value of the probability of extreme values ​​appearing, which is used to predict the accuracy of database cardinality estimation. Step 3: Preprocess the data set; divide the underlying database into several histograms according to one attribute and one histogram and equal frequency according to the same number of items in one interval, and perform histogram featurization to preprocess the database table; divide the data in the data set into database status and query partial features according to the position index of the feature dimension and merge them into the two-dimensional array required by the aggregated position encoding multi-head attention model on the column; The query features are obtained after encoding from M-estimator and for the queries divided into training part and test part, the mean corresponding to the queries in the training part and the test part is subtracted and divided by the standard deviation to achieve the purpose of logarithmic scaling; Among them, Q represents the feature extracted from the workload, u represents the mean of the query feature, and σ represents the standard deviation of the query feature. The query feature Q after preprocessing is obtained by calculation. norm ; To ensure that all queries are within the specified range, first norm Merge along the columns, remove the true labels (i.e., 0 in the true cardinality), and then logarithmically scale the remaining true labels; The cardinality estimator constructed by connecting multi-head attention, position encoding P and pooling network in the order of position encoding, pooling layer and multi-head attention makes more accurate cardinality prediction for queries in structured query language. Step 4: Save the optimal weights of the aggregated position encoding multi-head attention model; Step 5: Calculate the accuracy of the aggregated position encoding multi-head attention model on the test set; merge the prediction results of all training data sets into a one-dimensional array by column, and then convert the predicted labels and true labels into a unified cardinality format. Finally, use Q-Error to represent the relative error value between the true cardinality and the predicted cardinality: Among them, labels represents the true cardinality, and preds represents the predicted cardinality of the model.

2. The database cardinality estimation method based on the multi-head attention mechanism according to claim 1 is characterized in that: The step 2 comprises the following steps: Step 21: Use M-estimator encoding to scale the data using median and interquartile range (IQR); Step 22: Take the data slice x and the query Q as the input of the pre-trained aggregated positional encoding multi-head attention model, the shape of the data slice x is x = (batch size, number of attributes, attribute state dimension), the shape of the query Q is Q = (batch size, 1, dimension of query part features), process the input query Q = (batch size, 1, dimension of query part features) for each feature; Step 23: Calculate the median of the feature and then calculate the interquartile range (IQR) of the feature: Q.med = np.m(Q, axis = 0); IQR=np.p(Q,75,axis=0)-np.p(Q,25,axis=0); Among them, Q.med represents the median of the query feature, Q represents the original query data, np.m first sorts all the elements in the array, and then takes the average of the two middle elements if it is an even number, and takes the middle element as the median if it is an odd number, axis=0 means calculating the median on the column; IQR represents the interquartile range, np.p means finding the position of the element, if the position is an integer, the element is returned, if it is not an integer, the value is determined by linear interpolation to calculate the percentile, and the formula uses the 75th percentile and the 25th percentile to obtain the interquartile range; Step 24: Subtract the median of the feature from the query feature and divide the median-reduced data by the interquartile range (IQR) of the feature: The query features are added to the predefined feature matrix, that is, each row corresponds to a query or data sample, and each column corresponds to the features encoded by M-estimator and the connection condition attribute features.

3. The database cardinality estimation method based on the multi-head attention mechanism according to claim 1 is characterized in that: The step 3 comprises the following steps: Step 31: Position encoding processing data feature vector x; Step 32: Average pooling processes the feature vector after position encoding. The pooling layer receives the feature vector after position encoding processing. The formula is as follows: output(d)=[X d-p ,X d-p+1 …,X d-p+k+1 ]; Among them, output(d) represents the output, p represents the step size, k represents the size of the pooling window, and the pooling window selects some features; then the operation of selecting the maximum value is completed in sequence by sliding on the features through the step size, and finally a new output feature output(d) is formed; Step 33: Multi-head attention mechanism processes features; multi-head self-attention and multi-head cross-attention are used to increase the weight of feature vectors, and a multi-head attention mechanism is constructed. According to the data feature vector x in step 32, the attention output is first added to the input after layer normalization and deep residual connection. The final result is passed through a point-wise feedforward network to obtain the output feature vector; Step 34: Predict the accuracy of database cardinality estimation; a fully connected network consisting of an exponential linear unit activation function, 128 hidden neurons, and L2 regularization applied to a weight matrix is ​​used to process the input feature vector, and a multilayer perceptron layer is formed by the same multiple fully connected networks and the last layer of fully connected networks without an activation function. The final database cardinality estimation prediction value is obtained by calling the instance reg of the multilayer perceptron layer.

4. The database cardinality estimation method based on the multi-head attention mechanism according to claim 1, characterized in that: The step 4 comprises the following steps: Step 41: Create a checkpoint instance containing model parameters, optimizer state and training steps; Step 42: Create another checkpoint manager that manages several checkpoints and can specify the maximum number of checkpoints to be saved; Step 43: During the training process, if the validation loss of the current training batch is less than the previous loss function value, the smaller loss value replaces the previous loss value as the new updated optimal loss value, and the information of the current model state is recursively identified to save the current checkpoint; Step 44: When the aggregated position encoding multi-head attention model is initialized, the model first determines whether there is a saved checkpoint by checking whether there is a file in the specified directory. If so, it is saved in the checkpoint file using the storage method in the checkpoint manager so that when restoring, it knows which specific position to continue training from and thus restores the current training step.

5. The database cardinality estimation method based on the multi-head attention mechanism according to claim 2 is characterized in that: The step 31 comprises the following steps: Step 311: Receive input features, i.e., data features X = [X1, ..., X n ], calculate the index related to the input feature; Step 312: multiplying the feature index by the initialization matrix to obtain the location information of the feature; the initialization matrix is ​​an all-zero matrix XZ; Step 313: Add the position information to the original feature, the formula is: positional_encoding[pe i ]=Embedding(i) output=input+positional_encoding Among them, Embedding() represents the embedding position function, i represents the position index inside the feature, input represents the original input feature plus the obtained position information, and output represents the output feature of the position encoding.

6. The database cardinality estimation method based on the multi-head attention mechanism according to claim 2 is characterized in that: In step 33, in order to construct a multi-head attention mechanism, the query Q is first passed to the cross-attention mechanism, and the data feature x is used as the key Key and the value Value, and the query Q is used as the query Q to generate a cross-attention output; through layer normalization and deep residual connection, the cross-attention output is added to the query, and the result is then passed through a point-wise feedforward network to obtain the output feature.

7. The database cardinality estimation method based on the multi-head attention mechanism according to claim 1, characterized in that: In step 3, the specified range is [0, 1].

Images