Cardinality estimation method and system for database grouping query

Abstract

The invention provides a cardinal number estimation method and system for database grouping query, and belongs to the technical field of database query. The method comprises the following steps: firstly, carrying out compressed representation on bottom data of a database based on an entropy histogram; performing data characterization representation on the histogram to obtain an attribute column; based on a self-attention network, learning correlation among elements in the attribute column, and outputting a correlation matrix; finally, performing query characterization representation on the histogram and encoding the histogram into a query feature set; and utilizing a multi-layer perceptron to learn the relevance between the query feature set and the relevance matrix so as to output a cardinality estimation value. On the basis of fully paying attention to detailed information of an underlying database, the advantages of query driving and data driving are combined to adapt to queries containing grouped query predicates, and then the overall performance and query accuracy of the database are improved.

Description

Technical Field

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

[0002] The statements in this section merely provide background technical information related to the present invention and do not necessarily constitute prior art.

[0003] In SQL statements, GroupBy (grouped query) is a common operation used to divide a relationship into non-overlapping tuple sets in one or more tables, and its quantity depends on various factors such as the number of columns involved in the GroupBy operation and selection conditions. Since the GroupBy operation appears frequently in SQL queries, it is particularly important to efficiently process and optimize queries containing GroupBy. However, estimating the cardinality of a query containing the GroupBy operation is a challenging problem, especially the presence of selection conditions makes the problem more complex.

[0004] Traditional SQL query processing containing GroupBy usually adopts a "two-stage" execution strategy, that is, the GroupBy operation is executed after all join operations are completed. This is mainly because the optimizer focuses more on selection, projection, and join (SPJ) operations and pays less attention to the optimization of GroupBy. With the application of machine learning to the field of cardinality estimation, most studies still mainly focus on SPJ queries, and the problem of cardinality estimation for queries containing GroupBy has not received sufficient attention so far. This problem is still an important but not fully studied area. For the problem of studying the cardinality estimation of filtered grouped queries, existing methods are mainly classified into two categories: the first category is traditional methods, including techniques such as histograms, sketches, and sampling; the second category is machine learning methods, which mainly combine supervised learning with sampling to capture the correlation between grouping conditions and selection conditions, thereby realizing the estimation of cardinality.

[0005] However, through the analysis of the above two types of methods, the inventor found that there are still some technical problems in the existing cardinality estimation methods, such as:

[0006] (1) Currently, many cardinality estimation tasks adopt a query-driven model, learning the relationship between cardinality and queries in a supervised learning manner without paying attention to the detailed information of the underlying database. Since these models rely on historical query data for cardinality estimation, it means that when the query workload changes or the data is updated, the accuracy of the model's cardinality estimation decreases. This is because the dynamic environment affects the data distribution, thereby changing the mapping relationship between the true cardinality and the query distribution. Therefore, in order to improve the robustness and generalization ability of the model, the query-driven model requires a large number of queries for training. However, the process of processing a large amount of labeled data consumes a large amount of computing resources, which will have a serious impact on the database performance during the actual deployment of the model.

[0007] (2) Currently, there are also many cardinality estimation tasks that adopt a data-driven model, that is, focusing on starting from the underlying data and directly extracting information from the actual data in the database to learn the joint distribution of the data. However, when facing high-dimensional data, the training and maintenance costs of the model are relatively high.

[0008] (3) In order to improve the accuracy of cardinality estimation, some studies have tried to combine the advantages of the two learning models of query-driven and data-driven. In fact, most of the existing learning methods only focus on SPJ queries and ignore the importance of queries containing the GroupBy predicate in the application scenario. The selection condition has an important impact on queries containing the GroupBy predicate because it not only affects the size of the result set but also changes the number of groups. Existing methods usually adopt a simple way of multiplying the selectivity of the local predicate by the grouped cardinality based on the independence assumption. However, when there is a strong correlation between the selected column and the grouped column, or the data distribution is uneven, this method will produce significant errors. Summary of the Invention

[0009] To overcome the deficiencies of the above-mentioned prior art, the present invention provides a cardinality estimation method and system for database grouped queries, which can, on the basis of fully paying attention to the detailed information of the underlying database, combine the advantages of query-driven and data-driven to adapt to queries containing grouped query predicates, thereby improving the overall performance and query accuracy of the database.

[0010] To achieve the above object, one or more embodiments of the present invention provide the following technical solutions:

[0011] The first aspect of the present invention provides a cardinality estimation method for database grouped queries.

[0012] A cardinality estimation method for database grouped queries includes:

[0013] Compressively represent the underlying data of the database based on an entropy-based histogram;

[0014] Performing data characterization on the histogram to obtain an attribute column; learning the correlation between the elements in the attribute column based on a self-attention network, and outputting a correlation matrix;

[0015] The histogram is represented by query features, and the query is encoded as a query feature set; a multi-layer perceptron is used to learn the association between the query feature set and the correlation matrix, and a cardinality estimation value for database grouping query is output.

[0016] Furthermore, the underlying data of the database is compressed and represented based on the entropy histogram, including:

[0017] Initialize the underlying data of the database, scan the entire table data and define the number of bucket splits; then, perform local optimization based on the determined number of splits, that is, start from a histogram of a single bucket containing all attribute values, and repeatedly select the best split point with the minimum entropy reduction until the required number of buckets is reached; then, perform a global optimization operation to output a sorted set of split points and define the boundaries of the buckets.

