Cardinality estimation optimization method based on attention mechanism and data perception
By adopting an active learning framework based on attention mechanism and data perception in database query optimization, combining the dual attention model and Bayesian neural network, the problems of insufficient information extraction and limited model adaptability in the cardinal estimation method are solved, and the problem of insufficient information extraction and limited model adaptability in the cardinal estimation method is realized. More efficient and accurate cardinality estimation improves the overall performance of database query optimization.
Patent Information
- Application Number
- CN202410991235.5
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-07-23
- Publication Date
- 2024-11-15
- Estimated Expiration
- 2044-07-23
AI Technical Summary
The existing cardinality estimation methods have problems such as insufficient information extraction, limited model adaptability, and poor computing efficiency and storage requirements in database query optimization.
Adopting an active learning framework (ADAL) based on attention mechanism and data perception, combining position and cross attention modules through a dual attention model, using Bayesian neural networks to quantify uncertainty, and data sampling and modeling through active learning strategies Incremental training, build an integrated model to improve the accuracy and computational efficiency of cardinal estimation.
It significantly improves the accuracy of cardinal estimation and the adaptability of the model, improves the overall efficiency of query optimization, reduces calculation overhead, and enhances the generalization ability and stability of the model.
Smart Images

Figure CN118964403A_ABST
Abstract
Description
Technical Field
[0001] The present invention belongs to the technical field of query optimization of database management systems, and specifically relates to a cardinality estimation optimization method based on attention mechanism and data perception. Background Art
[0002] In database query optimization, cardinality estimation is a key link, which directly affects the generation and execution efficiency of query plans. Traditional cardinality estimation methods mainly rely on statistical information and heuristic algorithms, such as histogram-based methods, which usually assume that data follows a specific statistical distribution and that attributes are independent of each other. However, real-world datasets are often highly random and complex, which do not meet these assumptions, resulting in insufficient estimation accuracy of traditional methods in some cases.
[0003] With the development of machine learning technology, cardinality estimation methods based on machine learning have emerged. These methods can be divided into three categories: data-driven, query-driven, and hybrid-driven:
[0004] 1. Data-driven approach: Treat cardinality estimation as an unsupervised learning problem and perform estimation by modeling the joint distribution of relational tables. For example, DeepDB estimates cardinality by modeling relational sum product networks (RSPN).
[0005] 2. Query-driven approach: Treat cardinality estimation as a regression problem and map the query content to cardinality values. For example, MSCN uses a multi-set convolutional network to process query feature vectors and map them to cardinality estimates.
[0006] 3. Hybrid methods: Combine data and query information for learning to improve the accuracy of estimation. For example, UAE adopts a unified deep autoregressive model to learn the joint distribution of tables in an unsupervised manner and uses query content as auxiliary information for training in a supervised manner.
[0007] Although machine learning-based cardinality estimation techniques have made progress in some aspects, they still face some challenges:
[0008] 1. Mapping between data and queries: Existing methods often lack a reasonable mapping mechanism when mapping query features to actual cardinality, resulting in insufficient information extraction and affecting the accuracy of estimation.
[0009] 2. Model adaptability: Although neural networks have achieved success in many fields, their adaptability in cardinality estimation is still limited, and a single model is difficult to capture all types of query features.
[0010] 3. Computational efficiency and storage requirements: Some machine learning-based methods may not be as good as traditional query-driven methods in terms of computational efficiency and storage requirements. Summary of the invention
[0011] The present invention aims to solve the limitations of existing cardinality estimation methods in database query optimization by proposing an active learning framework (ADAL) based on attention mechanism and data perception to optimize cardinality estimation, which significantly improves the accuracy and computational efficiency of cardinality estimation.
[0012] The technical solution adopted by the present invention is:
[0013] A cardinality estimation optimization method based on attention mechanism and data perception includes the following steps:
[0014] S1. Characterize the database data and query statements to construct training data. Specifically, characterize the database state. For each attribute A of the database relation R, divide the value range of A into multiple buckets and calculate the frequency of the value of A in each bucket to construct a histogram. Then normalize the histogram and combine it into a feature vector set X of the database state. Query characterization includes connection characterization and filter characterization. Connection characterization converts the connection condition in the query into a binary vector to represent the relationship between the two connected attributes. Filter characterization converts the filter condition in the query into a vector representing the boundary point of the search hyperrectangle. Finally, these feature vectors are combined into a feature vector q of the SQL query.
[0015] S2. Construct a cardinality estimation model based on a dual attention model, wherein the dual attention model refers to a dual attention model including a position attention module and a cross attention module, wherein the feature vector of the database state is input into the position attention module for processing, and the position attention module captures the correlation between attributes in the database state through a self-attention mechanism; the feature vector of the SQL query and the output of the position attention module are simultaneously input into the cross attention module, and the cross attention module discovers the correlation between the database data and the query through cross attention to obtain a cardinality estimation;
[0016] Using the training data to perform offline training on the constructed cardinality estimation model, a preliminary cardinality estimation model is obtained;
[0017] S3. Quantify the uncertainty of the output of the preliminary cardinality estimation model using a Bayesian neural network, and perform data sampling using an active learning strategy, including uncertainty sampling, upper confidence bound and uncertainty combined sampling, and diversity sampling that combines the two.
[0018] The formula for the uncertainty sampling is:
[0019]
[0020] Among them, n B is the number of times the Bayesian neural network predicts the same sample point q, fB (q|W B ) is the prediction of the Bayesian neural network for sample q, It is the average of multiple predictions of the network for the same sample;
[0021] The formula for combining the upper confidence bound with the uncertainty sampling is:
[0022]
[0023] Among them, q i corresponds to f B (q|W B )’s q error;
[0024] The diversity sampling is to cluster the data by using the k-means clustering algorithm, and then select samples with larger uncertainty or upper confidence limit values from each cluster;
[0025] S4. Incrementally train the cardinality estimation model, specifically, use samples obtained through uncertainty sampling, upper confidence bound combined with uncertainty sampling, and diversity sampling combined with the two to train the models separately to obtain different models, and then combine multiple models to form an integrated model;
[0026] S5. Use the obtained integrated model to optimize the database cardinality estimation.
[0027] The beneficial effect of the present invention is that the present invention realizes a novel cardinality estimation method, which not only improves the accuracy of cardinality estimation, but also significantly improves the adaptability of the model and the overall efficiency of query optimization through active learning strategies and integrated model technology. BRIEF DESCRIPTION OF THE DRAWINGS
[0028] Figure 1 is a query featurization example graph.
[0029] Figure 2 It is a schematic diagram of the data-aware dual attention model (DDAM) architecture.
[0030] Figure 3 This is a schematic diagram of the ADAL framework. DETAILED DESCRIPTION
[0031] The present invention is described in detail below in conjunction with the accompanying drawings:
[0032] The present invention includes the following key points:
[0033] 1. Data-Aware Dual Attention Model (DDAM): Innovatively combines the Position Attention Module (PAM) and the Cross Attention Module (CAM) to gain a deeper understanding of the relationship between queries and data.
[0034] 2. Learning ability strategy based on Bayesian neural network: Introduce Bayesian neural network to quantify the uncertainty of the model and optimize the data sampling process through active learning strategy.
[0035] 3. Integrated model construction: By integrating models obtained by different sampling strategies, the generalization ability and stability of the model are enhanced.
[0036] 4. Improve end-to-end execution efficiency: optimize the model structure and learning strategy, reduce the computational overhead of the query optimizer, and improve the overall efficiency of query processing.
[0037] The present invention specifically comprises the following steps:
[0038] S1. Data and query feature encoding
[0039] S1.1 Data Characterization
[0040] In the first step of the present invention, the database state is characterized. The database state X is represented by constructing a histogram of each attribute, and these histograms can describe the data distribution of each attribute. Specifically, for each attribute A of the database relation R, a histogram is constructed by dividing the value range of A into multiple buckets and calculating the frequency of the value of A in each bucket. These histograms are normalized and combined into a feature vector set X of the database state.
[0041] S1.2 Query Characterization
[0042] like Figure 1 As shown in the figure, the query featurization process is shown, which converts the various components in the SQL query into numerical vectors to prepare for the subsequent model input. Query featurization includes connection featurization and filter featurization. Connection featurization converts the connection conditions in the query into binary vectors, which represent the relationship between the two attributes of the connection. Filter featurization converts the filter conditions in the query into vectors representing the boundary points of the search hyperrectangle. These feature vectors are combined into the feature vector q of the SQL query.
[0043] S2. Offline training
[0044] S2.1 Dual Attention Model Training
[0045] In the offline training phase, the dual attention model is used to learn the features of data and queries to obtain a preliminary cardinality estimation model. Figure 2 As shown in Figure 2, the dual attention model includes a position attention module (PAM) and a cross attention module (CAM). PAM captures the correlation between attributes in the database state through the self-attention mechanism, while CAM discovers the correlation between database data and queries through cross attention.
[0046] S2.2 Feature vector input
[0047] The data feature vector X and the query feature vector q are input into the dual attention model, which outputs a preliminary cardinality estimation result by learning the relationship between these feature vectors. The goal of this step is to train a model that can understand the complex relationship between data and queries.
[0048] S3. Application of active learning strategy
[0049] A Bayesian neural network is used to quantify the uncertainty of the model output, and data sampling is performed through an active learning strategy. Figure 3 As shown in the figure, the architecture of the ADAL framework is presented, including the construction process of data characterization, query characterization, PAM, CAM, Bayesian neural network, sampling strategy and integrated model.
[0050] S3.1 Uncertainty sampling
[0051] Adopt an uncertainty-based sampling strategy to select samples with high model prediction uncertainty for further training. This strategy selects samples that are most likely to improve model performance by quantifying the uncertainty of the model output. The present invention designs the following formula for the data sampling strategy:
[0052]
[0053] Among them, f B (q|W B ) is the prediction of the Bayesian neural network for sample q, It is the average of multiple predictions made by the network for the same sample.
[0054] S3.2 Upper confidence bound combined with uncertainty sampling
[0055] Combine the upper confidence bound and uncertainty to select samples with high improvement potential. This strategy selects samples that can maximize model improvement by considering the uncertainty and bias of model prediction. The present invention designs the following formula for data sampling strategy:
[0056]
[0057] Among them, q i corresponds to f B (q|W B )’s q error.
[0058] S3.3 Diversity sampling
[0059] The data is clustered using the k-means clustering algorithm, and then samples with larger uncertainty or upper confidence limits are selected from each cluster. Diversity sampling ensures that the model can learn different aspects of the data and improve the generalization ability of the model. This strategy avoids overfitting the model to a certain type of data by selecting samples with different characteristics.
[0060] S4. Incremental training and model fusion
[0061] S4.1 Incremental Training
[0062] The model is incrementally trained based on the data sampled by the active learning strategy. In this step, different data sampling strategies are used, such as sampling based on uncertainty, sampling combining upper confidence bound and uncertainty, and diversity sampling, to select the most valuable samples for model training.
[0063] S4.2 Model fusion
[0064] Through the ensemble learning strategy, multiple models trained by different sampling strategies are combined to form an integrated model. This integrated model can improve the model's adaptability to different queries and data distributions and enhance its generalization ability.
[0065] S5. Use the obtained integrated model to optimize the database cardinality estimation.
[0066] The method of the present invention is evaluated end-to-end, including:
[0067] Accuracy Assessment
[0068] The entire query optimization process is evaluated, focusing first on the accuracy of the cardinality estimation. The prediction performance of the model is evaluated by comparing the cardinality predicted by the model with the actual cardinality.
[0069] Efficiency evaluation
[0070] Secondly, evaluate the computational efficiency of the model, including the time for model training and testing. By optimizing the model structure and learning strategy, the computational overhead of the query optimizer in actual query processing can be reduced.
[0071] Experimental verification
[0072] Through experimental verification on multiple real database workloads, the effectiveness and superiority of the framework of the present invention in practical applications are ensured. The experimental results show that the ADAL framework exhibits superior performance in different query scenarios.
[0073] In summary, the present invention has the following characteristics:
[0074] Firstly, a data-aware dual attention model (DDAM) is proposed, which integrates the position attention module (PAM) and the criss-cross attention module (CAM). This model can deeply explore the intrinsic connection between query features and data features, thereby significantly enhancing the accuracy of cardinality estimation.
[0075] Secondly, the present invention adopts a learning ability strategy based on Bayesian neural network, and quantifies the model uncertainty through the Monte Carlo dropout method, which effectively improves the adaptability and generalization ability of the model to diversified data distribution.
[0076] Finally, we designed active learning data sampling strategies, including sampling based on uncertainty, sampling combining upper confidence bounds and uncertainty, and diversity sampling. These strategies can intelligently select the most critical data samples for model training, optimize the learning process, and improve the accuracy of predictions. Through the integrated learning strategy, multiple models obtained through different data sampling strategies are integrated to build a powerful integrated model, which further improves the generalization ability and stability of the model under various query workloads.
[0077] Therefore, the present invention proposes an efficient and accurate cardinality estimation method, and provides a new technical solution for the field of database query optimization.
Claims
1. A cardinality estimation optimization method based on attention mechanism and data perception, characterized in that: The following steps are involved: S1. Characterize the database data and query statements to construct training data. Specifically, characterize the database state. For each attribute A of the database relation R, divide the value range of A into multiple buckets and calculate the frequency of the value of A in each bucket to construct a histogram. Then normalize the histogram and combine it into a feature vector set X of the database state. Query characterization includes connection characterization and filter characterization. Connection characterization converts the connection condition in the query into a binary vector to represent the relationship between the two connected attributes. Filter characterization converts the filter condition in the query into a vector representing the boundary point of the search hyperrectangle. Finally, these feature vectors are combined into a feature vector q of the SQL query. S2. Construct a cardinality estimation model based on a dual attention model, wherein the dual attention model refers to a dual attention model including a position attention module and a cross attention module, wherein the feature vector of the database state is input into the position attention module for processing, and the position attention module captures the correlation between attributes in the database state through a self-attention mechanism; the feature vector of the SQL query and the output of the position attention module are simultaneously input into the cross attention module, and the cross attention module discovers the correlation between the database data and the query through cross attention to obtain a cardinality estimation; Using the training data to perform offline training on the constructed cardinality estimation model, a preliminary cardinality estimation model is obtained; S3. Quantify the uncertainty of the output of the preliminary cardinality estimation model using a Bayesian neural network, and perform data sampling using an active learning strategy, including uncertainty sampling, upper confidence bound and uncertainty combined sampling, and diversity sampling that combines the two. The formula for the uncertainty sampling is: Among them, n B is the number of times the Bayesian neural network predicts the same sample point q, f B (q|W B ) is the prediction of the Bayesian neural network for sample q, It is the average of multiple predictions of the network for the same sample; The formula for combining the upper confidence bound with the uncertainty sampling is: Among them, q i corresponds to f B (q|W B )’s q error; The diversity sampling is to cluster the data by using the k-means clustering algorithm, and then select samples with larger uncertainty or upper confidence limit values from each cluster; S4. Incrementally train the cardinality estimation model, specifically, use samples obtained through uncertainty sampling, upper confidence bound combined with uncertainty sampling, and diversity sampling combined with the two to train the models separately to obtain different models, and then combine multiple models to form an integrated model; S5. Use the obtained integrated model to optimize the database cardinality estimation.
Citation Information
Patent Citations
Cardinality cost estimation method based on attention mechanism
CN114398395A
Deep learning-based method and apparatus for skyline query cardinality estimation
WO2024021624A1
Cited By
Database cardinal number estimation method based on multi-head attention mechanism
CN119829544A
Database cardinality estimation method based on multi-head attention mechanism
CN119829544B
