Query cardinality estimation method based on group masked autoregressive model

Abstract

The present invention belongs to the field of database query technology, and specifically is a query cardinality estimation method based on a grouped masked autoregressive model. The present invention includes column data grouping preprocessing and grouped masked neural network cardinality estimation model training; the column data grouping preprocessing groups and sorts the data in a single table so that the model can better learn the joint probability distribution therein; the grouped masked neural network cardinality estimation model learns the distribution of some columns in different orders through position encoding, and the model training is divided into three stages: data training, mixed mask training, and direct query training. The model is trained by combining different data sets in mixed training to reduce the training time of the model; query information with large errors is recorded from the database system execution log and added to the data of the three-stage training to improve the training efficiency of the model. The present invention can reduce the query error when the traditional autoregressive model is used to estimate the cardinality, reduce time consumption, and make the model faster and more stable for cardinality estimation.

Description

Technical Field

[0001] The present invention belongs to the technical field of database query, and in particular relates to a query cardinality estimation method. Background Art

[0002] With the rapid development of the information age, the amount of data in databases is growing rapidly, and database relational schemas are becoming increasingly complex. To optimize queries in this context, query optimizers require cardinality estimation models that are more accurate and stable under complex models. Traditional cardinality estimation methods generally include histograms, data profiling, and sampling. However, these methods are inaccurate enough to meet the requirements of query optimization under today's complex conditions.

[0003] As mentioned above, machine learning-based cardinality estimation methods have gradually come into the spotlight. Existing machine learning-based cardinality estimation methods can be roughly divided into query-driven and data-driven methods, depending on the training object. The query-driven approach uses neural network models to learn the mapping between query conditions and corresponding cardinalities, while the data-driven approach uses models such as sum-product networks and autoregressive models to learn the joint probability distribution of the data. These methods have higher accuracy than traditional cardinality estimation methods.

[0004] Currently, data-driven approaches based on autoregressive models can adapt to more workload scenarios and achieve higher accuracy. However, autoregressive models struggle to fit the joint probability distribution of certain data columns, leading to large errors in queries involving these columns and, in turn, incorrect plan selection by the query optimizer. Alternatively, some approaches utilize query conditions, their cardinality, and data to optimize autoregressive models. This approach, driven by both data and queries, offers higher accuracy, but at the cost of significantly increased training time. This reduces the real-time performance of the model and makes it difficult to meet database query optimization requirements. Summary of the Invention

[0005] The object of the present invention is to provide a query cardinality estimation method based on a group mask autoregressive model that has high accuracy and meets the requirements of database query optimization.

[0006] The present invention proposes a query cardinality estimation method based on a group mask autoregressive model, which specifically includes the following steps: column data grouping preprocessing and training of a group mask neural network cardinality estimation model.

[0007] Step (1) column data grouping preprocessing;

[0008] Specifically, single-table data is grouped by column, in ascending order of information entropy. After grouping, the groups are sorted based on the weighted average of inter-column correlation and relative entropy (KL divergence). The sorted data is then fed into a grouped masked neural network cardinality estimation model with masks of varying order, and trained. This grouped preprocessing makes single-table data more suitable for prediction using an autoregressive model, reducing its prediction error. The neural network is then trained by applying masks to the data by group.

[0009] Further:

[0010] In the data column grouping stage, the information entropy of all data columns is calculated and sorted from small to large according to the information entropy. The top columns in the sorting are {X1, X2...X n}, after sorting, it becomes {X1′, X2′...X n ′}, this process is expressed as:

[0011] {X1′, X2′...X n ′}=Sort(H(X1),H(X2)...H(X n )), (1)

[0012] After sorting is completed, the data columns are clustered according to information entropy. The clustering method can use K-means and other clustering methods, μ j is the cluster center of the jth class in the clustering process, and the objective function of clustering can be expressed as:

[0013]

[0014] After completing the data column grouping, sort within the group. Assume that there are k columns in the group and the first columns to be sorted are {X1, X2...X k}, after sorting, it becomes {X1′, X2′...X k ′}, each column of data i calculates its relationship value R(i, j) with other columns j in the group, R(i, j) can be obtained by the correlation r(i, j) and relative entropy (KL divergence) D KL (i, j) is weighted averaged to produce:

[0015] R(i, j)=w1*r(X i , X j )-w2*D KL (X i , X j ), (3)

[0016] The correlation between columns can be calculated using the paired data minimum method, Pearson product-moment correlation coefficient, etc. Starting with the first column in the original group, the column that has not participated in the sorting and has the largest correlation value R(i, j) with the previous column that has been sorted is selected as the next column in the group sorting. The columns are selected as follows:

[0017] X′ i+1 =X j =max j∈{i}[]{j} R(i, j), ( 4 )

[0018] Among them, the first column is X1′=X1.

[0019] (2) Training of the group mask neural network cardinality estimation model;

[0020] The group masked neural network cardinality estimation model adopts an autoregressive neural network model, hereinafter referred to as the model, including using the original data and the query and query results on the original data to train and learn the autoregressive neural network model, obtain the joint probability distribution of different values ​​in different columns of the original data, and can use the masking mechanism to optimize the learning of the probability distribution of the data grouping and key queries generated in (1). The present invention can use a variety of autoregressive neural network models. The currently used one is the masked autoregressive model in the paper "MADE: Masked Autoencoder for Distribution Estimation" published by Mathieu Germain at ICML in 2015. The structure can be seen Figure 1 .

[0021] Model training consists of three phases: data training, mixed mask training, and direct query training. When using data for training, the data is first encoded. For columns with more than 64 distinct values, one-hot encoding can be used; for columns with more than 64 distinct values, word vector encoding can be used. The code for the current predicted position must also be appended to each piece of data.

[0022] The three training phases are summarized as follows:

[0023] In the data training phase, the n-length data sequence is expanded into n×n matrix data. Different mask matrices are added to the same data matrix in groups to perform training and learning in different orders. One sequence in the data corresponds to an n×n mask matrix. The mask is applied in groups. The mask matrix is ​​multiplied by the data matrix to obtain training data, which is then input into the neural network for training.

[0024] In the hybrid mask training phase, queries with errors greater than the set threshold η1 are selected from the test queries. The corresponding mask matrix is ​​generated according to the column conditions involved in the query. The mask matrix is ​​multiplied by the data matrix to obtain training data, which is then input into the neural network for training.

[0025] In the direct query training phase, we need to select queries whose errors are greater than a certain threshold η2 after the first two steps of training, add them to the dataset, and use the re-parameterization technique or split them into sub-queries to directly train the model. These queries can come from a pre-set test set or from log records in actual use.

[0026] The specific training process is as follows:

[0027] (1) For a set of stored data of length n, first group them according to the column data grouping preprocessing method to obtain k groups of data. The value of k is determined by the number of columns and the clustering situation, and can usually be [2-4]. Random arrangement.

[0028] (2) Data training phase: For the random permutations generated in the first step, the corresponding mask matrix M1 and position sequence P1 are generated according to the permutation order. The mask matrix M1 is multiplied by the n*n data matrix expanded by the m data sequences to obtain the masked data matrix, and the position sequence is attached to the left of the data matrix. If the number of sorting is large, part of the order can be selected and retained according to the query conditions of the training set or validation set. The data matrix is ​​then input into the neural network model, and the neural network outputs the conditional probability distribution of the variables to be predicted under the current sequence. The cross entropy of this distribution and the input data is calculated as the model loss loss1, and the neural network model is trained by backpropagation, and the training cycle is set to epoch0.

[0029] Taking 6 columns of data as an example, they are divided into 3 groups according to the grouping [(1, 2), (3, 4), (5, 6)], resulting in an inter-group sequence: Sq = (3, 4) - (5, 6) - (1, 2). The mask matrix M1 of this sequence is:

[0030]

[0031] The position sequence P1 generated by this sequence is:

[0032]

[0033] Expand a single data piece into an n*n data matrix D1, multiply the mask matrix by the data matrix, and concatenate the position sequence to obtain the training data D1′:

[0034]

[0035] The mask matrix generated at this time is added to the conditional probability distribution of the corresponding training. Figure 2 As shown in the figure, the solid line position represents that the mask matrix value is 1 and the model can see the value at this position; the dotted line position represents additional masking, and the mask value is 0 and the model cannot see the data value here.

