A method, apparatus, device and medium for estimating the cardinality of a database.

Abstract

This invention discloses a database cardinality estimation method, apparatus, device, and medium, relating to the field of database data processing technology. First, it defines unique scan and join operator nodes to clearly represent complex query types and patterns. Then, it constructs a query graph representing the entire data table, encodes and initializes the features of the nodes in the query graph, and inputs them into a graph neural network (GNN) for message passing and hidden state updates. After message passing, global aggregation merges the embedded representations of all nodes into a graph vector, which contains the global structure and semantic information of the entire query graph. Finally, a multilayer perceptron (MLP) is used to output the final cardinality estimate. This process, after query graph construction, uses a graph neural network to embed the hidden information between nodes in the query graph, exhibiting strong model generalization ability for complex query structures and eliminating prediction bias in cardinality estimation.

Description

Technical Field

[0001] This invention relates to the field of database data processing technology, and in particular to a database cardinality estimation method, apparatus, device, and medium. Background Technology

[0002] Cardinality estimation is the core of the database management system query optimizer. By estimating the number of rows the optimizer might return in a query, cardinality estimation guides the optimizer to select the optimal query plan. This operation directly affects and even determines query efficiency. Theoretically, as long as accurate cardinality estimation and physical plan execution costs are provided, and plans can be effectively enumerated in a huge search space, the database can formulate the optimal query plan within a reasonable time. Current cardinality estimation methods mainly include two categories: traditional cardinality estimation and learning-based cardinality estimation. Traditional cardinality estimation can be further divided into statistical and sampling-based types, while learning-based cardinality estimation can be divided into data-driven, query-driven, and a hybrid of data-driven and query-driven types.

[0003] The core of traditional cardinality estimation based on statistics is to use a data structure, such as a histogram or data profile, to fit the data distribution on a table. Data profiles are used to estimate the number of different elements in the dataset. Using a random hash function and based on random assumptions, any dataset can be mapped into a sequence of random values ​​that follows a uniform distribution for estimation. The core of sampling-based methods is to estimate the overall distribution of the data by sampling a small dataset from the original dataset to reflect the relationship between different tables. Unlike statistical cardinality estimation methods, sampling-based cardinality estimation methods do not rely on specific assumptions. They collect tuples from different tables according to a certain sampling method and then use these tuples to estimate the cardinality.

[0004] Query-driven cardinality estimation, a type of learning-based cardinality estimation, primarily learns the relationship between database query load and query return results to estimate cardinality without actually executing the query. Query-driven cardinality estimation methods typically use machine learning algorithms to analyze query characteristics and establish a mapping between query load, column attributes, and table structure, thereby supporting efficient and accurate cardinality estimation. Data-driven cardinality estimation methods aim to more accurately predict the number of rows returned by a query by fitting the joint distribution of data in the database, thus improving the performance of the database system when handling complex queries. In data-driven cardinality estimation, the core of the model lies in learning the joint distribution of multiple columns of data in the database, thereby estimating the proportion of data that meets specific query conditions. Hybrid-driven cardinality estimation techniques combine the advantages of query-driven and data-driven approaches, further improving the accuracy and applicability of cardinality estimation by combining query features and data distribution information.

[0005] However, traditional cardinality estimation often leads to high prediction bias when dealing with complex and difficult query scenarios involving multiple columns, multiple predicates, and multiple table joins. Query-driven methods rely heavily on a large amount of labeled historical query data and their true cardinality for training. When query patterns change or new types of queries appear, the model often fails to generalize, resulting in decreased prediction accuracy. Data-driven models are trained based on existing data distributions and may not adapt well to new types of query patterns, especially with unseen query structures or complex predicates, resulting in poor generalization ability. Therefore, current traditional cardinality estimation and learning-based cardinality estimation often exhibit high prediction bias when performing cardinality estimation due to poor generalization ability when query patterns change or complex query types appear. Summary of the Invention

[0006] This invention provides a database cardinality estimation method, apparatus, device, and medium, which can solve the problem that in the existing technology, traditional cardinality estimation and learning-based cardinality estimation often have poor generalization ability when performing cardinality estimation due to changes in query patterns or the occurrence of complex query types, resulting in high prediction bias.

