Database cardinality estimation method based on multi-head attention mechanism

Abstract

This invention provides a database cardinality estimation method based on a multi-head attention mechanism, belonging to the technical field of database data processing. It includes the following steps: acquiring a dataset and labels; preprocessing the dataset; constructing a multi-head network based on an attention mechanism; storing the optimal weights of the aggregated positional encoding multi-head attention model; and calculating the accuracy of the network on a test set. The aggregated positional encoding multi-head attention model of this invention introduces a lightweight and efficient positional encoding module. It integrates multi-head self-attention and multi-head cross-attention, which are used to capture data relationships and query dependencies, respectively. The aggregated positional encoding multi-head attention model stacks positional encoding with pooling layers to adaptively highlight the most unique internal information of features, thereby better calculating the cardinality of the corresponding query and improving the accuracy of database cardinality estimation.

Description

Technical Field

[0001] This invention relates to the technical field of data processing in databases, and more particularly to a database cardinality estimation method based on a multi-head attention mechanism. Background Technology

[0002] The query optimizer plays a crucial role in a DBMS, and cardinality estimation is a vital component of it. The query optimizer generates an optimal execution plan based on the logical structure of the query and the physical organization of the database, thereby improving query execution efficiency and performance. Cardinality estimation aims to obtain the cardinality of the query, and the obtained cardinality is one of the important parameters for execution plan selection, index selection, and query optimization performance.

[0003] In recent years, deep learning-based cardinality estimation has gradually replaced traditional estimation methods and addressed their shortcomings. Deep learning-based database cardinality estimation can be divided into data-driven and query-driven approaches. Data-driven cardinality estimation primarily involves statistically analyzing underlying data to construct complex data structures to store information, and then obtaining the cardinality by parsing query expressions. However, it lacks universality in data sampling and often suffers from low accuracy when handling large, dynamic workloads. Query-driven cardinality estimation uses supervised methods to learn the correspondence between query features and cardinality, thereby estimating the cardinality. It can employ either regression models or graph structures, transforming the cardinality estimation problem into a graphical one.

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

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

[0006] The technical means employed in this invention are as follows:

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

[0008] Step 1: Obtain the dataset and labels with preset attributes from the database, and randomly divide the dataset and labels into training set, validation set and test set according to the ratio of 70%, 15% and 15% respectively;

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

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

[0011] The query features are obtained after encoding by the M-estimator. For queries that are divided into training and testing parts, the mean of the query corresponding to the training and testing parts is subtracted and divided by the standard deviation to achieve logarithmic scaling.

[0012]

[0013] Where 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 preprocessed query feature Q is calculated. norm To ensure all queries are within the specified range, first set Q... norm Merge along the columns, then remove the zeros from the true labels (i.e., the true cardinality), and then logarithmically scale the remaining true labels;

[0014] A cardinality estimator is constructed by connecting multi-head attention, positional encoding P, and pooling network in the order of positional encoding, then pooling layer, and finally multi-head attention to make higher accuracy cardinality prediction for queries in structured query language.

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

[0016] Step 5: Calculate the accuracy of the aggregated positional encoding multi-head attention model on the test set; merge the prediction results of all datasets in the training set into a one-dimensional array according to columns, then convert the predicted labels and the true labels into a unified cardinality format, and finally use Q-Error to represent the relative error value between the true cardinality and the predicted cardinality:

[0017]

[0018] Here, labels represent the true cardinality, and preds represent the model's prediction cardinality.

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

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

[0021] Step 22: Use the data slice x and query Q as input to the pre-trained aggregation location encoding multi-head attention model. The shape of the data slice x is x = (batch size, number of attributes, dimension of attribute state), and the shape of the query Q is Q = (batch size, 1, dimension of query partial feature). Process the input query Q = (batch size, 1, dimension of query partial feature) 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] Here, Q.med represents the median of the query feature, Q represents the original query data, np.m first sorts all elements in the array, then if the number is even, it takes the average of the two middle elements, and if the number is odd, it takes the middle element as the median, axis=0 indicates that the median is calculated on the column; IQR represents the interquartile range, np.p indicates finding the position of the element, if the position is an integer, it returns the element, if it is not an integer, it determines the value through linear interpolation, and thus calculates 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 query feature from the median-removed data, and divide the median-removed data by the interquartile range (IQR) of that feature:

