Method and device for improving adaptability of an active learning cardinality estimation model

Abstract

The application relates to the field of database query optimizers, and provides a method and device for improving adaptability of an active learning-based cardinality estimation model. The main purpose is to solve the problem that the data distribution during training and the actual data distribution may be inconsistent, the cardinality estimation model is out of date, and inaccurate cardinality estimation is caused. The main scheme comprises model training, wherein static data and query statements are used to train an original cardinality estimation model; model updating, wherein after data changes, if the data distribution changes exceed a threshold value, dynamic data is used to update the original cardinality estimation model, an active learning strategy is used to select more useful query statements to update the original cardinality estimation model, otherwise, the original cardinality estimation model is used; and the performance of the updated cardinality estimation model is tested by using dynamic data.

Description

Technical Field

[0001] This invention relates to the field of database query optimizers, and provides a method and apparatus for improving the adaptability of an active learning cardinality estimation model. Background Technology

[0002] A database query optimizer is a tool that selects the most efficient execution plan from numerous candidate plans for a given query and then submits it to the executor for execution. Cardinality estimation and cost estimation are crucial components of the query optimizer. Cardinality refers to the number of records a query returns, and cardinality estimation estimates the cardinality without actually executing the query. Recent research shows that using machine learning algorithms to train a regression model for query cardinality can replace the cardinality estimation component of traditional query optimizers, achieving more accurate cardinality estimation and thus improving query optimization efficiency. However, in scenarios where data changes, the cardinality estimation model may become outdated and produce inaccurate cardinality estimates because the data distribution during training may differ from the actual data distribution.

[0003] The technical solutions of the two existing technologies are most similar to the proposal in this application:

[0004] 1. Improve adaptability by retraining the cardinality estimation model: This approach directly discards outdated cardinality estimation machine learning models, re-executes the query training pool to obtain actual cardinality results (labels), and then uses these updated queries and labels to train a new cardinality estimation machine learning model from scratch.

[0005] 2. Improve adaptability by fine-tuning the cardinality estimation model through random sampling: This approach, based on outdated cardinality estimation machine learning, randomly samples a portion of queries from the query training pool, re-executes them to obtain the actual cardinality results (labels), and then uses these query statements to fine-tune the outdated cardinality estimation machine learning model. Summary of the Invention

[0006] The purpose of this invention is to address the problem that the data distribution during training may not be consistent with the actual data distribution, which can lead to the cardinality estimation model becoming outdated and resulting in inaccurate cardinality estimation.

[0007] To achieve the above objectives, the present invention employs the following technical means:

[0008] An adaptive improvement method for an active learning cardinality estimation model includes the following specific steps:

[0009] Step 1: Model training. Train the original cardinality estimation model using static data and query statements.

[0010] Step 2: Model update. After the data changes, if the data distribution changes beyond the threshold, the original cardinality estimation model is updated using dynamic data. An active learning strategy is used to select more useful query statements to label in order to update the original cardinality estimation model. Otherwise, the original cardinality estimation model is used.

[0011] Step 3: Model testing. Use dynamic data to test the performance of the updated cardinality estimation model.

[0012] In the above technical solution, step 1 specifically includes the following steps:

[0013] Step 1.1: Data Initialization:

[0014] Four real-world datasets (Census, Forest, Power, and DMV) were used as the actual underlying data on the database in the initial state where the query statements were applied.

[0015] Step 1.2: Query statement construction:

[0016] First, select a set of attributes (a1, a2, ..., a...). n The predicates are placed using (b1, b2, ..., bc) and then a query center (b1, b2, ..., bc) is selected for each predicate. n Then, determine the operator op("=", "≥", "≤", "[]") for each predicate. If the selected operator is a range query "[]", the query width w also needs to be determined. The final result is in the form of "select * from where a1 op b1 and b2 op b2…a nd b n op b n The query statement for "".

[0017] Step 1.3: Cardinality Tag Generation:

[0018] The query statement constructed in step 1.2 is actually executed on the data table containing the data in step 1.1 to obtain the actual cardinality result corresponding to the query statement;

[0019] Step 1.4: Query the code:

[0020] Each predicate p involved in the query statement k The parsing is a range [l] k ,u k If the predicates involved in a query statement are encoded as a numerical vector [l1, u1, l2, u2, ..., l], then the predicates involved in the query statement can be encoded as a numerical vector [l1, u1, l2, u2, ..., l]. n u nIn addition to the above, to capture the data distribution information provided by the histogram in the statistical information, three additional features (MinSel, AVI, EBO) are encoded. By reading the histogram, the cardinality estimate of each column in the data table (sel1, sel2, ..., sel) can be obtained. n MinSel represents the minimum cardinality estimate in each column of the data table, i.e., MinSel = min(sel1, sel2, ..., sel...). n ); AVI represents the product of the cardinality estimates in each column of the data table, i.e. EBO represents the product of the first four largest cardinal estimates in each column of the data table, calculated using an exponentially decaying method. Finally, the query predicate and additional features are concatenated to obtain the final query feature vector, i.e., [l1, u1, l2, u2, ..., l n ,u n [,MinSel,AVI,EBO];

[0021] Step 1.5: Model Training:

[0022] The query feature vector obtained in step 1.4 is passed through a fully connected layer, a ReLU activation layer, a fully connected layer, a ReLU activation layer, a fully connected layer, a ReLU activation layer, and a fully connected layer containing only one neuron. The resulting cardinality estimate is then compared with the true cardinality obtained in step 1.3 to calculate the mean squared error (MSE) loss. Finally, the Adam optimizer is used to update the parameters in the neural network in reverse.

[0023] In the above technical solution, step 2 specifically includes the following steps:

[0024] Step 2.1: Data Change Detection:

[0025] First, calculate the Spearman rank correlation coefficient between each column in the data table. After obtaining the correlation coefficient matrix, take the absolute value of all elements and then calculate the average value, which is the overall average correlation coefficient, used to represent the overall correlation. If the overall correlation increases or decreases beyond an acceptable threshold after the data change compared to before the data change, the cardinality estimation model update step is triggered, i.e., proceed to step 2.2. Otherwise, the original cardinality estimation model is used, and the process jumps to step 2.3.

[0026] Step 2.2: Query statement selection:

[0027] Using a pool-based active learning strategy, based on the informativeness, representativeness, and diversity of query statements, the most valuable query statements are selected from the outdated query pool, i.e., queries that are more useful for model updates, as follows:

[0028] The informativeness of the query statements is achieved by modifying the expected value of the computational model to select the most valuable samples from the unlabeled data for labeling. First, for each query statement i, its Hessian matrix is ​​calculated by taking the second-order partial derivative of the loss function with respect to the model parameters. This matrix describes the local curvature information of the objective function. Then, for each query statement i, the standard deviation of its gradient vector is calculated by taking the first-order partial derivative of the loss function with respect to the original cardinality to estimate the model parameters. This standard deviation describes the uncertainty of the model's output for that sample. Finally, the EMC value is calculated for each query statement i. The EMC value is the sum of the Hessian matrix and the standard deviation of the gradient vector for that sample, calculated as follows:

[0029] EMC=trace(Hessian(f(i)))+std(grad(f(i))),

[0030] Where trace(Hessian(f(i))) is the trace of the Hessian matrix, and std(grad(f(i))) is the standard deviation of the gradient vector. The larger EMC(i) is in the above formula, the greater the change in the model prediction result after the sample is labeled. Sort the EMC values ​​of all samples in the query pool and select several query statements with the largest EMC values.

[0031] The representativeness criterion for a query statement refers to prioritizing the selection of densely distributed samples;

[0032] The diversity criterion for query samples refers to the fact that the selected samples are distributed as widely as possible throughout the input space. Therefore, k-means clustering is performed on all samples in the query pool, where k = d + 1, and d refers to the feature dimension. Then, the sample closest to each cluster center is selected, and the above operation is iterated until a predetermined number of query statements are obtained.

[0033] Step 2.3: Merge and deduplicate query statements. Merge the query statements obtained in Step 2.2 and remove duplicate query statements.

[0034] Step 2.4: Cardinality Label Generation. The query statement obtained in Step 2.3 is actually executed on the dynamic data after the changes in Step 2.1 to obtain the true cardinality result, Card. act ;

[0035] Step 2.5: Model Fine-tuning. The query statements selected through the active learning strategy are encoded. Similar to Step 1.4, the resulting query feature vectors are processed by the cardinality estimation model trained in Step 1.5 to obtain cardinality estimation results. The MSE loss is calculated by comparing the cardinality results obtained in Step 2.4 with the actual cardinality results. Then, the Adam optimizer is used to update the neural network parameters in reverse.

[0036] In the above technical solution, step 3 specifically includes the following steps:

[0037] Step 3.1: Query Encoding: Obtain the query feature vector, same as step 1.4;

[0038] Step 3.2: Query Inference: Input the query feature vector obtained in Step 3.1 into the cardinality estimation model obtained in Step 2, and output the cardinality estimate;

[0039] Step 3.3: Q-error calculation: Calculate the Q-error using the cardinality estimate obtained in Step 3.2 and the true cardinality result obtained in Step 2.4. The calculation method is as follows:

[0040]

[0041] In the above formula, Card est Card represents the cardinality estimation result. act Representing the true cardinality result, Q-error can be used to measure the relative difference between the estimated value and the actual value, thereby evaluating the performance of the cardinality estimation result.

[0042] The present invention also provides an adaptive enhancement device for an active learning cardinality estimation model, comprising:

[0043] The model training module uses static data and query statements to train the original cardinality estimation model;

[0044] The model update module updates the original cardinality estimation model with dynamic data if the data distribution changes beyond a threshold after data changes. It also uses an active learning strategy to select more useful query statements to label in order to update the original cardinality estimation model; otherwise, it continues to use the original cardinality estimation model.

[0045] The model testing module uses dynamic data to test the performance of the updated cardinality estimation model.

[0046] The model training module in the above-mentioned device specifically includes the following steps:

[0047] Step 1.1: Data Initialization:

[0048] Four real-world datasets (Census, Forest, Power, and DMV) were used as the actual underlying data on the database in the initial state where the query statements were applied.

[0049] Step 1.2: Query statement construction:

[0050] First, select a set of attributes (a1, a2, ..., a...). n The predicates are placed using (b1, b2, ..., bc) and then a query center (b1, b2, ..., bc) is selected for each predicate.n Then, determine the operator op("=", "≥", "≤", "[]") for each predicate. If the selected operator is a range query "[]", the query width w also needs to be determined. The final result is in the form of "select * from where a1 op b1 and b2 op b2…a nd b n op b n The query statement for "".

[0051] Step 1.3: Cardinality Tag Generation:

[0052] The query statement constructed in step 1.2 is actually executed on the data table containing the data in step 1.1 to obtain the actual cardinality result corresponding to the query statement;

[0053] Step 1.4: Query the code:

[0054] Each predicate p involved in the query statement k The parsing is a range [l] k ,u k If the predicates involved in a query statement are encoded as a numerical vector [l1, u1, l2, u2, ..., l], then the predicates involved in the query statement can be encoded as a numerical vector [l1, u1, l2, u2, ..., l]. n u n In addition to the above, to capture the data distribution information provided by the histogram in the statistical information, three additional features (MinSel, AVI, EBO) are encoded. By reading the histogram, the cardinality estimate of each column in the data table (sel1, sel2, ..., sel) can be obtained. n MinSel represents the minimum cardinality estimate in each column of the data table, i.e., MinSel = min(sel1, sel2, ..., sel...). n ); AVI represents the product of the cardinality estimates in each column of the data table, i.e. EBO represents the product of the first four largest cardinal estimates in each column of the data table, calculated using an exponentially decaying method. Finally, the query predicate and additional features are concatenated to obtain the final query feature vector, i.e., [l1, u1, l2, u2, ..., l n ,u n [,MinSel,AVI,EBO];

[0055] Step 1.5: Model Training:

[0056] The query feature vector obtained in step 1.4 is passed through a fully connected layer, a ReLU activation layer, a fully connected layer, a ReLU activation layer, a fully connected layer, a ReLU activation layer, and a fully connected layer containing only one neuron. The resulting cardinality estimate is then compared with the true cardinality obtained in step 1.3 to calculate the mean squared error (MSE) loss. Finally, the Adam optimizer is used to update the parameters in the neural network in reverse.

[0057] In the above-mentioned device, the model update module specifically includes the following steps:

[0058] Step 2.1: Data Change Detection:

[0059] First, calculate the Spearman rank correlation coefficient between each column in the data table. After obtaining the correlation coefficient matrix, take the absolute value of all elements and then calculate the average value, which is the overall average correlation coefficient, used to represent the overall correlation. If the overall correlation increases or decreases beyond an acceptable threshold after the data change compared to before the data change, the cardinality estimation model update step is triggered, i.e., proceed to step 2.2. Otherwise, the original cardinality estimation model is used, and the process jumps to step 2.3.

[0060] Step 2.2: Query statement selection:

[0061] Using a pool-based active learning strategy, based on the informativeness, representativeness, and diversity of query statements, the most valuable query statements are selected from the outdated query pool, i.e., queries that are more useful for model updates, as follows:

[0062] The informativeness of the query statements is achieved by modifying the expected value of the computational model to select the most valuable samples from the unlabeled data for labeling. First, for each query statement i, its Hessian matrix is ​​calculated by taking the second-order partial derivative of the loss function with respect to the model parameters. This matrix describes the local curvature information of the objective function. Then, for each query statement i, the standard deviation of its gradient vector is calculated by taking the first-order partial derivative of the loss function with respect to the original cardinality to estimate the model parameters. This standard deviation describes the uncertainty of the model's output for that sample. Finally, the EMC value is calculated for each query statement i. The EMC value is the sum of the Hessian matrix and the standard deviation of the gradient vector for that sample, calculated as follows:

[0063] EMC=trace(Hessian(f(i)))+std(grad(f(i))),

[0064] Where trace(Hessian(f(i))) is the trace of the Hessian matrix, and std(grad(f(i))) is the standard deviation of the gradient vector. The larger EMC(i) is in the above formula, the greater the change in the model prediction result after the sample is labeled. Sort the EMC values ​​of all samples in the query pool and select several query statements with the largest EMC values.

[0065] The representativeness criterion for a query statement refers to prioritizing the selection of densely distributed samples;

[0066] The diversity criterion for query samples refers to the fact that the selected samples are distributed as widely as possible throughout the input space. Therefore, k-means clustering is performed on all samples in the query pool, where k = d + 1, and d refers to the feature dimension. Then, the sample closest to each cluster center is selected, and the above operation is iterated until a predetermined number of query statements are obtained.

[0067] Step 2.3: Merge and deduplicate query statements. Merge the query statements obtained in Step 2.2 and remove duplicate query statements.

[0068] Step 2.4: Cardinality Label Generation. The query statement obtained in Step 2.3 is actually executed on the dynamic data after the changes in Step 2.1 to obtain the true cardinality result, Card. act ;

[0069] Step 2.5: Model Fine-tuning. The query statements selected through the active learning strategy are encoded. Similar to Step 1.4, the resulting query feature vectors are processed by the cardinality estimation model trained in Step 1.5 to obtain cardinality estimation results. The MSE loss is calculated by comparing the cardinality results obtained in Step 2.4 with the actual cardinality results. Then, the Adam optimizer is used to update the neural network parameters in reverse.

[0070] The model testing module in the above-mentioned device specifically includes the following steps:

[0071] Step 3.1: Query Encoding: Obtain the query feature vector, same as step 1.4;

[0072] Step 3.2: Query Inference: Input the query feature vector obtained in Step 3.1 into the cardinality estimation model obtained in Step 2, and output the cardinality estimate;

[0073] Step 3.3: Q-error calculation: Calculate the Q-error using the cardinality estimate obtained in Step 3.2 and the true cardinality result obtained in Step 2.4. The calculation method is as follows:

[0074]

[0075] In the above formula, Card estCard represents the cardinality estimation result. act Representing the true cardinality result, Q-error can be used to measure the relative difference between the estimated value and the actual value, thereby evaluating the performance of the cardinality estimation result.

[0076] A storage medium in which a processor executes a program stored in the storage medium to implement the above-described adaptive improvement method for an active learning cardinality estimation model.

[0077] Because the present invention adopts the above-described technical solution, it has the following beneficial effects:

[0078] 1. Compared to retraining the cardinality estimation model, this technique is much more efficient because labeling query statements requires actually executing the query to obtain the true cardinality result as the label of the query statement, which is very time-consuming. Therefore, using an active learning strategy to select some query statements for labeling can significantly reduce the labeling time and improve the update efficiency of the cardinality estimation model.

[0079] 2. Compared to the random sampling fine-tuning cardinality estimation model, this technique addresses the issue that randomly sampled data may not be useful for model updates and often results in redundant query statements. These samples are not useful for model updates. Therefore, using an active learning strategy to select and label some query statements can effectively improve model accuracy and ensure the performance of the cardinality estimation model. Attached Figure Description

[0080] Figure 1 This is a flowchart. Detailed Implementation

[0081] This invention provides a method for improving the adaptability of an active learning cardinality estimation model, comprising the following specific steps:

[0082] Step 1: Model training. Train the original cardinality estimation model using static data and query statements.

[0083] Step 2: Model update. After the data changes, if the data distribution changes beyond the threshold, the original cardinality estimation model is updated using dynamic data. An active learning strategy is used to select more useful query statements to label in order to update the original cardinality estimation model. Otherwise, the original cardinality estimation model is used.

[0084] Step 3: Model testing. Use dynamic data to test the performance of the updated cardinality estimation model.

[0085] Step 1 above specifically includes the following steps:

[0086] Step 1.1: Data Initialization: Four real-world datasets (Census, Forest, Power, DMV) were used as the actual underlying data on the database for the query statements in the initial state;

[0087] Step 1.2: Query statement construction: First, select a set of attributes (a1, a2, ..., a...). n The predicates are placed using (b1, b2, ..., bc) and then a query center (b1, b2, ..., bc) is selected for each predicate. n Then, determine the operator op("=", "≥", "≤", "[]") for each predicate. If the selected operator is a range query "[]", the query width w also needs to be determined. The final result is in the form of "select * from w here a1 op b1 and b2 op b2…and b n op b n The query statement for "".

[0088] Step 1.3: Cardinality Label Generation: The query statement constructed in Step 1.2 is actually executed on the data table containing the data in Step 1.1 to obtain the true cardinality result corresponding to the query statement;

[0089] Step 1.4: Query Encoding: Encode each predicate p involved in the query statement. k The parsing is an interval [l k ,u k If the predicates involved in a query statement are encoded as a numerical vector [l1, u1, l2, u2, ..., l], then the predicates involved in the query statement can be encoded as a numerical vector [l1, u1, l2, u2, ..., l]. n u n In addition to the above, to capture the data distribution information provided by the histogram in the statistical information, three additional features (MinSel, AVI, EBO) are encoded. By reading the histogram, the cardinality estimate of each column in the data table (sel1, sel2, ..., sel...) can be obtained. n MinSel represents the minimum cardinality estimate in each column of the data table, i.e., MinSel = min(sel1, sel2, ..., sel...). n ); AVI represents the product of the cardinality estimates in each column of the data table, i.e. EBO represents the product of the first four largest cardinal estimates in each column of the data table, calculated using an exponentially decaying method. Finally, the query predicate and additional features are concatenated to obtain the final query feature vector, i.e., [l1, u1, l2, u2, ..., l n u n [MinSel,AVI,EBO];

[0090] Step 1.5: Model Training: The query feature vector obtained in Step 1.4 is passed through a fully connected layer, a ReLU activation layer, a fully connected layer, a ReLU activation layer, a fully connected layer, a ReLU activation layer, and a fully connected layer containing only one neuron. The resulting cardinality estimate is then compared with the true cardinality obtained in Step 1.3 to calculate the mean squared error (MSE) loss. Then, the Adam optimizer is used to update the parameters in the neural network in reverse.

[0091] Step 2 above specifically includes the following steps:

[0092] Step 2.1: Data Change Detection. In a real production environment, data changes due to insertion, update, and deletion operations. This means the data distribution during testing may differ from the data distribution during training, leading to a performance degradation of the trained cardinality estimation model. To describe the degree of data change, it's necessary to calculate the overall correlation before and after the change. First, calculate the Spearman rank correlation coefficient between each column. After obtaining the correlation coefficient matrix, take the absolute value of all elements and calculate the average, which is the overall average correlation coefficient, representing the overall correlation. If the overall correlation increases or decreases by more than an acceptable threshold (e.g., 10%) after the data change compared to before, the following cardinality estimation model update step is triggered; otherwise, the original cardinality estimation model is used, and the process proceeds to Step 3.

[0093] Step 2.2: Query Selection. Due to data changes, the cardinality result obtained by executing the original query on the original data is no longer applicable and needs to be re-executed to update the cardinality. However, this update process is very time-consuming and unsuitable for production deployment scenarios with frequent data changes. Therefore, a pool-based active learning strategy is used to select some of the most valuable query statements (i.e., queries that are more useful for model updates) from the outdated query pool based on the informativeness, representativeness, and diversity of the query statements.

[0094] The informativeness of the query statement is achieved by selecting the most valuable samples from the unlabeled data for labeling through Expected Model Change (EMC). First, for each sample i, its Hessian matrix is ​​calculated by calculating the second-order partial derivative of the loss function with respect to the model parameters. This matrix describes the local curvature information of the objective function. Then, for each sample i, the standard deviation of its gradient vector is calculated by calculating the first-order partial derivative of the loss function with respect to the original cardinality to estimate the model parameters. This standard deviation describes the uncertainty of the model's output for that sample. Finally, the EMC value is calculated for each sample i. The EMC value is the sum of the Hessian matrix and the standard deviation of the gradient vector for that sample, calculated as follows: EMC = trace(Hessian(f(i))) + std(grad(f(i))), where trace(Hessian(f(i))) is the trace of the Hessian matrix, and std(grad(f(i))) is the standard deviation of the gradient vector. In the above formula, the larger the EMC(i), the greater the change in the model's prediction result after the sample is labeled, and therefore the more likely the sample is to improve the model's performance. The EMC values ​​of all samples in the query pool are sorted, and several query statements with the largest EMC values ​​are selected.

[0095] The representativeness criterion for query statements refers to prioritizing densely distributed samples, while the diversity criterion for query statements refers to selecting samples that are as widely distributed as possible throughout the input space. Therefore, k-means clustering is performed on all samples in the query pool, where k = d + 1 (d refers to the feature dimension), and then the samples closest to each cluster center are selected. This process is iterated until a predetermined number of query statements are obtained.

[0096] Step 2.3: Merge and remove duplicate queries. Merge the queries obtained in Step 2.2 and remove duplicate queries.

[0097] Step 2.4: Cardinality Label Generation. The query statement obtained in Step 2.3 is executed on the dynamically modified data from Step 2.1 to obtain the true cardinality result, Card. act .

[0098] Step 2.5: Model Fine-tuning. The query statements selected through the active learning strategy are encoded. Similar to Step 1.4, the resulting query feature vectors are processed by the cardinality estimation model trained in Step 1.5 to obtain cardinality estimation results. The MSE loss is calculated by comparing the cardinality results obtained in Step 2.4 with the actual cardinality results. Then, the Adam optimizer is used to update the neural network parameters in reverse.

[0099] Step 3 specifically includes the following steps:

[0100] Step 3.1: Query Encoding. Obtain the query feature vector, as in Step 1.4.

[0101] Step 3.2: Query Inference. Input the query feature vector obtained in Step 3.1 into the cardinality estimation model obtained in Step 2, and output the cardinality estimate.

[0102] Step 3.3: Q-error calculation. The Q-error is calculated using the cardinality estimate obtained in Step 3.2 and the true cardinality result obtained in Step 2.4, as follows:

[0103]

[0104] In the above formula, Card est Card represents the cardinality estimation result. act Representing the true cardinality result, Q-error can be used to measure the relative difference between the estimated value and the actual value, thereby evaluating the performance of the cardinality estimation result.

[0105] This technical proposal has the following characteristics:

[0106] 1. In step 2.1, a data distribution change detection mechanism was designed. Once the overall correlation of the data changes beyond the threshold, an active learning strategy is initiated to select samples to update the cardinality estimation model; otherwise, the original cardinality estimation model is used.

[0107] 3. In step 2.2, when selecting query statements using the active learning strategy, the three dimensions of query statement informationality, representativeness, and diversity were considered, which reflect the contribution to model updating from different perspectives.

[0108] This application applies an active learning strategy based on the informativeness, representativeness, and diversity of data samples to the learning-based query-driven cardinality estimation task, primarily improving the adaptability of the cardinality estimation model after data updates. Specifically, this active learning strategy considers the informativeness, representativeness, and diversity of the query data samples. Informativeness is achieved by calculating the Model Change Expectation (EMC) for unlabeled samples and then selecting the query statement with the highest EMC value. Representativeness and diversity are achieved by performing k-means clustering on the unlabeled samples and selecting the sample closest to the cluster center. After selecting more useful query statements for model updates through the active learning strategy, these queries are used to update the outdated learning-based query-driven cardinality estimation model. This achieves higher cardinality estimation accuracy in a shorter time, balancing update time and model accuracy, and effectively improving the adaptability of the learning-based query-driven cardinality estimation method.

Claims

1. A method for improving the adaptability of an active learning cardinality estimation model, characterized in that, Comprise the following specific steps: Step 1: model training, using static data and query statement training original cardinality estimation model; Step 2: model update, after data changes, if the data distribution changes more than the threshold, then update the original cardinality estimation model using dynamic data, use active learning strategy to select more useful query statement annotation to update the original cardinality estimation model, otherwise use the original cardinality estimation model; Step 2 specifically comprises the following specific steps: Step 2.1: data change detection: First, calculate the Spearman rank correlation coefficient between each column in the data table, and after obtaining the correlation coefficient matrix, take the absolute value of all elements, then calculate the average, which is the average correlation coefficient of the whole, which is used to represent the overall correlation; If the overall correlation increases or decreases compared with the data before the data changes, more than the acceptable threshold, then trigger the cardinality estimation model update step, that is, enter step 2.2, otherwise use the original cardinality estimation model, skip to step 2.3; Step 2.2: query statement selection: Using pool-based active learning strategy, based on the informativeness, representativeness and diversity of query statement, select some most valuable queries from the outdated query pool, that is, more useful queries for model update, as follows: The informativeness of the query statement is achieved by calculating the second-order partial derivative of the loss function with respect to the model parameters to calculate the Hessian matrix of each query statement i, which describes the local curvature information of the objective function; Then, for each query statement i, calculate the standard deviation of the gradient vector by calculating the first-order partial derivative of the loss function with respect to the original cardinality estimation model parameters, which describes the uncertainty of the model output for this sample; Finally, calculate the EMC value of each query statement i, which is the sum of the Hessian matrix and the gradient vector standard deviation of the sample, and the calculation method is as follows: ， wherein, is the trace of the Hessian matrix, is the standard deviation of the gradient vector, in the above equation is larger, it means that the change of the model prediction result after the sample is marked is larger, the EMC values of all samples in the query pool are sorted, and a number of query sentences with the largest EMC values are selected; The representativeness standard of the query statement refers to the preferential selection of samples with dense distribution; The diversity criterion of the query sample refers to the selected samples being as dispersed as possible in the entire input space, so that the diversity criterion is satisfied for all samples in the query pool - s clusters, wherein refers to the feature dimension, and then the sample closest to each cluster center is selected, and the above operation is iterated until a predetermined number of query sentences is obtained; Step 2.3: query statement merging and deduplication, merging the query statements obtained in step 2.2 and removing the duplicate query statements therein; Step 2.4: Cardinality label generation, the query statement obtained in step 2.3 is actually executed on the dynamic data changed in step 2.1 to obtain the real cardinality result ; Step 3: model test, test the performance of the updated cardinality estimation model using dynamic data. 2.The method of claim 1, wherein, Step 1 specifically comprises the following specific steps: Step 1.1: data initialization: Four real-world data } were used as the actual underlying data on which the query statements acted on the database in the initial state; Step 1.2: query statement construction: First, a set of attributes ( ) is selected to place predicates, then for each of the predicates, a query center ( , ,…, ) is selected, and then an operator {" ”, " ”, " ”, " ”} of each predicate is determined, wherein if the selected operator is a range query ”, the width of the query also needs to be determined, and finally a query statement in the form of … ” is obtained. Step 1.3: cardinality label generation: Execute the query statement constructed in step 1.2 on the data table in step 1.1 to get the real cardinality result corresponding to the query statement; Step 1.4: query encoding: Each predicate involved in a query statement is parsed as an interval ] and encoded as a numerical vector where k ∈ n. In addition, three extra features are encoded to capture the data distribution information provided by the histogram in the statistics The cardinality estimate of each column in the data table is obtained by reading the histogram The minimum value among the cardinality estimates of each column in the data table, i.e. ; The product of the cardinality estimates of each column in the data table, i.e. ; The top four largest exponentially decaying products among the cardinality estimates of each column in the data table, i.e. The final query feature vector is obtained by concatenating the query predicates and the extra features, i.e. ;​​ Step 1.5: model training: Calculate the mean square error loss MSE loss between the cardinality estimation result obtained by the query feature vector in step 1.4 and the real cardinality obtained in step 1.3, and then use the Adam optimizer to update the parameters in the neural network in reverse. 3.The method of claim 2, wherein, The step 3 specifically comprises the following specific steps: Step 3.1: query encoding: obtain the query feature vector, as in step 1.4; Step 3.2: query reasoning: input the query feature vector obtained in step 3.1 into the cardinality estimation model obtained in step 2, and output the cardinality estimation value; Step 3.3: - Calculation: The cardinality estimate from Step 3.2 and the true cardinality result from Step 2.4 are used to calculate - , as follows: - In the above formula denotes the base estimate result, denotes the true base result, - is used to measure the relative difference between the estimated value and the actual value, and further evaluate the performance of the base estimate result.