[0007] This invention provides a database cardinality estimation method, comprising the following steps:

[0008] Obtain table and column information from the database tables, generate scan and join operator nodes based on the table and column information, and construct a query graph;

[0009] The features of each node in the query graph are represented as feature vectors, and the initial hidden state of each node is obtained. Based on the message passing mechanism of graph neural network (GNN), message passing and hidden state updates are performed on each node to obtain the updated hidden state and feature vector of each node. The updated hidden state and feature vector of each node are globally aggregated to obtain the graph vector after the query graph changes.

[0010] The graph vectors are input into a multilayer perceptron (MLP) to obtain cardinality estimates of the database.

[0011] Preferably, the step of generating scan operator nodes and join operator nodes based on table information and column information, and constructing a query graph, includes:

[0012] Based on the table and column information of the database tables, generate scan operator node S(Sid,Tn,Cn,P,St,E) and join operator node J(Jid,Lid,Rid,JP,Jt,E);

[0013] Where: Sid and Jid represent the unique identifier id of each node, Tn represents table information, Cn represents column information, P represents query predicate, St represents the scanning method used by the scan node, Lid and Rid represent the id of the nodes connected to the left and right subtrees of the join node, JP represents the join condition, Jt represents the join method used by the join node, and E represents the true cardinality of the scan operator.

[0014] The information involved in the scan and join operations executed by the generated scan operator node and join operator node is set as nodes, and the association between scan and join operations is set as edges, thus constructing a query graph G = (V, E).

[0015] Preferably, the step of representing the features of each node in the query graph as a feature vector and obtaining the initial hidden state of each node includes:

[0016] One-Hot encoding is used to encode the feature information represented by each node in the query graph and convert it into numerical form to form multiple vectors corresponding to each node; the multiple vectors corresponding to each node are concatenated to form the feature vector corresponding to each node.

[0017] The feature vector corresponding to each node is mapped using a multilayer perceptron (MLP) to obtain the initial hidden state of each node.

[0018] Preferably, the step of message passing and hidden state updating for each node includes:

[0019] The message passing mechanism of a graph neural network (GNN) is used to perform message passing and hidden state updates for each node. The message passing includes message generation, message aggregation, and node state updates.

[0020] During message generation, each node starts from its own state, determines the message content to be transmitted through the edges between itself and its neighbors, and generates the message to be passed on to its neighbors; the message generation formula is:

[0021] m uv =f message (h u ,e uv )

[0022] Where: u represents each node in the query graph; h u Represents the hidden state of each node; v represents the neighboring nodes of the node; m uv Indicates the generated message; f message The function representing message generation;

[0023] During message aggregation, each node receives messages from all its neighboring nodes and integrates these messages to generate a comprehensive message vector m. v Its message aggregation formula is:

[0024]

[0025] Where: AGG represents an aggregate function; This represents all neighboring nodes of node v;

[0026] During node state updates, each node updates its own hidden state based on the aggregated neighbor messages and its own hidden state. The state update formula is as follows:

[0027]

[0028] in: This indicates the new hidden state of the node after the update; Indicates the current hidden state of the node; m v This represents the aggregated message.

[0029] Preferably, the step of globally aggregating the updated hidden state and feature vector of each node includes:

[0030] After message passing and hidden state updates are completed, each node in the query graph generates an updated feature vector. Global aggregation is performed using a dimension-wise summation method, adding the updated feature vectors of all nodes in the query graph dimension-wise to obtain a fixed-length graph vector. The dimension-wise summation formula is:

[0031]

[0032] Where: x i represents the feature vector of the i-th node; n represents the number of nodes in the query graph;

[0033] When there are n nodes in the query graph, and the dimension of the feature vector of each node is d, then the dimension of the graph vector after global aggregation is also d.

[0034] Preferably, obtaining the cardinality estimate includes:

[0035] The graph vector is input into the multilayer perceptron (MLP), and the graph vector h... graph After nonlinear mapping through multiple fully connected layers, a cardinality estimate is output; the mapping equation of its multilayer perceptron (MLP) is:

[0036] h (1) =σ(W (1) h graph +b (1) )

[0037] h (2) =σ(W (2) h (1) +b (2) )