[0027]

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

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

[0030] Step 31: Encode the data feature vector x using location encoding;

[0031] Step 32: Average pooling is used to process the position-encoded feature vectors. The pooling layer receives the position-encoded feature vectors, as shown in the following formula:

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

[0033] Where output(d) represents the output, p represents the step size, and k represents the size of the pooling window. The pooling window selects a subset of features. The step size is then used to slide across the features to select the maximum value in turn, ultimately forming a new output feature output(d).

[0034] Step 33: Multi-head attention mechanism for feature processing; The weight of the feature vector is increased by multi-head self-attention and multi-head cross attention to construct a multi-head attention mechanism. Based on the data feature vector x in Step 32, the attention output is added to the input after layer normalization and deep residual connection. Finally, the result is passed through a point-like feedforward network to obtain the output feature vector.

[0035] Step 34: Predict the accuracy of database cardinality estimation; The input feature vector is processed by a fully connected network consisting of an exponential linear unit activation function, 128 hidden neurons, and L2 regularization applied to the weight matrix. Then, a multilayer perceptron layer is formed by multiple fully connected networks and a last fully connected network 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 check manager that manages several checkpoints and allows you to specify the maximum number of checkpoints to save;

[0039] Step 43: If the validation loss of the current training batch is less than the previous loss function value during training, the smaller loss value is used to replace the previous loss value as the new optimal loss value for updating, and the information of the current model state is identified in a recursive manner to save the current checkpoint.

[0040] Step 44: Encoding the multi-head attention model at the aggregation location. During model initialization, the model first checks whether there are any saved checkpoints by checking if there are any files in the specified directory. If there are, the checkpoint manager uses the storage method to save them in the checkpoint file so that it knows from which specific position to continue training during recovery, thereby restoring the current training step.

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

[0042] Step 311: Receive the input features, i.e., the data features x = [X1, ..., X]. n After that, calculate the indices related to the input features;

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

[0044] Step 313: Add the location information to the original features, using the following formula:

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

[0046] output=input+positional_encoding

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

[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 and value, while the query Q is used as the query Q to generate the 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.

[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] This invention introduces a lightweight and efficient position encoding and pooling module by aggregating position encoding multi-head attention, thereby better capturing information within features.

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

[0053] The aggregated positional encoding multi-head attention in this invention stacks multi-head self-attention and 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. Attached Figure Description

[0054] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0055] Figure 1 This is a general framework diagram of the multi-head attention model for aggregated position encoding in this invention. Detailed Implementation

[0056] To enable those skilled in the art to better understand the present invention, the technical solutions of the present invention will be clearly and completely described below with reference to the accompanying drawings of the embodiments of the present invention. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort 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, claims, and accompanying drawings of this invention are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of the invention described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover a non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.

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

[0059] Step 1: Obtain the dataset and labels with preset attributes from the database, and randomly divide the dataset and labels into training set, validation set and test set according to the ratio of 70%, 15% and 15% respectively;

[0060] Step 2: Construct an aggregated positional encoding multi-head attention model based on an attention mechanism. First, the aggregated positional encoding multi-head attention model is used to extract features from the data and the query in the structured query language. By using M-estimator encoding, the probability of extreme values ​​in the query features is reduced, keeping the features close to the average value of the query data. This is further used 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: Use the data slice x and query Q as input to the pre-trained aggregation location encoding multi-head attention model. The shape of the data slice x is x = (batch size, number of attributes, dimension of attribute state), and the shape of the query Q is Q = (batch size, 1, dimension of query partial feature). Process the input query Q = (batch size, 1, dimension of query partial feature) 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 formula below. Q.med represents the median of the query feature, Q is the original query data, np.m first sorts all elements in the array, then if the number is even, takes the average of the two middle elements; if it's odd, takes the middle element as the median. axis=0 indicates that the median is calculated on the column. In the second formula, IQR represents the interquartile range, and np.p finds the position of the element. If the position is an integer, it returns the element; otherwise, it uses linear interpolation to determine the value, thus calculating the percentile. The formula uses the 75th and 25th percentiles 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 query feature from the query feature, and divide the median-removed data by the interquartile range (IQR) of the feature, as shown in the formula below; 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 the M-estimator and the connection condition attribute feature).