4. An active learning device for improving adaptability of a cardinality estimation model, characterized by, Comprise: The model training module trains the original cardinality estimation model with static data and query statements; The model updating module updates the original cardinality estimation model with dynamic data if the data distribution changes by more than a threshold, or uses an active learning strategy to select more useful query statements to update the original cardinality estimation model, otherwise the original cardinality estimation model is used; The model updating module specifically comprises the following specific steps: Step 2.1: data change detection: First, calculate the Spearman rank correlation coefficient between each column in the data table, and then take the absolute value of all elements in the correlation coefficient matrix, and then calculate the average value, which is the overall average correlation coefficient, which is used to represent the overall correlation; if the overall correlation increases or decreases compared to before the data change by more than an acceptable threshold, the cardinality estimation model updating step is triggered, i.e. step 2.2, otherwise the original cardinality estimation model is used and step 2.3 is skipped; Step 2.2: query statement selection: Using a pool-based active learning strategy, based on the informativeness, representativeness and diversity of the query statement, select some of the most valuable queries from the outdated query pool, i.e. the queries that are more useful for model updating, as follows: The informativeness of the query statement is achieved by calculating the second-order partial derivative of the loss function with respect to the model parameters to calculate the Hessian matrix, which describes the local curvature information of the objective function; then for each query statement i, calculate the standard deviation of the gradient vector by calculating the first-order partial derivative of the loss function with respect to the original cardinality estimation model parameters, which describes the uncertainty of the model output for this sample; finally, calculate the EMC value for each query statement i, which is the sum of the Hessian matrix and the gradient vector standard deviation of the sample, calculated as follows: ， wherein, is the trace of the Hessian matrix, is the standard deviation of the gradient vector, in the above equation is larger, it means that the change of the model prediction result after the sample is marked is larger, the EMC values of all samples in the query pool are sorted, and a number of query sentences with the largest EMC values are selected; The representativeness criterion for query statements is to preferentially select samples with dense distribution; The diversity criterion of the query sample refers to the selected samples being as dispersed as possible in the entire input space, so that the diversity criterion is satisfied for all samples in the query pool - s clusters, wherein refers to the feature dimension, and then the sample closest to each cluster center is selected, and the above operation is iterated until a predetermined number of query sentences is obtained; Step 2.3: query statement merging and deduplication, merging the query statements obtained in step 2.2 and removing duplicate query statements; Step 2.4: Cardinality label generation, the query statement obtained in step 2.3 is actually executed on the dynamic data changed in step 2.1 to obtain the real cardinality result ; The model testing module tests the performance of the updated cardinality estimation model with dynamic data.