[0018] Furthermore, the histogram is represented by data characterization, including: converting the histogram into a set of vectors of the same dimension, combining the histogram of each attribute into an attribute column X={x 1 ,...,x T}; where x i represents the histogram of the i-th attribute, and T represents the number of all attributes in the database.

[0019] Furthermore, in the process of data characterization of the database represented by the histogram, each relationship is traversed only once, and an insertion, deletion or update operation only affects one relationship.

[0020] Furthermore, the correlation between elements in the attribute column is learned based on the self-attention network, including: passing the attribute column to multiple stacked layers with the same structure, each stacked layer consists of two sublayers; wherein the first sublayer is a multi-head attention layer, which is used to receive three inputs of key, value and query; the second sublayer is a feedforward sublayer, which is used to realize data mapping through a stacked fully connected network and an activation function.

[0021] Furthermore, the histogram is represented by query characterization, and the query is encoded as a query feature set, including: describing the query features of the SQL query as a set of features including tables, connections, predicates and groupings, that is, performing table encoding, connection condition encoding, predicate encoding and grouping attribute encoding on the query features respectively.

[0022] Further, the connection condition encoding includes: representing the connection conditions involved in the query using the adjacency matrix of the connection graph, where the connection graph consists of a set of nodes and a set of edges containing connection conditions; calculating the representation of each node by passing the feature vectors of the nodes and the adjacency matrix of the connection graph through a graph convolutional network using the Laplacian matrix; and finally, generating the final connection representation through a sum pooling layer.

[0023] The second aspect of the present invention provides a cardinality estimation system for database grouped queries.

[0024] A cardinality estimation system for database grouped queries includes:

[0025] A data compression module configured to: compressively represent the underlying data of the database based on an entropy-based histogram;

[0026] A data analysis module configured to: perform data characterization on the histogram to obtain attribute columns; learn the correlation between each element in the attribute columns based on a self-attention network and output a correlation matrix;

[0027] A query module configured to: perform query characterization on the histogram, encode the query into a query feature set; learn the association between the query feature set and the correlation matrix using a multi-layer perceptron and output a cardinality estimation value for database grouped queries.

[0028] The third aspect of the present invention provides a computer-readable storage medium having a program stored thereon, and when the program is executed by a processor, it implements the steps in a cardinality estimation method for database grouped queries as described in the first aspect of the present invention.

[0029] The fourth aspect of the present invention provides an electronic device including a memory, a processor, and a program stored on the memory and executable on the processor, and when the processor executes the program, it implements the steps in a cardinality estimation method for database grouped queries as described in the first aspect of the present invention.

[0030] The above one or more technical solutions have the following beneficial effects:

[0031] (1) The present invention directly obtains the underlying data of the database, performs data feature representation and query feature representation on the information of these underlying databases to obtain a correlation matrix and a query feature set; and then estimates the cardinality for database grouped queries based on the correlation matrix and the query feature set. During the entire estimation process, the present invention fully focuses on the detailed information of the underlying database, rather than relying on historical query data for cardinality estimation. Therefore, even when the query workload changes or the data is updated, the accuracy of the cardinality estimation of the model in the present invention will not decrease. Thus, the model of the present invention has better robustness and generalization ability, and the database performance is better.

[0032] (2) Before performing data feature representation, the present invention compresses and represents the underlying data of the database based on an entropy-based histogram, which greatly reduces the complexity of data processing to a certain extent, rather than directly extracting information; moreover, the present invention also performs query feature representation on the histogram, that is, the present invention not only starts from the underlying data, but also pays attention to the advantages of data processing complexity and query-driven. Therefore, when facing high-dimensional data, the training and maintenance costs of the model will be lower.

[0033] (3) When performing query feature representation on the histogram, the present invention performs table encoding, join condition encoding, predicate encoding, and grouping attribute encoding on the query features respectively; when performing predicate encoding, the present invention divides the numerical domain of each attribute, assigns an entry in the feature vector to each division, and at the same time assigns a value to each entry to indicate whether the division corresponding to the entry satisfies the predicate in the query. Different from the traditional uniform division, the present invention adopts an entropy-based histogram division method to determine the division points according to the actual data distribution of the attributes. Therefore, the present invention also takes into account the importance of queries containing GroupBy predicates in the application scenario. Even when there is a strong correlation between the selected column and the grouping column, or the data distribution is uneven, there will be no significant error in the present invention.

[0034] Advantages of additional aspects of the present invention will be partly given in the following description, partly will become apparent from the following description, or will be learned through the practice of the present invention. Brief Description of the Drawings

[0035] The specification drawings constituting a part of the present invention are used to provide a further understanding of the present invention. The schematic embodiments of the present invention and their descriptions are used to explain the present invention and do not constitute an improper limitation to the present invention.

[0036] Figure 1 It is a flowchart of a method for estimating the cardinality for database grouped queries in Embodiment 1 of the present invention.

[0037] Figure 2Schematic diagram of pseudo-code for compressing and representing the underlying data of the database in Embodiment 1 of the present invention.

[0038] Figure 3 Schematic diagram of data characterization in Embodiment 1 of the present invention.