[0067]

[0068] Preferably, step 3: preprocess the dataset; divide the underlying database into several histograms according to one attribute and one histogram, and in an equal frequency manner, according to an interval containing the same number of histograms, and perform histogram feature generation to preprocess the database table; divide the data in the dataset into database status and query part features according to the position index of the feature dimension, and merge them on the column into a two-dimensional array required by the aggregated position encoding multi-head attention model;

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

[0070]

[0071] A cardinality estimator is constructed by connecting multi-head attention, positional encoding P, and pooling network in the order of positional encoding, then pooling layer, and finally multi-head attention to make higher accuracy cardinality prediction for queries in structured query language.

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

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

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

[0075] Step 312: Multiply the feature index by the initialization matrix (a predefined all-zero matrix XZ) to obtain the feature's location information;

[0076] Step 313: Add the location information to the original feature, as shown in the following formula:

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

[0078] output=input+positional_envoding;

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

[0080] Step 32: Average pooling is used to process the position-encoded feature vectors. The pooling layer receives the position-encoded feature vectors, as shown in the following formula:

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

[0082] Where output(d) represents the output, p represents the step size, and k represents the size of the pooling window. The pooling window selects a subset of features. The step size is then used to slide across the features to select the maximum value in turn, ultimately forming a new output feature output(d).

[0083] Step 33: Multi-head attention mechanism for feature processing; The weights of the feature vector are increased through multi-head self-attention and multi-head cross-attention to construct a multi-head attention mechanism. Based on the data feature vector x from Step 32, the attention output is first added to the input after layer normalization and deep residual connections. The final result is passed through a point-like feedforward network to obtain the output feature vector. In Step 33, to construct the multi-head attention mechanism, the query Q is first passed to the cross-attention mechanism, using the data feature x as the key and value, while the query Q is used as the query Q to generate the cross-attention output. Through layer normalization and deep residual connections, 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; The input feature vector is processed by a fully connected network consisting of an exponential linear unit activation function, 128 hidden neurons, and L2 regularization applied to the weight matrix. Then, a multilayer perceptron layer is formed by multiple fully connected networks and a last fully connected network without an activation function. The final database cardinality estimation prediction value is obtained by calling the instance (reg) of the multilayer perceptron layer.

[0085] Step 4: Save the optimal weights of the aggregation 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 check manager that manages several checkpoints and allows you to specify the maximum number of checkpoints to save;

[0088] Step 43: If the validation loss of the current training batch is less than the previous loss function value during training, the smaller loss value is used to replace the previous loss value as the new optimal loss value for updating, and the information of the current model state is identified in a recursive manner to save the current checkpoint.

[0089] Step 44: Encoding the multi-head attention model at the aggregation location. During model initialization, the model first checks whether there are any saved checkpoints by checking if there are any files in the specified directory. If there are, the checkpoint manager uses the storage method to save them in the checkpoint file so that it knows from which specific position to continue training during recovery, thereby restoring the current training step.

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

[0091]

[0092] Here, labels represent the true cardinality, and preds represent the model's prediction cardinality.

[0093] Example 1

[0094] The experimental data used in this invention comes from STATS, a commonly used dataset in the field of cardinality estimation. This dataset was chosen because it contains complex join relationships. The dataset contains eight attribute tables, each with four to five attribute types, such as id, postid, score, and creationdate. The workload employed includes both static and dynamic workloads to evaluate the accuracy of this method in estimating database cardinality under different queries.

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