5. The apparatus of claim 4, wherein, The model training module specifically comprises the following specific steps: Step 1.1: data initialization: Four real-world data } sets were used as the underlying data on which the query statements acted on the database at the initial state; Step 1.2: query statement construction: First, a set of attributes ( ) is selected to place predicates, then for each predicate, a query center ( , ,…, ) is selected, and then an operator {" ”, " ”, " ”, " "} of each predicate is determined, wherein if the selected operator is a range query ”, the width of the query also needs to be determined, and finally a query statement in the form of … ” is obtained. Step 1.3: cardinality label generation: Step 1.4: query encoding: Step 1.5: model training: Each predicate involved in a query statement is parsed as an interval ] and the predicate encoding in a query statement is a numerical vector ] where k ∈ n. In addition, three extra features are encoded to capture the data distribution information provided by the histogram of each column in the data table . The cardinality estimate of each column in the data table is obtained by reading the histogram , , , , , , , . Finally, the query predicate and the extra features are concatenated to obtain the final query feature vector . ​ The query feature vector obtained in step 1.4 is input into the base estimation model obtained in step 2, and the base estimation value is output.

6. The apparatus of claim 5, wherein, The model test module specifically comprises the following specific steps: Step 3.1: query encoding: obtain a query feature vector, as in step 1.4; Step 3.2: query reasoning: input the query feature vector obtained in step 3.1 into the base estimation model obtained in step 2, and output a base estimation value; Step 3.3: - Calculation: The cardinality estimate from Step 3.2 and the true cardinality result from Step 2.4 are used to calculate - , as follows: - In the above formula denotes the base estimate result, denotes the true base result, - is used to measure the relative difference between the estimated value and the actual value, and further evaluate the performance of the base estimate result.

7. A storage medium, characterized by The processor executes the program stored in the storage medium to implement the adaptive improvement method of the active learning base estimation model according to any one of claims 1-3.

Images