[0039] Figure 4 Schematic diagram of query characterization in Embodiment 1 of the present invention.

[0040] Figure 5 Model framework diagram of a cardinality estimation system for database grouped queries in Embodiment 2 of the present invention.

[0041] Figure 6 Detail schematic diagram of the model framework of a cardinality estimation system for database grouped queries in Embodiment 2 of the present invention. Detailed implementation mode

[0042] It should be noted that the following detailed description is exemplary and is intended to provide further illustration of the present invention. Unless otherwise specified, all technical and scientific terms used herein have the same meaning as commonly understood by those of ordinary skill in the technical field to which the present invention belongs.

[0043] It should be noted that the terms used herein are only for describing the specific implementation mode and are not intended to limit the exemplary implementation mode according to the present invention.

[0044] In the case of no conflict, the embodiments in the present invention and the features in the embodiments can be combined with each other.

[0045] Overall idea proposed by the present invention: The present invention proposes a cardinality estimation method for database grouped queries, using the self-attention mechanism and the multi-layer perceptron. First, compress the underlying database using the entropy-based histogram construction method, characterize the data, and use the self-attention mechanism to learn the correlation between data columns; then, characterize the query and use the multi-layer perceptron to learn the relationship between the query and the output matrix of the data module; finally, output the estimated cardinality.

[0046] Embodiment 1

[0047] This embodiment discloses a cardinality estimation method for database grouped queries.

[0048] As Figure 1 shown, a cardinality estimation method for database grouped queries includes:

[0049] Step S1: Compress and represent the underlying data of the database based on the entropy-based histogram;

[0050] Step S2: Characterize the data of the histogram to obtain an attribute column; learn the correlation between each element in the attribute column based on a self-attention network, and output a correlation matrix;

[0051] Step S3: Characterize the query of the histogram, and encode the query into a query feature set; use a multi-layer perceptron to learn the association between the query feature set and the correlation matrix, and output a cardinality estimate value for database grouped query.

[0052] Based on the above steps, the present invention can, on the basis of fully paying attention to the detailed information of the underlying database, combine the advantages of query-driven and data-driven to adapt to queries containing grouped query predicates, thereby improving the overall performance and query accuracy of the database. For the convenience of understanding the technical solution of the present invention, the following further explains and illustrates the specific implementation steps in the technical solution of the present invention.

[0053] Step S1: Compress and represent the underlying data of the database based on an entropy-based histogram.

[0054] Initialize the underlying data of the database, scan the full-table data and define the number of bucket splits; then, based on the determined number of splits, perform local optimization, that is, start from a histogram of a single bucket containing all attribute values, and repeatedly select the best split point with the smallest entropy reduction until the required number of buckets is reached; subsequently, perform a global optimization operation to output a sorted set of split points and define the boundaries of the buckets.

[0055] Specifically, compressing and representing the underlying data of the database based on the data compression module is to use the histogram method as the data summary for the underlying data information; among them, the histogram construction method adopted in this embodiment is an entropy-based construction method to ensure that each bucket has the maximum amount of information. In particular, the frequency values in the same bucket are as similar as possible, which is essentially to maximize the entropy value (ME) of the frequency column in the bucket, because ME aims to achieve a standard uniform distribution. The pseudocode for compressing and representing the underlying data of the database is specifically as Figure 2 shown, and can be specifically implemented through the following steps:

[0056] Step S1-1: Initialization

[0057] First, receive the input data; among them, the input data includes a list of regions (area list) of frequency distribution and the required number of splits (k). Then, according to the input data, calculate the initial split points and record them in the split point list (splits); at the same time, initialize a minimum heap (minHeap) to store the minimum entropy reduction value of each bucket and its corresponding split information, providing a global reference for subsequent optimization steps.

[0058] Step S1-2, Local Optimization

[0059] In the local optimization phase, the optimal split point is calculated for each current bucket. By initializing the frequencies and entropies of the left and right partitions (LHS and RHS), and gradually moving the values in the bucket from the RHS to the LHS, the frequencies and entropies on both sides can be incrementally updated, thus efficiently calculating the entropy reduction value. Each time an update is made, if the current entropy reduction value is lower than the previous minimum value, the new optimal split point is automatically recorded. Finally, the local optimal split point and the minimum entropy reduction value for each bucket are inserted into the minimum heap, providing a basis for the global optimization phase.

[0060] Furthermore, the following formula is used to design an incremental version of the entropy-based histogram:

[0061] H(X) = p 1 H(X 1 ) + p 2 H(X 2 ) + H(p 1 , p 2 );

[0062] Where X represents the attribute column of the database table, X 1 and X 2 are two non-overlapping partitions of X, and p 1 and p 2 are the frequencies associated with X 1 and X 2 . If a new row R is inserted into the attribute column X as a new interpolation of this attribute column, its entropy will increase by:

[0063]

[0064] Where t represents the number of distinct values of the attribute column X before the update, c is the number of distinct values of the interpolation R, H() represents the entropy value, and X′ represents the updated attribute column; the probabilities of the attribute column X and the interpolation R are and respectively. Similarly, if a row R is deleted from the attribute column x, its entropy will decrease by:

[0065]

[0066] Step S1-3, Global Optimization