[0038] Where: h graph W represents a fixed-dimensional vector obtained after message passing and global aggregation. (1) and W (2) These represent the weight matrices of the first and second layers, respectively; b (1) and b (2) σ represents the bias vector; h represents the nonlinear activation function (ReLU); (1) and h (2) This indicates the activated intermediate layer;

[0039] The output layer of the multilayer perceptron (MLP) generates a scalar value as a cardinality estimate, and its equation is as follows:

[0040]

[0041] Among them: W (3) and b (3) These represent the weights and biases of the output layer, respectively. This represents the final estimated base.

[0042] This invention also provides a database cardinality estimation device, comprising:

[0043] The module is used to obtain table and column information of database tables, generate scan and join operator nodes based on the table and column information, and construct a query graph.

[0044] The feature transformation module is used to represent the features of each node in the query graph as feature vectors and obtain the initial hidden state of each node; based on the message passing mechanism of the graph neural network (GNN), message passing and hidden state updates are performed on each node to obtain the updated hidden state and feature vector of each node; the updated hidden state and feature vector of each node are globally aggregated to obtain the graph vector after the query graph is transformed.

[0045] The cardinality estimation module is used to input graph vectors into the multilayer perceptron (MLP) to obtain cardinality estimates for the database.

[0046] This invention also provides an electronic device, including a memory and a processor;

[0047] The memory is used to store computer programs;

[0048] The memory is used to execute the computer program stored in the memory to implement the steps of a database cardinality estimation method as described above.

[0049] This invention also provides a computer-readable storage medium for storing a computer program, which, when executed by a processor, implements the steps of a database cardinality estimation method as described above.

[0050] This invention provides a database cardinality estimation method, apparatus, device, and medium, which have the following advantages compared with the prior art:

[0051] This invention first defines unique scan and join operator nodes to clearly represent complex query types and patterns. Then, it constructs a query graph representing the entire data table, encodes and initializes the features of the nodes in the query graph, and inputs them into a graph neural network (GNN) for message passing and hidden state updates. After message passing, global aggregation merges the embedded representations of all nodes into a single graph vector, which contains the global structure and semantic information of the entire query graph. Finally, a multilayer perceptron (MLP) is used to output the final cardinality estimate. This process clearly displays the complex query types and patterns in the database, thus constructing a query graph representing the entire data table. The graph neural network embeds the hidden information between the nodes in the query graph, exhibiting strong model generalization ability for complex query structures and eliminating prediction bias in cardinality estimation. Attached Figure Description

[0052] Figure 1 This is a schematic diagram of the overall process of a database cardinality estimation method provided in an embodiment of the present invention;

[0053] Figure 2 This is a schematic diagram illustrating the generation of the scan and join operators in a database cardinality estimation method provided in an embodiment of the present invention.

[0054] Figure 3 This is a schematic diagram of the input query graph architecture in the GNN architecture of a database cardinality estimation method provided in an embodiment of the present invention;

[0055] Figure 4 This is a schematic diagram of the message passing architecture in the GNN architecture of a database cardinality estimation method provided in an embodiment of the present invention;

[0056] Figure 5 This diagram illustrates the global aggregation and MLP processing in the GNN architecture of a database cardinality estimation method provided in an embodiment of the present invention. Detailed Implementation

[0057] To make the above-mentioned objects, features, and advantages of the present invention more apparent and understandable, specific embodiments of the present invention will be described in detail below with reference to the accompanying drawings. Many specific details are set forth in the following description to provide a thorough understanding of the present invention. However, the present invention can be practiced in many other ways different from those described herein, and those skilled in the art can make similar modifications without departing from the spirit of the present invention. Therefore, the present invention is not limited to the specific embodiments disclosed below.

[0058] See Figure 1 This invention provides a database cardinality estimation method, comprising the following steps:

[0059] Step S1: Data collection and graph construction; Input data table information and column information, use sampling algorithm to build scan and join operators for column attributes in each table, and transform them into graph G.

[0060] Step S2: Feature encoding and feature initialization; Perform feature initialization on the generated graph G, transforming the relevant information of each node in the graph into a form suitable for processing by the GNN model.