[0036] When the number of groups exceeds 4, resulting in more than 6 sorts, or when the training dataset in the latter two stages grows to a point that severely impacts model training time, you can select and retain a portion of the order based on the query conditions of the training or validation set to reduce model training time at this stage. By analyzing the distribution of joint column conditions in the training or validation set, for example, if there are many queries involving both columns {4, 6}, you can retain the order Sq = (3, 4) - (5, 6) - (1, 2), allowing the model to skip some columns and learn the joint probability distribution of the two columns {4, 6}.

[0037] (3) During the hybrid mask training phase, set the error threshold η1. The threshold is usually set to 1.5, but can also be determined based on the actual accuracy requirements of the model. error The query conditions greater than η1 generate mask samples. These queries can come from the pre-set training set and the query logs in actual use. The closer the threshold is to 1, the more queries will be collected and the more accurate the model will be. Let query Q = {X1∈R1, X2∈R2...X n ∈R n}, let X i The range is R i ′, if R i ≠R i ′, then the query Q is considered to have a condition on column i. According to whether the query has a condition on column i, the following sequence S = {f1, f2...f n}, the mask matrix length L = sum(S), where:

[0038]

[0039] Generate a mask matrix and position sequence based on the sequence S. The matrix size is n*L. Taking 6 columns of data as an example, let Q = {X1∈R1, X3∈R3, X4∈R4}, and we can get S = {1, 0, 1, 1, 0, 0}. The mask matrix M2 is:

[0040]

[0041] The position sequence P2 is:

[0042]

[0043] Expand a single piece of data into a data matrix D2 of size L*n, multiply the mask matrix by the data matrix and concatenate the position sequence to obtain the training data D2′:

[0044]

[0045] The data is input into the model together with the training data of the first stage for training. The model output is calculated, and the cross entropy of the distribution of the corresponding position with the original data is used as the loss loss2. The model is trained by backpropagation together with the loss1 of the first stage.

[0046] (4) Direct query training phase: select the query Q whose error value is greater than the set threshold η2 after the first two phases of training, and use the query conditions and their base values ​​to train the model. The threshold η2 is usually set to 1.8, and can also be determined by yourself according to the actual accuracy requirements of the model. It usually needs to be greater than η1. These queries can come from a pre-set test set or from log records in actual use. In the direct query phase, you can use the re-parameterization technique to directly train all types of queries, or you can decompose the range query into multiple corresponding point queries and re-execute them. For range queries, use the re-parameterization technique to compare the model output results with the actual label calculation q error As loss loss3, the neural network model is trained with back propagation in combination with loss1 and loss2 in the previous two stages. For a query q that only contains equal conditions, the sequence that meets the query can be directly input into the model to obtain the estimated value sel(q)′, and the selectivity sel(q) of the query q is used to calculate q error Backpropagation of the grouped masked neural network cardinality estimation model as loss.

[0047] As can be seen from the above, the present invention adopts a hybrid training method for the three-stage training of the cardinality estimation model, that is, first, the model is trained in the data training stage using the preprocessed data, which belongs to the initialization training; then the query information and results of the training set query and the actual query are statistically analyzed, which are used for the hybrid mask training and direct query training stages. When the number of queries exceeding the error threshold in the corresponding stage is greater than the preset threshold, the corresponding training stage will be triggered to complete the training of the neural network model in (2).

[0048] In the hybrid training of the cardinality estimation model, it is necessary to set the error thresholds η1 and η2, and set the query quantity statistical threshold N η1 With N η2 , two-stage training rounds epoch1 and epoch2, record the query Q and the corresponding number of occurrences N whose error is greater than the threshold in the test phase and the actual use phase respectively Q1 With N Q2 , when the number of queries exceeding the error threshold in the corresponding stage NQ Greater than N η When , the trainer will trigger the corresponding hybrid mask training or direct query training, and the model will be trained for epochs. 1or2 training.