[0067] Global optimization extracts the current globally optimal split point from the minimum heap, divides the corresponding bucket into two new sub-buckets, and updates the bucket list. Subsequently, the local optimal split point is recalculated for the new buckets, and the information in the minimum heap is updated. This process is iterated until the specified k splits are completed. Finally, the split point list (splits) is output, recording the positions of all split points for subsequent analysis and use.

[0068] Step S2: Perform data characterization on the histogram to obtain an attribute column; learn the correlation between each element in the attribute column based on the self-attention network and output a correlation matrix.

[0069] The data analysis module uses the attention mechanism to build a bridge between the marginal distribution of a single attribute and the joint distribution of multiple attributes, so as to better process the joint distribution information between multiple attributes. Specifically, it can be achieved through the following steps:

[0070] Step S2-1: Perform data characterization on the histogram to obtain an attribute column.

[0071] Use the concise summary histogram of the database (referred to as DB-H) as part of the input features for training the model. When the database D is updated, DB-H can be updated correspondingly and effectively so that they can be input into the training model to generate a cardinality estimate. In this embodiment, the default mode of the database D is static.

[0072] Furthermore, after performing data characterization on the attribute column x, also known as DB-H, it is a compressed representation of the entire database, which can roughly describe the data distribution of each attribute and the relationship between them. In order to approximate the underlying data distribution more closely, retain more data information, and at the same time adapt to the non-uniformity of the data distribution, this embodiment uses an entropy-based histogram instead of the traditional equal-width histogram. Characterize the data as a set of vectors of the same dimension, and use the histogram of each attribute as DB-H, that is, X = {x 1 ,..., x T}; where x i is the histogram of the i-th attribute, is the number of all attributes in the database, N represents the number of tables in the database, and n i represents the number of attributes of the i-th table. This characterization method is simple but powerful and can effectively access and update the histogram. As Figure 3 shown, for a given attribute A i , if A i is a categorical attribute, first convert the value to an integer within the range [1, m], where m represents the number of categories of the categorical attribute. Given a database D, this embodiment determines the set of cut points splits = {s 1 , s 2 ,..., s k} through the entropy-based histogram construction algorithm 1, and creates a histogram x i containing d x = k + 1 buckets for each attribute A i , and the value β i in each bucket j jis determined by counting the number of attribute values falling within the interval [s j , s j+1 ). The database can be represented as a set containing T elements, where T is the total number of attributes in the database, and each element is a d x -dimensional histogram vector. By scaling, each value β j of the histogram is normalized to the range [0, 1] to ensure that the calculation of feature values is carried out on the same scale. The number of bins d x (i.e., the dimension corresponding to the histogram vector) can be flexibly adjusted according to the complexity of the data distribution. When d x is large, although it will consume more memory and computing time, it can better capture the potential correlations between attributes.

[0073] On this basis, the process of characterizing the histogram in a static database only needs to traverse each relationship once. For insert, delete, or update operations that only affect one relationship, the time complexity of modifying the relevant histogram is O(v), where v represents the number of records involved.

[0074] Step S2-2: Learn the correlations between elements in the attribute column based on the self-attention network and output the correlation matrix.

[0075] Receive the attribute column X after database characterization through the data module and pass the attribute column to n enc identical stacked layers, each stacked layer consisting of two sub-layers; among them, the first sub-layer is the multi-head attention layer N sa , which receives the same inputs: keys, values, queries, with a dimension of T×d x . These three matrices (query, key, value matrices) obtain information from the data characterization vector respectively and output a new matrix set The second sub-layer is the feed-forward sub-layer FF, located above the multi-head attention layer N sa , and is used to map the set to the data representation set Z i ′ through a stacked fully-connected network and an activation function (ReLU).

[0076] To alleviate the degradation problem and simplify training, residual connections and layer normalization are adopted. Therefore, the output in each sub-layer is LayerNorm(V + sub_layer(V)); where LayerNorm() represents layer normalization; sub_layer() represents a sub-layer in the stacked layer, and this sub-layer can be the multi-head attention layer N sa , or it can be the feed-forward sub-layer FF. The output matrix Z′ can be expressed as:

[0077]

[0078]

[0079] Among them, represents the output of each layer of the network, and Z 0 ' represents the initialization of the first layer of the network, represents the output after layer normalization of the output of the previous layer of the network, and Z i ' represents the output after passing through the feed-forward sub-layer, W represents the weight matrix, and b represents the bias term.

[0080] Finally, the output matrix Z' will be converted into a representation matrix through linear projection in order to align the dimension of the representation matrix Z with the query feature q. The keys, values, and queries in the attention layer are the same set, and they are either DB-H or the output of the previous layer. This setting enables each output element to focus on all the outputs of the previous layer, thereby focusing on all DB-Hs. More importantly, the self-attention layer calculates the correlation between any two histograms. Each histogram element describes the local distribution of an attribute, and the data module can more effectively discover the implicit relationship between any pair of attributes, thereby showing the joint distribution characteristics of attributes in the output set. Through the self-attention layer, the present invention creates a connection between all attributes in the database.

[0081] Step S3: Perform query feature representation on the histogram, and encode the query into a set of query features; use a multi-layer perceptron to learn the association between the query feature set and the correlation matrix, and output a cardinality estimate value for database grouped query.

[0082] Step S3-1: Perform query feature representation on the histogram, and encode the query into a set of query features.