[0061] Step S3: Message passing and state update; use the message passing mechanism of GNN to learn the relationships between different nodes in the query graph.

[0062] Step S4: Global aggregation; After updating node features through message passing, the representations of all nodes are globally summed and aggregated to integrate the information of all nodes in the graph, ensuring that the final vector representation contains complete query structure information.

[0063] Step S5: Cardinality prediction using a multilayer perceptron; The aggregated graph vector is input into a multilayer perceptron (MLP), and a single numerical value is output through further nonlinear mapping as the cardinality estimate for the query.

[0064] Step S6: Cardinality estimation; When performing cardinality estimation by inputting a query, a new query is input, and corresponding scan nodes and join nodes are generated based on the query statement. After encoding, they are input into a trained graph neural network for training to estimate its cardinality.

[0065] Specifically:

[0066] Step S1 specifically includes:

[0067] Obtain table and column information from the dataset to generate scan operator nodes S(Sid,Tn,Cn,P,St,E) and join operator nodes J(Jid,Lid,Rid,JP,Jt,E); where: Sid and Jid represent the unique identifier id of each node, Tn represents table information, Cn represents column information, St represents the scanning method used by the scan node, Lid and Rid are the ids of the nodes connected to the left and right subtrees of the join node, JP represents the join condition, and Jt represents the join method used by the join node; for the predicate P of the scan operator node, the K-means clustering algorithm is used to first divide the columns into different intervals, then randomly select one interval as the predicate of the query, and use the number of data contained in the interval as the true cardinality E of the scan operator; at the same time, the information involved in the scan operation and the join operation are designed as nodes, and the association between the operations is designed as edges, thus forming a query graph G = (V,E); Figure 2 The image shows an example of constructing scan and join operators from a query table.

[0068] To generate the scan operator, it is necessary to sample the column attributes of all tables in the training set to ensure that the constructed graph can represent the features of the entire dataset.

[0069] For predicate generation in the scan operator, the K-means clustering algorithm is used to divide the values ​​in the column attributes into intervals, and then each interval is used as the predicate P of the scan operation. The number of elements in the interval is the true cardinality E of the scan operator. The specific execution steps include:

[0070] ① First, randomly select K values ​​as the centers of the data clusters.

[0071] ② Next, calculate the distance of each data point from the initial cluster center and assign the data points to the cluster corresponding to the nearest cluster center.

[0072] ③Then calculate the mean of all member data points in the cluster and update the position of the cluster center.

[0073] ④ Finally, repeat the above two steps until the position of the cluster center no longer changes.

[0074] For example: If there is a column of attributes with values ​​{2,3,5,6,8,9,11,12,14,15,30,31,32,33,34}, and the value of K is set to 3, and 3 data points are randomly selected as the initial centers, in this case, {5,12,32} will be selected.

[0075] Based on the above steps, first calculate the distance of each data point from the cluster center, and assign the data points to the nearest cluster. Then, update the position of the cluster center using the average value. Repeat this operation until the cluster centers converge, resulting in three clusters, which can be represented as follows:

[0076] 1: {2,3,5,6,8,9}; the corresponding predicate P is 2<=a<=9, and its true cardinality E is 6.

[0077] 2: {11,12,14,15}; the corresponding predicate P is 11<=a<=15, and its true cardinality E is 4.

[0078] 3: {30,31,32,33,34}; the corresponding predicate P is 30<=a<=34, and its true cardinality E is 5.

[0079] The join operator is generated based on the primary and foreign key relationships in the data table.

[0080] Step S2 specifically includes:

[0081] A single query graph is sampled and encoded and its features are initialized.

[0082] Feature Encoding: For the previously generated query graph, in order for the graph neural network to process these categorical features, it is necessary to encode the relevant features such as table names, column names, predicates, scan operation types, join conditions, and join types, converting them into numerical form. Based on this, sampling One-Hot encoding converts these discrete features into numerical form, such as... Figure 3 As shown.

[0083] like Figure 2 As shown, we have:

[0084] S1{s1,access_log,site_id,<3,St,3}.

[0085] S3{s3, Websites, id, = 1, St, 1}.

[0086] J1{s1,s3,A.site_id=W.id,Jt,1}.