[0096] Step 1: Obtain the dataset and tags from the underlying database, including attributes such as ID, post ID, voting type ID, creation date, user ID, bounty amount, reputation, and pageviews. Then, randomly divide the dataset and tags into training set, validation set, and test set according to a ratio of 70%, 15%, and 15%.

[0097] Step 2: Construct an aggregated positional encoding multi-head attention model based on the attention mechanism; First, use the aggregated positional encoding multi-head attention model to extract features from the data and the structured query language query. By using M-estimator encoding, the probability of extreme values ​​in the query features is reduced, and the features are controlled to be close to the average value of the query data; This is further used 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: Use the data slice x and query Q as input to the pre-trained aggregation location encoding multi-head attention model. The shape of the data slice x is x = (batch size, number of attributes, dimension of attribute state), and the shape of the query Q is Q = (batch size, 1, dimension of query partial feature). Process the input query Q = (batch size, 1, dimension of query partial feature) 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 formula below. Q.med represents the median of the query feature, Q is the original query data, np.m first sorts all elements in the array, then if the number is even, takes the average of the two middle elements; if it's odd, takes the middle element as the median. axis=0 indicates that the median is calculated on the column. In the second formula, IQR represents the interquartile range, and np.p finds the position of the element. If the position is an integer, it returns the element; otherwise, it uses linear interpolation to determine the value, thus calculating the percentile. The formula uses the 75th and 25th percentiles 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 query feature from the query feature, and divide the median-removed data by the interquartile range (IQR) of the feature, as shown in the formula below; 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 the M-estimator and the connection condition attribute feature).

[0104]

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

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

[0107]

[0108] A cardinality estimator, constructed by connecting multi-head attention, positional encoding P, and pooling network in the order of positional encoding, then pooling layer, and finally multi-head attention, can make more accurate cardinality predictions for queries in structured query language.

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

[0110] Step 312: Multiply the feature index by the initialization matrix (a predefined all-zero matrix XZ) to obtain the feature's location information;

[0111] Step 313: Add the location information to the original feature, as shown in the following formula:

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

[0113] output=input+positional_encoding

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

[0115] Step 32: Average pooling is used to process the position-encoded feature vectors. The pooling layer receives the position-encoded feature vectors, as shown in the following formula:

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

[0117] Where output(d) represents the output, p represents the step size, and k represents the size of the pooling window. The pooling window selects a subset of features. The step size is then used to slide across the features to select the maximum value in turn, ultimately forming a new output feature output(d).

[0118] Step 33: Multi-head attention mechanism for feature processing; The weight of the feature vector is increased by multi-head self-attention and multi-head cross attention to construct a multi-head attention mechanism. Based on the data feature vector x in Step 32, the attention output is added to the input after layer normalization and deep residual connection. Finally, the result is passed through a point-like feedforward network to obtain the output feature vector.

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

[0120] Step 4: Save the optimal weights of the aggregation 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 check manager that manages several checkpoints and allows you to specify the maximum number of checkpoints to save;

[0123] Step 43: If the validation loss of the current training batch is less than the previous loss function value during training, the smaller loss value is used to replace the previous loss value as the new optimal loss value for updating, and the information of the current model state is identified in a recursive manner to save the current checkpoint.

[0124] Step 44: Encoding the multi-head attention model at the aggregation location. During model initialization, the model first checks whether there are any saved checkpoints by checking if there are any files in the specified directory. If there are, the checkpoint manager uses the storage method to save them in the checkpoint file so that it knows from which specific position to continue training during recovery, thereby restoring the current training step.

[0125] Step 5: Calculate the accuracy of the aggregated positional encoding multi-head attention model on the test set; merge the prediction results of all datasets in the training set into a one-dimensional array (transpose), then convert the predicted labels and ground truth labels into a unified cardinality format (remove zeros first, then perform logarithmic scaling), and finally use Q-Error to represent the relative error between the ground truth cardinality and the predicted cardinality. As shown in the formula below, labels represent the ground truth cardinality, and preds represent the model's predicted cardinality.

[0126]