[0083] As Figure 4 shown, the query feature q of the SQL query is described as a set of features including table, join, predicate, and grouping, that is, (T q , J q , P q , G q ); among them, represents the table involved in the query, represents the join condition involved in the query, represents the filtering predicate condition involved in the query, represents the grouping attribute involved in the query. This method can intuitively express the structural information of the query through multiple feature sets. Further, perform table encoding, join condition encoding, predicate encoding, and grouping attribute encoding on the query feature q respectively, and it can be specifically implemented through the following steps:

[0084] Step S3-1-1, Table Encoding. Each table t′∈T is represented by a unique one-hot encoding vector, which is used to distinguish different tables while preserving the specific association between the table and the query.

[0085] Step S3-1-2, Join Condition Encoding

[0086] The join conditions involved in the query are represented using the adjacency matrix of the join graph; where the join graph consists of a set of nodes and a set of edges containing join conditions, and its topological information is defined by an n×n symmetric matrix, where n is the number of relations in the database. This embodiment only considers equi-joins and assumes that there is at most one foreign key between each pair of relations. The feature vector of the nodes and the adjacency matrix of the join graph are used by a Graph Convolutional Network (GCN) with the Laplacian matrix to calculate the representation of each node; finally, after processing by the sum pooling layer, the final join representation is generated. This encoding process can be formalized as the following formula:

[0087]

[0088] where, H (l) represents the input feature of the l-th layer, W (l) represents the weights to be trained for the l-th layer network; H (0) represents the initial feature vector of the current join graph. Additionally, is the calculation result of, where represents the degree matrix, is the result after adding the identity matrix to the adjacency matrix. F join represents the join representation after processing by the activation function σ and the sum pooling layer, and Sum() represents summation.

[0089] Step S3-1-3, Predicate Encoding

[0090] In the dataset, the number of attributes (m) and the data domain of each attribute are fixed. Therefore, even though the same attribute may appear in multiple predicates, no query will reference more than m different attributes. Based on this fact, this embodiment divides the numerical domain of each attribute, assigns an entry in the feature vector to each division, and assigns a value to each entry indicating whether the division corresponding to the entry satisfies the predicate in the query feature q. Different from traditional uniform partitioning, this embodiment uses an entropy-based histogram partitioning method to determine the partitioning points according to the actual data distribution of the attributes. Algorithm 1 divides the data of each attribute A i into a histogram x x containing d i =k + 1 buckets, and stores the set of split points splits = {s 1 , s2 ,..., s k}。The eigenvector entries correspond to the attribute values v ∈ A i , whose index values are determined according to the positions of the cut points. Each eigenvector entry represents whether the attribute value within the corresponding partition interval [s j , s j+1 ) satisfies the predicate condition in the query feature q. Specifically, in this embodiment, 0 is used to indicate that no value conforms, a continuous value between 0 and 1 indicates that some values conform, and it is calculated according to the ratio of the value range that meets the condition to the total range of the bucket:

[0091]

[0092] where v represents the predicate constant and 1 indicates that all values conform. The characterization results of each attribute are concatenated together to form the final eigenvector.

[0093] Example: Suppose there is a table containing attributes A, B, and C. The histogram construction method based on entropy divides the histogram into 12 buckets; among them, the set of cut points for attribute A is splits_A = {-10, -8, -5, -2, 0, 10, 20, 30, 35, 40, 45}, the set of cut points for attribute B is splits_B = {10, 20, 30, 40, 50, 60, 70, 80, 100, 110, 120}, and the set of cut points for attribute C is splits_C = {-5, 0, 5, 10, 15, 20, 25, 30, 35, 40, 45}. For a query with the current predicate A < 7 AND B >= 35 AND B <= 85, its eigenvector representation is as Figure 4 shown.

[0094] For A < 7, through the stored set of cut points, it can be seen that 7 is mapped to the sixth entry (i.e., the sixth bucket) in the A vector, and the value of this entry is calculated according to the ratio All the entries on the left are set to 1, indicating that the values less than 7 conform to the condition. Correspondingly, all the entries on the right within the A vector range are set to 0. Since there is no predicate for attribute C, the characterization of attribute C is a vector all of 1s.

[0095] Step S3-1-4, Group Attribute Encoding

[0096] Similar to the table encoding, each grouped attribute is represented by a unique one-hot encoded vector, and all these one-hot encoded vectors are collected into a separate grouped set to reflect the grouping information involved in the query.

[0097] Step S3-2, Use a multi-layer perceptron to learn the association between the query feature set and the correlation matrix, and output a cardinality estimate value for database grouped queries.

[0098] Estimate the cardinality based on the query representation (T q , J q , P q , G q ), which can be specifically implemented through the following steps:

[0099] For each element t in the table set T q , calculate the new output ω of this set after neural network learning through MLP T , that is:

[0100]

[0101] For each element j in the connection set J q , calculate the new output ω of this set after neural network learning through MLP J , that is:

[0102]

[0103] For each element p in the predicate set P q , calculate the new output ω of this set after neural network learning through MLP P , that is:

[0104]

[0105] For each element g in the grouping set G q , calculate the new output ω of this set after neural network learning through MLP G , that is:

[0106]

[0107] For each element z in the data module output matrix Z, calculate the new output ω of this matrix element after neural network learning through MLP Z , that is:

[0108]