[0087] The corresponding query is:

[0088] SELECT * FROM Websites;

[0089] JOIN access_log;

[0090] ON Websites.id=access_log.site_id;

[0091] WHERE Websites.id = 1;

[0092] AND access_log.site_id<3.

[0093] The query described here only involves the join between the Websites table and the access_log table. Websites and access_log can be encoded as [1,0] and [0,1] respectively. For the predicate, >, <, and = are encoded as [1,0,0], [0,1,0], and [0,0,1] respectively. If the data value after the predicate is 1, 2, or 3, then their encodings are [1,0,0], [0,1,0], and [0,0,1] respectively.

[0094] After encoding is completed, the features of each node are represented as a vector, which contains all the numerical features of the node and the numerically categorical features. Then, the generated feature vectors are concatenated. This operation is performed on each node to concatenate all the processed features to form a complete feature vector.

[0095] After obtaining a complete feature vector, a multi-layer perceptron (MLP) mapping is used to process the node feature vector. During the initialization process, the feature vector of each node is mapped through a multi-layer perceptron (MLP) or a linear layer to obtain the initial hidden state of the node. The features of different types of nodes are mapped to the hidden representation of the same dimension so that subsequent message passing and state updates can be performed in the same dimensional space.

[0096] The formula for implementing the initial hidden state of a node is:

[0097]

[0098] in: f represents the initial hidden state of node v; init This represents a linear mapping function or MLP used to transform input features.

[0099] During the mapping process, a non-linear activation function (such as ReLU) is typically used to increase the model's non-linear representation capability. Its formula is as follows:

[0100]

[0101] Where: W represents the learnable weight matrix; b represents the bias term; x vThis represents the initial feature vector of node v.

[0102] Step S3 specifically includes:

[0103] Message passing and state updates; message passing is the process by which nodes exchange information via edges, helping each node obtain information from its neighbors and enabling each node to accumulate context information. The core tasks of the message passing layer include message generation, message aggregation, and node state updates, such as... Figure 4 As shown.

[0104] Message Generation: During the message generation phase, each node generates a message to be passed on to its neighbors based on its current state. Starting from its own state, the node determines the message content to be passed on through the edges between itself and its neighbors. The formula for message generation is:

[0105] m uv =f message (h u ,e uv )

[0106] Where: each node u is based on its hidden state h u Generate message m uv To be passed to neighboring node v; f message The function representing message generation; e uv The features representing an edge are typically a linear transformation or a multilayer perceptron (MLP); the features of an edge may include the edge type (such as connection, scan) or other information related to the query structure.

[0107] Message aggregation: Message aggregation refers to the process by which each node receives messages from all its neighboring nodes and integrates these messages; this is one of the key steps in message passing. Node v receives messages from all its neighboring nodes u∈ Receive the messages m generated by each party uv These messages contain the state information of neighboring nodes, as well as edge information;

[0108] All received messages are merged using an aggregation function to generate a comprehensive message vector m. v Its formula is:

[0109]

[0110] Where: AGG represents an aggregate function.

[0111] Aggregation methods include:

[0112] Sum: Summing all received messages allows you to accumulate information about neighboring nodes.

[0113] Mean: Take the average value of all received messages to reduce the deviation of information between nodes.

[0114] Max Pooling: Takes the maximum value among all messages and selects the strongest feature.

[0115] Therefore, message aggregation ensures that each node not only obtains information from its direct neighbors, but also integrates information from these neighboring nodes; through this aggregation, the hidden state of a node gradually contains more information about the entire query graph, enabling the model to capture complex multi-node interactions.

[0116] Node state update: During the state update phase, each node updates its new hidden state based on the aggregated neighbor messages and its own hidden state. The state update enables each node to adjust itself to adapt to the information transmitted by neighboring nodes.

[0117] The new hidden state of a node From the current hidden state and the aggregated message m v The update is performed using a multilayer perceptron (MLP), and the formula is as follows:

[0118]

[0119] This invention introduces the nonlinear activation function ReLU during the state update process to introduce nonlinearity and enhance the model's representational power. Its formula is as follows:

[0120]

[0121] Where: W represents the learnable weight matrix; This indicates that the current state of the node and the aggregated message are concatenated as new input.