[0049] This cardinality estimation model uses hybrid training. During use and testing, queries Q with an error exceeding η2 are split into subqueries q consisting solely of point queries. These subqueries are automatically executed during idle time to obtain the selectivity sel(Q). The subquery results are logged, and the trainer adds subqueries with an error exceeding η2 (η2 = 1.8 is a default) to the training data during the direct query training phase.

[0050] The split query can be expressed as:

[0051] split(Q)=[q1,q2,...q n ],

[0052] Let Q = {X1∈R1, X3∈R3, X4∈R4},

[0053] Then we have: split(Q)=[q1={X1=x1},Q3={X1=x1,X3=x3},Q4={X1=x1,X3=x3,X4=x4}],

[0054] Among them, x i ∈R i , x i R i Randomly select values ​​within the range. This query can be split into R1*R3*R4 subqueries. Randomly select values ​​from the queries and add the subqueries that produce large errors to the dataset.

[0055] The query cardinality estimation method based on the group masked autoregressive model of the present invention has the following advantages:

[0056] The model and its training method of the present invention combine data-driven and query-driven learning, adapting to different types of training data combinations. Furthermore, the proposed method of masking, guided by queries, reduces training time and enables the model to better capture the joint probability distribution of data compared to traditional autoregressive methods, thereby improving the accuracy of cardinality estimation and helping the database generate better query plans.

[0057] The process of hybrid training of cardinality estimation model can be found in Figure 3 shown. BRIEF DESCRIPTION OF THE DRAWINGS

[0058] Figure 1 It is a diagram of the autoregressive neural network model (masked autoencoder neural network) of the present invention.

[0059] Figure 2 It is the training data after masking in the data training phase and the corresponding conditional probability.

[0060] Figure 3 Illustration of the hybrid training process for the cardinality estimation model.

[0061] Figure 4 2 is a diagram illustrating the training process of the autoregressive neural network model in an embodiment.

[0062] Figure 5 Schematic diagram of the application of the cardinality estimation model in the database in the implementation method. DETAILED DESCRIPTION

[0063] The following are specific examples of the present invention to further describe the present invention. Figure 4 .

[0064] Census13 dataset: This is a US census dataset. Each row of data contains attributes such as age, job type, education level, and marital status, totaling 5 numerical attributes and 8 categorical attributes. The Census13 dataset used in this paper contains 48,842 rows of data, and the dataset is initially stored in a CSV file. To meet the query load required for training and testing, an open source random query generator was used to generate 1,000 queries on this dataset and obtain the corresponding query results, of which 500 were used as the training set and 500 as the test set.

[0065] PostgreSQL database: This is an open source object-relational database that uses the SQL language for querying. It has a query optimization module and a corresponding cardinality estimation module. The deployment location of the present invention is in its cardinality estimation module.

[0066] First, the Census13 dataset is processed according to the column data grouping preprocessing method. The column data is divided into 3 groups and sorted within the groups, and 6 inter-group sortings are generated at the same time. After the preprocessing is completed, the data training in the hybrid three-stage training is carried out to enable the model to initially learn the joint probability distribution of the data and can be used for querying; the model with the completed data training is used to execute 500 training set queries and calculate the q of the training set. error Error, the queries with errors greater than η1 (η1 = 1.5) are added to the dataset of the mixed mask training part and trained again with the original data; the model that has completed the above two stages of training is used again to execute 500 training set queries and calculate q error Error, where the error is greater than η2 (η2 = 1.8) query is added to the direct query training data set and trained again. After the training is completed, the cardinality estimator effect can be tested on the test set. The complete training process is shown in Figure 4At this point, the model has completed the hybrid three-stage training.

[0067] Import the Census13 dataset from the CSV file as a single table into PostgreSQL, and use the trained cardinality model to connect it to the cardinality estimation module of PostgreSQL for query optimization. At this point, various business queries can be performed in the database. The above test set can be used to simulate the business query process. The query process can be seen Figure 5 Input query, cardinality estimation and output result part. When the business query is in progress, a log module is added to the cardinality estimation module to record the queries and query results related to the Censusl3 data table and return them to the model trainer. Queries with errors greater than η1 and η2 are added to the dataset; if the Census13 data table in PostgreSQL changes, it is re-exported to a CSV file to replace the original dataset. The data update process can be seen in Figure 5 When the number of occurrences of two queries with errors greater than the query quantity statistical threshold N is greater than the query quantity statistical threshold N η1 With N η2 When the model is trained again with new datasets and new queries, the accuracy of the model query cardinality estimation is improved. Figure 5 The dotted line connects the data update and retraining parts.