[0127] Based on the above steps, this invention proposes a novel attention mechanism for cardinality estimation tasks in database data and queries: a model of aggregated positional encoding multi-head attention. Aggregated positional encoding multi-head attention utilizes positional encoding modules to identify internal information of features and employs multi-head self-attention and multi-head cross-attention for cardinality estimation, thus effectively exploring the advantages of attention mechanisms in solving database cardinality estimation problems. Furthermore, experimental results show that compared to state-of-the-art attention methods, accuracy is improved by 6%, and it exhibits superior performance on the STATS dataset, achieving a maximum accuracy of 0.9358%.

[0128] The sequence numbers of the above embodiments of the present invention are for descriptive purposes only and do not represent the superiority or inferiority of the embodiments.

[0129] In the above embodiments of the present invention, the descriptions of each embodiment have different focuses. For parts not described in detail in a certain embodiment, please refer 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. The device embodiments described above are merely illustrative; for example, the division of units can be a logical functional division, and in actual implementation, there may be other division methods. For instance, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the displayed or discussed mutual coupling, direct coupling, or communication connection may be through some interfaces; the indirect coupling or communication connection between units or modules may be electrical or other forms.

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

[0132] Furthermore, the functional units in the various embodiments of the present invention can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The integrated unit can be implemented in hardware or as a software functional unit.

[0133] If the integrated unit is implemented as 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, in essence, 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. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of the present invention. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, read-only memory (ROM), random access memory (RAM), portable hard drives, magnetic disks, or optical disks.

[0134] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, and not to limit them; although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some or all of the technical features; and these modifications or substitutions 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 multi-head attention mechanism, characterized in that, Includes the following steps: Step 1: Obtain the dataset and labels with preset attributes from the database, and randomly divide the dataset and labels into training set, validation set and test set according to the ratio of 70%, 15% and 15% respectively; Step 2: Construct an aggregated positional encoding multi-head attention model based on the attention mechanism; use the aggregated positional encoding multi-head attention model to extract features from the data and the query of the structured query language, reduce the probability of extreme values ​​in the query features by M-estimator encoding, and control the features to the average probability of extreme values, which is used to predict the accuracy of database cardinality estimation. Step 3: Preprocess the dataset; divide the underlying database into histograms according to one attribute and one histogram, and divide the histograms into several histograms according to the same frequency and the same number of items in an interval, and perform histogram featureization to preprocess the database tables; divide the data in the dataset into database state and query part features according to the position index of the feature dimension, and merge them on the column into a two-dimensional array required by the aggregated position encoding multi-head attention model; The query features are obtained after encoding by the M-estimator. For queries that are divided into training and testing parts, the mean of the query corresponding to the training and testing parts is subtracted and divided by the standard deviation to achieve logarithmic scaling. ； in, Q This represents the features extracted from the workload. u This represents the mean of the query feature. The standard deviation of the query features is used to calculate the preprocessed query features. Q norm To ensure all queries are within the specified range, first... Q norm Merge along the columns, then remove the zeros from the true labels (i.e., the true cardinality), and then logarithmically scale the remaining true labels; A cardinality estimator is constructed by connecting multi-head attention, positional encoding P, and pooling network in the order of positional encoding, then pooling layer, and finally multi-head attention to make higher accuracy cardinality prediction for queries in structured query language. Step 4: Save the optimal weights of the aggregation position encoding multi-head attention model; Step 5: Calculate the accuracy of the aggregated positional encoding multi-head attention model on the test set; merge the prediction results of all datasets in the training set into a one-dimensional array according to columns, then convert the predicted labels and ground truth labels into a unified cardinality format, and finally use... Q-Error This represents the relative error between the true base and the predicted base. ； in, labels Represents the true cardinality. preds This indicates the prediction base of the model.