[0122] State updates are an iterative process that is usually repeated multiple times. In each iteration, each node gradually acquires more information about distant nodes, so that the final hidden state contains global information about the entire graph.

[0123] Step S4 specifically includes:

[0124] Global aggregation; after the message passing layer is completed, each node in the query graph generates an updated representation vector. These vectors contain local information of each node and aggregation information from its neighbors. To facilitate cardinality estimation by the Multilayer Perceptron (MLP), this invention integrates the local information of each node into a unified vector representation to facilitate capturing the overall structure and content of the query graph, such as... Figure 5 As shown.

[0125] For the query graph that has been previously passed through messages, this invention uses a dimension-wise summation method to perform global aggregation, adding the representation vectors of all nodes in the query graph dimension by dimension to obtain a graph vector of fixed length; assuming there are n nodes in the query graph, and the dimension of the representation vector of each node is d, then the graph representation vector after global aggregation is also d in dimension.

[0126] The formula for summation dimension by dimension is:

[0127]

[0128] Where: x i This represents the representation vector of the i-th node.

[0129] By performing a dimension-wise summation operation, this invention aggregates the information of all nodes contained in the vector, capturing the overall features of the entire graph structure. Furthermore, after the dimension-wise summation, the output graph representation vector has a fixed dimension, which makes the model consistent when processing query graphs of different sizes and adapts to variable-scale input requirements.

[0130] Therefore, the globally aggregated graph representation vector not only contains the features of a single node, but also incorporates the global information of the entire graph. This global representation can reflect the overall structure of the query graph, the connection patterns of the nodes, and the density of the graph.

[0131] Step S5 specifically includes:

[0132] Cardinality prediction is performed using a multilayer perceptron. After the message passing and global aggregation phases, the query graph is represented as a fixed-length graph vector. The overall structure and content of the graph are captured by combining the comprehensive information of the nodes and edges of the query graph. For this fixed-length graph vector, the present invention uses a multilayer perceptron (MLP) for cardinality estimation.

[0133] In the process of cardinality estimation using a multilayer perceptron (MLP), the graph represents the vector h. graph It goes through several fully connected layers of nonlinear mapping, and finally outputs a cardinality estimate.

[0134] The MLP mapping formula is:

[0135] h (1) =σ(W (1) h graph +b (1) )

[0136] h (2) =σ(W (2) h (1) +b (2) )

[0137] Where: h graphW represents a fixed-dimensional vector obtained after message passing and global aggregation. (1) and W (2) These represent the weight matrices of the first and second layers, respectively; b (1) and b (2) σ represents the bias vector; h represents the nonlinear activation function (ReLU); (1) and h (2) This represents the intermediate layer representation after activation.

[0138] Finally, the output layer of the multilayer perceptron (MLP) generates a scalar value as a log-cardinality estimate, with the following formula:

[0139]

[0140] Among them: W (3) and b (3) These represent the weights and biases of the output layer, respectively. This represents the final cardinality estimate.

[0141] Regarding the model's loss function and optimization, this invention uses mean squared error (MSE) loss to measure the difference between the predicted value and the true logarithm for model training. The formula is as follows:

[0142]

[0143] in: This represents the base estimate predicted by the model; y i Represents the true base value; and log(y) i ) represent the logarithms of the predicted value and the actual value, respectively.

[0144] By minimizing the mean squared error loss, the model can continuously adjust its parameters so that the prediction results gradually approach the true values, thereby optimizing the accuracy of cardinality estimation. After several rounds of iterative training, the GNN model can accurately model the query graph and extract effective global features from the query graph for cardinality prediction. The trained model will be applied to new query graphs during the inference phase to perform real-time cardinality prediction.

[0145] Step S6 specifically includes:

[0146] Given a query, the system generates corresponding scan and join nodes based on the query statement, performs feature encoding and feature initialization, and then processes them through a trained graph neural network (GNN). The GNN model performs message passing on the query graph again, updating the representation of each node. After message passing is complete, global aggregation merges the embedding representations of all nodes into a graph vector, which contains the global structure and semantic information of the entire query graph. The globally aggregated graph vector is then input into a multilayer perceptron (MLP). The MLP processes the input vector through several fully connected layers and nonlinear activation functions (such as ReLU) and outputs the final cardinality estimate.