Claims

1. A query cardinality estimation method based on a group masked autoregressive model, characterized in that: The specific steps include: column data grouping preprocessing, group mask neural network cardinality estimation model training; Step (1) column data grouping preprocessing; Specifically, the data from a single table is grouped by column, from smallest to largest according to information entropy. After grouping, the data is sorted within the group based on the weighted average of the inter-column correlation and relative entropy, i.e., KL divergence. The sorted data is then fed into a group mask neural network cardinality estimation model with masks of different orders added to the data, and trained. The grouped preprocessed data from a single table facilitates prediction using an autoregressive model, reducing the prediction error of the autoregressive model. The neural network is then trained by adding masks to the data by group. (2) Training of the group mask neural network cardinality estimation model; The group mask neural network cardinality estimation model adopts an autoregressive neural network model (hereinafter referred to as the model), including using the original data and the query and query results on the original data to perform hybrid training and learning on the model to obtain the joint probability distribution of different values ​​of different columns in the original data, and using a masking mechanism to optimize the learning of the probability distribution of the data grouping and key query generated in step (1); Model training is divided into three stages: data training, mixed mask training, and direct query training. When using data for training, the data is first encoded. For columns with a small value range, one-hot encoding is used, and for columns with a long value range, word vector encoding is used. At the same time, the code of the current prediction position needs to be appended to each data item. Among them: In the data training phase, the n-length data sequence is expanded into n×n matrix data. Different mask matrices are added to the same data matrix in groups to perform training and learning in different orders. One sequence in the data corresponds to an n×n mask matrix. The mask is applied in groups. The mask matrix is ​​multiplied by the data matrix to obtain training data, which is then input into the neural network for training. In the hybrid mask training phase, queries with errors greater than the set threshold η1 are selected from the test queries. The corresponding mask matrix is ​​generated according to the column conditions involved in the query. The mask matrix is ​​multiplied by the data matrix to obtain training data, which is then input into the neural network for training. In the direct query training phase, we need to select queries whose errors after the first two steps of training are greater than the set threshold η2, add them to the dataset, and use the re-parameterization technique or split them into sub-queries to directly train the model. These queries come from a pre-set test set or from log records in actual use.

2. The query cardinality estimation method based on the group masked autoregressive model according to claim 1, characterized in that: The column data grouping preprocessing described in step (1) is as follows: Calculate the information entropy of all data columns and sort them from small to large. The top columns are {X1, X2...X n }, after sorting, it becomes {X1′, X2′...X n ′}, this process is expressed as: {X1′,X2′...X n ′}=Sort(H(X1),H(X2)...H(X n )), (1) After sorting is completed, the data columns are clustered according to information entropy. The clustering method uses the K-means method. Let μ j is the cluster center of the jth class in the clustering process, and the objective function of clustering is expressed as: After completing the data column grouping, sort within the group. Assume that there are k columns in the group and the first columns to be sorted are {X1, X2...X k }, after sorting, it becomes {X1′, X2′...X k ′}, each column of data i calculates its relationship value R(i, j) with other columns j in the group, R(i, j) is calculated by the correlation r(i, j) and the relative entropy D KL (i, j) is weighted averaged to produce: R(i,j)=w1*r(X i ,X j )-w2*D KL (X i ,X j ), (3) Inter-column correlations were calculated using the pairwise minimum method or the Pearson product-moment correlation coefficient; Starting with the first column in the original group, the column that has not participated in the sorting and has the largest relationship value R(i, j) with the previous column that has been sorted is selected as the next column in the group sorting. The columns selected are: X′ i+1 =X j =max j∈{i}[]{j} R(i,j), (4) Among them, the first column is X1′=X1.