[0109] The representations of all sets are concatenated and passed to the final output MLP to obtain the final cardinality estimate value ω out , that is:

[0110] ω out = MLP out ([ω T , ω J , ω P , ω G , ω Z );

[0111] This architecture does not require converting each element in the set into an ordered sequence. For each set S, the model learns a specific neural network MLP for each element in the set separately S (v s ), and this network acts on the feature vector v of each element s ∈ S in the set s . Subsequently, by averaging these transformed representations, the final representation ω of the set is obtained s , that is: Finally, the independent representations of each set are concatenated and passed to the final output MLP: where N is the total number of sets. All MLP modules use the ReLU activation function ReLU(x) = max(0, x), and the last layer of the output MLP module uses the sigmoid activation function to ensure that the final output value ω out is within the range of [0, 1]. Through this design, the QueryM can effectively capture the complex relationship between the SQL query and the database compressed data, thus achieving more accurate cardinality estimation

[0112] Experimental verification:

[0113] To further verify the superiority of a cardinality estimation method for database grouped queries provided by the present invention, the following experimental verification operations are performed in this embodiment. Specifically:

[0114] First, regarding the dataset and query workload. This embodiment focuses on three real-world datasets, namely: IMDB, STATS, and Poker Hand, and generates corresponding query workloads for each dataset. Among them, the IMDB dataset is from the world's largest movie database, showing the characteristics of data skew and high inter-column correlation; the STATS dataset contains 8 relationships, involving complex many-to-many joins; the Poker Hand dataset contains a large amount of game data, with high discreteness and sparsity. At the same time, this embodiment designs different query workloads for each dataset to evaluate the performance of the model in various data scenarios. Specifically:

[0115] ①Regarding the IMDB dataset. In this embodiment, experiments are first conducted using the real IMDB dataset from the world's largest movie database and rating website. Due to its inherent data skew and inter-column correlation, the IMDB dataset poses challenges to cardinality estimators. The dataset contains 26 tables, and in this embodiment, it mainly focuses on six key relationships, namely: title, movie_info, movie_companies, movie_keyword, movie_info_idx, and cast_info. Additionally, the evaluation in this embodiment mainly focuses on the numerical columns in these relationships. For the query workload on the IMDB dataset, this embodiment generates a workload across multiple columns, and the query is constructed as follows: all joins are primary key-foreign key joins, and one or more columns are selected from the optional filter columns as filter predicates. For example, one filter column (such as episode_nr: episode number or production_year: production year) or two filter columns (such as episode_nr and production_year) may be selected. Each filter predicate can be an equality (=), less than (<), or greater than (>) predicate, and the value comes from the corresponding column in the database, such as production_year = 2010. Next, a GROUP BY clause is generated, and multiple grouping columns are randomly selected from all available columns. A total of 500 queries are generated in this search space. To further check whether the relationship between the filter predicate and the grouping column can be effectively captured, this embodiment differentiates between the filter predicate column and the grouping column. Subsequently, one or more columns are continued to be selected from the optional filter columns as filter predicates. For the GROUP BY clause, new columns are selected as grouping columns, and multiple columns are randomly selected to form the grouping clause. In this new search space, this embodiment also generates 500 queries. Therefore, this embodiment uses 1000 queries on the IMDB dataset to evaluate the model of the present invention.

[0116] ②Regarding the STATS dataset. The STATS dataset contains 8 relationships, namely: users, posts, postLinks, postHistory, comments, votes, badges, and tags, with a total of 43 attributes. The queries on this dataset involve more complex many-to-many joins. Similar to the IMDB dataset, this embodiment selects one or more filter predicates from the optional filter columns, focusing on numerical columns. For the GROUP BY clause, multiple attributes are randomly selected to form the grouping clause. To better capture the correlation between the filter predicate and the grouping column, this embodiment ensures that the attributes selected for the GROUP BY clause are completely different from the attributes used for the filter predicate. In total, this embodiment uses 1000 queries from this search space to evaluate the STATS dataset.

[0117] ③Regarding the Poker Hand dataset. Next, this embodiment selects the real-world dataset Poker Hand from the UCI Machine Learning Repository. Each record in this dataset represents a hand dealt from a standard deck of 52 playing cards. Each card is described using two attributes: suit and rank. There are a total of 10 predictive attributes, where the class attribute describes the type of the hand. The class attribute is highly correlated with the characteristics of the hand, which poses a challenge to the cardinality estimator. For the query workload of the Poker Hand dataset, a method similar to that of the IMDB dataset is adopted: multiple columns are selected from the optional filter columns to generate filter predicates, and each filter predicate uses operators such as equal (=), less than (<), or greater than (>). In addition, multiple columns are randomly selected from the optional grouping columns to form the GROUP BY clause. This embodiment constructs 500 query workloads within the search space of the Poker Hand dataset.

[0118] Subsequently, regarding the comparison objects. This embodiment mainly uses the following representative methods for comparison. Specifically: ①PG (the cardinality estimator of PostgreSQL) is the simplest statistics-based cardinality estimation method in PostgreSQL. It estimates the cardinality of a query by leveraging the basic statistical characteristics of the data, such as histograms. This method is simple to implement and has low computational cost, and is suitable for quickly estimating basic queries. ②CVOPT uses random sampling techniques to handle the cardinality of grouped queries on large datasets. This method provides cardinality estimation by randomly sampling the dataset while ensuring relatively low computational overhead. The design focus of CVOPT is to improve efficiency when processing large-scale datasets, thereby optimizing the query response time. ③SCBC combines the techniques of the HyperLogLog algorithm and focuses on maintaining the distinct value counts of each grouping column. It calculates the cardinality bounds for each column and maintains the upper and lower limits to calculate the query cardinality. ④Deep Sketches integrates the supervised learning model MSCN with sampling techniques. Through this method, Deep Sketches effectively captures data biases and correlations. This method improves the accuracy of cardinality estimation by training historical queries, and thus shows better performance in complex queries. ⑤RMSE proposes a method for estimating the number of distinct values (NDV) of projection attributes that appear in different queries, mainly using the concept of weighted non-repetitive sampling in the field of mathematics. In this embodiment, it is extended to SPJ queries with multiple projection attributes. ⑥ALECE uses a hybrid model to handle SPJ queries. The model includes two layers of attention mechanisms. Since it cannot directly estimate GROUP BY queries, it is extended to an estimator that can handle GROUP BY queries through data and query characterization.

[0119] Next, regarding the evaluation metrics. The E2E time refers to the total execution time of all queries in a workload. This evaluation metric is very important because it directly relates to whether the cardinality estimation method can improve the performance of a database management system (DBMS). Q-error measures the difference between the estimated cardinality C′ and the true cardinality T’, and is defined as follows:

[0120]

[0121] where Q-error() represents the Q-error measurement function.

[0122] Furthermore, the parameter settings include hyperparameters such as the number of hidden units, the number of training epochs, the batch size, and the learning rate. The number of hidden units and the number of training epochs determine the model's ability to learn additional features. During training, the batch size and the learning rate affect the model's convergence speed. By adjusting these hyperparameters, an optimal combination of prediction performance can be obtained. In the model of the present invention, the learning rate is set to 0.01 and the batch size is set to 128. In addition, to more effectively utilize the underlying database information, a histogram with 40 intervals is adopted for each attribute.

[0123] Finally, regarding the experimental results.

[0124] Table 1 shows the Q-errors of different methods. Overall, FGCE is significantly more accurate than other methods on all three datasets. Although the median Q-error of Deep Sketches is lower than that of PG, Congress, and SCBC, it still exhibits relatively large estimation errors. These errors mainly stem from the selection conditions in the queries, and the presence of joins further exacerbates the inaccuracy. As previously mentioned, the accuracy of FGCE highlights the limitations of PG, which relies on statistical information and independent assumptions to estimate the cardinality of filter and grouping queries. For SCBC, the presence of selection conditions causes the model to rely on potentially inaccurate sampling-based estimators when estimating query cardinality. Although Deep Sketches has improved in estimation accuracy compared to the above three methods, it treats selection and grouping as independent modules during training and fails to capture potential correlations, resulting in corresponding cardinality estimation errors. FGCE achieves the lowest Q-error at most percentiles. For example, at the 95th percentile of the Poker Hand dataset, the Q-error of FGCE is less than 10, showing a significant advantage over other methods in the tail of the distribution. In contrast, the errors of Congress, SCBC, Deep Sketches, and RMSE at the 95th percentile are significantly higher than that of FGCE. At other percentiles, these methods also cannot match the accuracy of FGCE. Although the accuracy of ALECE is close to that of FGCE, it fails to effectively capture the correlation between grouping and selection in the query-driven module. In contrast, FGCE better reveals the hidden relationships between attributes, between attributes and queries, and between grouping and selection.

[0125] Q-error on each dataset in Table 1

[0126]

[0127]

[0128] Embodiment 2

[0129] This embodiment discloses a cardinality estimation system for database grouping queries.

[0130] As Figure 5 shown, a cardinality estimation system for database grouping queries includes:

[0131] A data compression module, a data analysis module, and a query module; wherein, the data compression module and the data analysis module together constitute the data module.

[0132] The data compression module is configured to: compressively represent the underlying data of the database based on an entropy-based histogram;

[0133] The data analysis module is configured to: represent the data characteristics of the histogram to obtain an attribute column; learn the correlation between each element in the attribute column based on the self-attention network, and output a correlation matrix. Specifically, the data module receives the attribute column X after database characterization and passes the attribute column to n enc identical stacked layers, each stacked layer consisting of two sub-layers; where the first sub-layer is a multi-head attention layer N sa (i.e., Figure 6 the Multi-Head Attention shown in Figure 6 ), which receives the same inputs: keys, values, queries. These three matrices (query, key, and value matrices) obtain information from the data characterization vector respectively and output a new set of matrices. The second sub-layer is a feed-forward sub-layer FF (i.e., sa the Feed Forward shown in ), located above the multi-head attention layer N i .

[0134] The query module is configured to: represent the query characteristics of the histogram, encode the query into a query feature set; learn the association between the query feature set and the correlation matrix using a multi-layer perceptron, and output a cardinality estimate for database grouped queries. Specifically, as Figure 6 shown, NN represents a neural network, pooling represents a pooling layer, and they are connected by a fully connected layer.

[0135] Example 3

[0136] The purpose of this embodiment is to provide a computer-readable storage medium.

[0137] A computer-readable storage medium, on which a computer program is stored, and when the program is executed by a processor, it implements the steps in a method for estimating the cardinality of database grouped queries as described in Embodiment 1 of the present disclosure.

[0138] Example 4

[0139] The purpose of this embodiment is to provide an electronic device.

[0140] An electronic device, including a memory, a processor, and a program stored on the memory and executable on the processor. When the processor executes the program, it implements the steps in a method for estimating the cardinality of database grouped queries as described in Embodiment 1 of the present disclosure.

[0141] In the apparatuses of the foregoing Second, Third, and Fourth Embodiments, the steps involved correspond to those of the First Method Embodiment. For specific implementation manners, reference may be made to the relevant description part of the First Embodiment. The term "computer-readable storage medium" should be understood to include a single medium or multiple media including one or more instruction sets; it should also be understood to include any medium that can store, encode, or carry an instruction set for execution by a processor and cause the processor to execute any method in the present invention.

[0142] Those skilled in the art should understand that the foregoing modules or steps of the present invention can be implemented by a general computer device. Optionally, they can be implemented by program codes executable by a computing device, so that they can be stored in a storage device for execution by the computing device, or they can be separately fabricated into individual integrated circuit modules, or multiple modules or steps among them can be fabricated into a single integrated circuit module for implementation. The present invention is not limited to any specific combination of hardware and software.

[0143] Although the specific implementation manners of the present invention have been described above in conjunction with the accompanying drawings, this is not a limitation on the protection scope of the present invention. Those skilled in the art should understand that, based on the technical solution of the present invention, various modifications or deformations that can be made by those skilled in the art without creative efforts are still within the protection scope of the present invention.

Claims

1. A cardinality estimation method for database grouping query, characterized in that: include: The underlying data of the database is compressed and represented based on the entropy histogram; Performing data characterization on the histogram to obtain an attribute column; Learning the correlation between the elements in the attribute column based on a self-attention network and outputting a correlation matrix; Performing query feature representation on the histogram, encoding the query into a query feature set; The multi-layer perceptron is used to learn the association between the query feature set and the correlation matrix, and output the cardinality estimation value for database grouping query.

2. A cardinality estimation method for database grouping query according to claim 1, characterized in that: The entropy-based histogram compresses the underlying data of the database, including: Initialize the underlying data of the database, scan the entire table data and define the number of bucket splits; then, perform local optimization based on the determined number of splits, that is, start from a histogram of a single bucket containing all attribute values, and repeatedly select the best split point with the minimum entropy reduction until the required number of buckets is reached; then, perform a global optimization operation to output a sorted set of split points and define the boundaries of the buckets.

3. A cardinality estimation method for database grouping query according to claim 1, characterized in that: The histogram is represented by data characterization, including: converting the histogram into a set of vectors of the same dimension, combining the histogram of each attribute into an attribute column X = {x1, ..., x T }; where x i represents the histogram of the i-th attribute, and T represents the number of all attributes in the database.

4. A cardinality estimation method for database grouping query according to claim 3, characterized in that: In the process of data characterization of the database represented by the histogram, each relationship is traversed only once, and an insertion, deletion or update operation only affects one relationship.

5. A cardinality estimation method for database grouping query according to claim 1, characterized in that: The correlation between elements in the attribute column is learned based on the self-attention network, including: passing the attribute column to multiple stacked layers with the same structure, each stacked layer consists of two sublayers; the first sublayer is a multi-head attention layer, which is used to receive three inputs: key, value and query; the second sublayer is a feedforward sublayer, which is used to realize data mapping through a stacked fully connected network and activation function.

6. A cardinality estimation method for database grouping query according to claim 1, characterized in that: The query is characterized by representing the histogram, and the query is encoded as a query feature set, including: describing the query features of the SQL query as a set of features including tables, connections, predicates and groups, that is, performing table encoding, connection condition encoding, predicate encoding and grouping attribute encoding on the query features respectively.

7. A cardinality estimation method for database grouping query according to claim 6, characterized in that: The connection condition encoding includes: using the adjacency matrix of the connection graph to represent the connection conditions involved in the query, wherein the connection graph is composed of a group of nodes and a group of edges containing the connection conditions; using the Laplacian matrix to calculate the representation of each node by combining the feature vector of the node with the adjacency matrix of the connection graph through a graph convolutional network; and finally, generating the final connection representation after processing through a summing pooling layer.

8. A cardinality estimation system for database group query, characterized in that: include: The data compression module is configured to: compress and represent the underlying data of the database based on the entropy histogram; The data analysis module is configured to: perform data characterization on the histogram to obtain an attribute column; Learning the correlation between the elements in the attribute column based on a self-attention network and outputting a correlation matrix; A query module is configured to: perform query feature representation on the histogram and encode the query into a query feature set; The multi-layer perceptron is used to learn the association between the query feature set and the correlation matrix, and output the cardinality estimation value for database grouping query.

9. A computer-readable storage medium having a program stored thereon, characterized in that: When the program is executed by a processor, the steps in the cardinality estimation method for database grouping query as described in any one of claims 1 to 7 are implemented.

10. An electronic device comprising a memory, a processor, and a program stored in the memory and executable on the processor, characterized in that: When the processor executes the program, the steps in the cardinality estimation method for database grouping query as described in any one of claims 1 to 7 are implemented.

Images