[0147] For example, a query can be entered as follows:

[0148] SELECT*FROM orders o,lineitem l;

[0149] WHERE o.orderkey=l.orderkey;

[0150] AND o.orderkey>=c1;

[0151] AND l.orderdate>c2;

[0152] AND l.clerk = c3;

[0153] AND o.orderkey=l.orderkey.

[0154] The node constructed using this invention is represented as follows:

[0155] S(s1, orders, orderkey, >=c1, IS, NULL);

[0156] S(s2, lineitem, orderdate, >c2, IS, NULL);

[0157] S(s3,lineitem,clerk,=c3,IS,NULL);

[0158] J(j1,s2,s3,null,FJ,NULL);

[0159] J(j2, s1, j1, o.orderkey=l.orderkey, FJ, NULL).

[0160] The constructed graph data is then encoded and its features initialized, and input into the previously trained graph neural network for message passing, updating the representation of each node. After message passing is complete, the embedding representations of all nodes are merged into a graph vector through global aggregation. This vector contains the global structure and semantic information of the entire query graph. The globally aggregated graph vector is then input into a multilayer perceptron (MLP). The MLP processes the input vector through several fully connected layers and nonlinear activation functions (such as ReLU) and outputs the final cardinality estimate.

[0161] The embodiments described above are merely illustrative of several implementations of the present invention, and while the descriptions are relatively specific and detailed, they should not be construed as limiting the scope of the invention patent. It should be noted that those skilled in the art can make various modifications and improvements without departing from the concept of the present invention, and these all fall within the protection scope of the present invention. Therefore, the protection scope of this invention patent should be determined by the appended claims.

Claims

1. A database cardinality estimation method, characterized in that, Includes the following steps: Obtain table and column information from the database tables, generate scan and join operator nodes based on the table and column information, and construct a query graph; The features of each node in the query graph are represented as feature vectors, and the initial hidden state of each node is obtained. Based on the message passing mechanism of graph neural network (GNN), message passing and hidden state updates are performed on each node to obtain the updated hidden state and feature vector of each node. The updated hidden state and feature vector of each node are globally aggregated to obtain the graph vector after the query graph changes. The graph vectors are input into a multilayer perceptron (MLP) to obtain a cardinality estimate of the database. The process of message passing and hidden state updating for each node includes: The message passing mechanism of a graph neural network (GNN) is used to perform message passing and hidden state updates for each node. The message passing includes message generation, message aggregation, and node state updates. During message generation, each node starts from its own state, determines the message content to be transmitted through the edges between itself and its neighbors, and generates the message to be passed on to its neighbors; the message generation formula is: m uv =f message (h u ,e uv ) Where: u represents each node in the query graph; h u Represents the hidden state of each node; v represents the neighboring nodes of the node; m uv Indicates the generated message; f message The function that represents message generation; During message aggregation, each node receives messages from all its neighboring nodes and integrates these messages to generate a comprehensive message vector m. v Its message aggregation formula is: Where: AGG represents an aggregate function; This represents all neighboring nodes of node v; During node state updates, each node updates its own hidden state based on the aggregated neighbor messages and its own hidden state. The state update formula is as follows: in: This indicates the new hidden state of the node after the update; Indicates the current hidden state of the node; m v This represents the aggregated message.

2. The database cardinality estimation method according to claim 1, characterized in that, The process of generating scan operator nodes and join operator nodes based on table and column information, and constructing a query graph, includes: Based on the table and column information of the database tables, generate scan operator node S(Sid,Tn,Cn,P,St,E) and join operator node J(Jid,Lid,Rid,JP,Jt,E); Where: Sid and Jid represent the unique identifier id of each node, Tn represents table information, Cn represents column information, P represents query predicate, St represents the scanning method used by the scan node, Lid and Rid represent the id of the nodes connected to the left and right subtrees of the join node, JP represents the join condition, Jt represents the join method used by the join node, and E represents the true cardinality of the scan operator. The information involved in the scan and join operations executed by the generated scan operator node and join operator node is set as nodes, and the association between scan and join operations is set as edges, thus constructing a query graph G = (V, E).