2. The database cardinality estimation method based on multi-head attention mechanism according to claim 1, characterized in that, Step 2 includes the following steps: Step 21: Use M-estimator encoding with median and interquartile range IQR Scaling data; Step 22: Slice the data x and query Q The shape of the data slice x is used as input to the pre-trained aggregation position encoding multi-head attention model. Query Q The shape is For each feature processing input query ); Step 23: Calculate the median of the characteristic and then calculate the interquartile range of the characteristic. IQR : ； ； in, Q.med This represents the median of the query feature. Q This represents the original query data. np.m First, sort all elements in the array. Then, if the number is even, take the average of the two middle elements; if the number is odd, take the middle element as the median. This indicates that the median is calculated in the column. IQR This represents the interquartile range. np.p This indicates that the position of an element is found. If the position is an integer, the element is returned. If it is not an integer, the value is determined by linear interpolation, and then the percentile is calculated. The formula uses the 75th percentile and the 25th percentile to obtain the interquartile difference. Step 24: Subtract the median of the query feature from the median-removed data, and then divide the median-removed data by the interquartile range of the feature. IQR : ； The query features are added to a predefined feature matrix, where each row corresponds to a query or data sample, and each column represents the features encoded by the M-estimator and the connection condition attribute features.

3. The database cardinality estimation method based on multi-head attention mechanism according to claim 1, characterized in that, Step 3 includes the following steps: Step 31: Location encoding of data feature vectors x ; Step 32: Average pooling is used to process the position-encoded feature vectors. The pooling layer receives the position-encoded feature vectors, as shown in the following formula: ； in, output ( d ) indicates output. p Indicates step size, k The size of the pooling window is indicated, and the pooling window selects a subset of features. Then, by sliding the stride across the features, the maximum values ​​are selected sequentially, ultimately forming new output features. output ( d ); Step 33: Multi-head attention mechanism for feature processing; The weight of the feature vector is increased by multi-head self-attention and multi-head cross attention to construct a multi-head attention mechanism. Based on the data feature vector x in Step 32, the attention output is added to the input after layer normalization and deep residual connection. Finally, the result is passed through a point-like feedforward network to obtain the output feature vector. Step 34: Predict the accuracy of database cardinality estimation; the input feature vector is processed by a fully connected network consisting of an exponential linear unit activation function, 128 hidden neurons, and L2 regularization applied to the weight matrix. A multilayer perceptron layer is then formed by multiple fully connected networks and a final fully connected network without an activation function. An instance of the multilayer perceptron layer is then invoked. reg To obtain the final estimated database cardinality forecast.

4. The database cardinality estimation method based on multi-head attention mechanism according to claim 1, characterized in that, Step 4 includes the following steps: Step 41: Create a checkpoint instance containing model parameters, optimizer state, and training steps; Step 42: Create another check manager that manages several checkpoints and allows you to specify the maximum number of checkpoints to save; Step 43: If the validation loss of the current training batch is less than the previous loss function value during training, the smaller loss value is used to replace the previous loss value as the new optimal loss value for updating, and the information of the current model state is identified in a recursive manner to save the current checkpoint. Step 44: Encoding the multi-head attention model at the aggregation location. During model initialization, the model first checks whether there are any saved checkpoints by checking if there are any files in the specified directory. If there are, the checkpoint manager uses the storage method to save them in the checkpoint file so that it knows from which specific position to continue training during recovery, thereby restoring the current training step.

5. The database cardinality estimation method based on multi-head attention mechanism according to claim 3, characterized in that, Step 31 includes the following steps: Step 311: Receive input features, i.e., data features Then, calculate the indices related to the input features; Step 312: Multiply the feature index by the initialization matrix to obtain the feature's location information; the initialization matrix is ​​an all-zero matrix XZ; Step 313: Add the location information to the original features, using the following formula: (i) in, Embedding () indicates the embedding position function. i Indicates the position index within the feature. input Represents the original features of the input. output This represents the output feature of the positional encoding.

6. The database cardinality estimation method based on multi-head attention mechanism according to claim 3, characterized in that, In step 33, to construct a multi-head attention mechanism, the first step is to query... Q The data is passed to the cross-attention mechanism and utilizes data features. x As a key Key Sum Value , and query Q Then as a query Q To generate cross-attention output; By using layer normalization and deep residual connections, 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 features.

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

Images