3. The query cardinality estimation method based on the group masked autoregressive model according to claim 2, characterized in that: The training of the group mask neural network cardinality estimation model in step (2) is as follows: (1) For a set of stored data of length n, first group them according to the column data grouping preprocessing method to obtain k groups of data. The value of k is determined by the number of columns and the clustering situation. Generate Random arrangement of species; (2) Data training phase: for the random permutations generated in step (1), the corresponding mask matrix M1 and position sequence P1 are generated according to the permutation order; the mask matrix M1 is multiplied by the n*n data matrix expanded by the m data sequences to obtain the masked data matrix, and the position sequence is appended to the left of the data matrix; the data matrix is ​​then input into the model, and the model outputs the conditional probability distribution of the variables to be predicted under the current sequence. The cross entropy of the conditional probability distribution and the input data is calculated as the model loss loss1, and the model is trained by backpropagation, and the training cycle is set to epoch 0; (3) In the mixed mask training phase, the error threshold η1 is set and the error q after the data training phase is selected. error The query conditions greater than η1 generate mask samples. These queries come from the pre-set training set and the query logs in actual use. Let query Q = {X1∈R1, X2∈R2...X n ∈R n }, let X i The range is R i ′, if R i ≠R i ′, then the query Q is considered to have a condition on column i; based on whether the query has a condition on column i, the following sequence S = {f1,f2...f n }, the mask matrix length L = sum(S), where: Generate a mask matrix and position sequence according to the sequence S, the matrix size is n*L, Expand a single piece of data into a data matrix D2 of size L*n, multiply the mask matrix by the data matrix and concatenate the position sequence to obtain the training data D2′. This data is input into the model together with the training data of the first stage for training. The model output is calculated, and the cross entropy of the distribution of the corresponding position with the original data is used as the loss loss2. The model is trained by backpropagation together with the loss1 of the first stage. (4) In the direct query training phase, queries Q whose error values ​​are greater than the set threshold η2 after the first two phases of training are selected, and the model is trained using the query conditions and their cardinality values; these queries come from a pre-set test set or from log records in actual use; in the direct query phase, the re-parameterization technique is used to directly train all types of queries, or the range query is decomposed into multiple corresponding point queries and re-executed; for range queries, the re-parameterization technique is used to compare the model output results with the actual label calculation q error As loss loss3, the model is trained by backpropagation combined with loss1 and loss2 in the previous two stages; for queries q that only contain equal conditions, the sequence that meets the query is directly input into the model to obtain the estimated value sel(q)′, and the selectivity sel(q) of query q is used to calculate q error Backpropagation of the grouped masked neural network cardinality estimation model as loss.

4. The query cardinality estimation method based on the group masked autoregressive model according to claim 3, characterized in that: The three-stage training of the model adopts a hybrid training method, that is, firstly, the model is trained in the data training stage using the preprocessed data, which belongs to the initialization training; Then, the query information and results of the training set query and the actual query are counted and used for the hybrid mask training and direct query training phase. When the number of queries exceeding the error threshold in the corresponding phase is greater than the pre-set threshold, the corresponding training phase will be triggered to complete the training of the neural network model in (2); In model hybrid training, it is necessary to set error thresholds η1 and η2, and set the query quantity statistical threshold N η1 With N η2 , two-stage training rounds epoch1 and epoch2, record the query Q and the corresponding number of occurrences N whose error is greater than the threshold in the test phase and the actual use phase respectively Q1 With N Q2 , when the number of queries exceeding the error threshold in the corresponding stage N Q Greater than N η When , the corresponding hybrid mask training or direct query training is triggered, and the model is trained for epochs 1or2 training; During the hybrid training and testing phase of this model, queries Q with an error exceeding η2 are split into subqueries q containing only point queries and automatically executed during idle time to obtain the selectivity sel(q). The subquery results are recorded in the log, and subqueries with large errors are added to the training data of the direct query training phase. The split query expression is: split(Q)=[q1,q2,...q n ], Let Q = {X1∈R1, X3∈R3, X4∈R4}, Then we have: split(Q)=[q1={X1=x1},q3={X1=x1,X3=x3},q4={X1=x1,X3=x3,X4=x4}],where x i ∈R i , x i R i Randomly select values ​​within the range; the query is split into R1*R3*R4 sub-queries in total, and random values ​​are selected from the queries. The sub-queries that produce large errors are added to the data set.

Images