3. The database cardinality estimation method according to claim 1, characterized in that, The step of representing the features of each node in the query graph as a feature vector and obtaining the initial hidden state of each node includes: One-Hot encoding is used to encode the feature information represented by each node in the query graph and convert it into numerical form to form multiple vectors corresponding to each node; the multiple vectors corresponding to each node are concatenated to form the feature vector corresponding to each node. The feature vector corresponding to each node is mapped using a multilayer perceptron (MLP) to obtain the initial hidden state of each node.

4. The database cardinality estimation method according to claim 1, characterized in that, The step of globally aggregating the updated hidden state and feature vector of each node includes: After message passing and hidden state updates are completed, each node in the query graph generates an updated feature vector. Global aggregation is performed using a dimension-wise summation method, adding the updated feature vectors of all nodes in the query graph dimension-wise to obtain a fixed-length graph vector. The dimension-wise summation formula is: Where: x i represents the feature vector of the i-th node; n represents the number of nodes in the query graph; When there are n nodes in the query graph, and the dimension of the feature vector of each node is d, then the dimension of the graph vector after global aggregation is also d.

5. The database cardinality estimation method according to claim 1, characterized in that, The acquisition of the cardinality estimate includes: The graph vector is input into the multilayer perceptron (MLP), and the graph vector h... graph After nonlinear mapping through multiple fully connected layers, a cardinality estimate is output; the mapping equation of its multilayer perceptron (MLP) is: h (1) =σ(w (1) h graph +b (1) ) h (2) =σ(W (2) h (1) +b (2) ) Where: h graph W represents a fixed-dimensional vector obtained after message passing and global aggregation. (1) and W (2) These represent the weight matrices of the first and second layers, respectively; b (1) and b (2) σ represents the bias vector; h represents the nonlinear activation function (ReLU); (1) and h (2) This indicates the activated intermediate layer; The output layer of the multilayer perceptron (MLP) generates a scalar value as a cardinality estimate, and its equation is as follows: Among them: W (3) and b (3) These represent the weights and biases of the output layer, respectively. This represents the final estimated base.

6. A database cardinality estimation device, characterized in that, include: The module is used to obtain table and column information of database tables, generate scan and join operator nodes based on the table and column information, and construct a query graph. The feature transformation module is used to represent the features of each node in the query graph as feature vectors and obtain the initial hidden state of each node; based on the message passing mechanism of the graph neural network (GNN), message passing and hidden state updates are performed on each node to obtain the updated hidden state and feature vector of each node; the updated hidden state and feature vector of each node are globally aggregated to obtain the graph vector after the query graph is transformed. The cardinality estimation module is used to input graph vectors into the multilayer perceptron (MLP) to obtain cardinality estimates of the database. The process of message passing and hidden state updating for each node includes: The message passing mechanism of a graph neural network (GNN) is used to perform message passing and hidden state updates for each node. The message passing includes message generation, message aggregation, and node state updates. During message generation, each node starts from its own state, determines the message content to be transmitted through the edges between itself and its neighbors, and generates the message to be passed on to its neighbors; the message generation formula is: m uv =f message (h u ,e uv ) Where: u represents each node in the query graph; h u Represents the hidden state of each node; v represents the neighboring nodes of the node; m uv Indicates the generated message; f message The function that represents message generation; During message aggregation, each node receives messages from all its neighboring nodes and integrates these messages to generate a comprehensive message vector m. v Its message aggregation formula is: Where: AGG represents an aggregate function; This represents all neighboring nodes of node v; During node state updates, each node updates its own hidden state based on the aggregated neighbor messages and its own hidden state. The state update formula is as follows: in: This indicates the new hidden state of the node after the update; Indicates the current hidden state of the node; m v This represents the aggregated message.

7. An electronic device, characterized in that, include: Memory and processor; The memory is used to store computer programs; When the processor executes the computer program stored in the memory, it implements the steps of the database cardinality estimation method as described in any one of claims 1 to 5.

8. A computer-readable storage medium, characterized in that, Used to store a computer program, which, when executed by a processor, implements the steps of a database cardinality estimation method as described in any one of claims 1 to 5.

Images