Database cardinal number estimation method and device, equipment and medium

Abstract

The invention discloses a database cardinal number estimation method and device, equipment and a medium, and relates to the technical field of database data processing.The method comprises the steps that unique scan operator nodes and join operator nodes are defined, complex query types and query modes are clearly represented, then a query graph capable of representing a whole data table is constructed, and a database cardinal number estimation result is obtained; feature coding and feature initialization are carried out on nodes in a query graph, then a graph neural network GNN is input to carry out message passing and hidden state updating, after message passing is completed, embedded representations of all the nodes are combined into a graph vector through global aggregation, the graph vector comprises a global structure and semantic information of the whole query graph, and the overall structure and semantic information of the whole query graph are integrated. And finally, outputting a final cardinality estimation value by using a multi-layer perceptron (MLP), and in the process, after the query graph is constructed, embedding hidden information among nodes in the query graph by using a graph neural network, so that the method has very strong model generalization ability for a complex query structure, and eliminates prediction deviation during cardinality estimation.

Description

Technical Field

[0001] The present invention relates to the technical field of database data processing, and in particular to a database cardinality estimation method, device, equipment and medium. Background Art

[0002] Cardinality estimation is the core of the query optimizer of the database management system. Cardinality estimation guides the query optimizer to select the best query plan by estimating the number of rows of the query that the optimizer may return. This operation will directly affect or even determine the query efficiency. In theory, as long as accurate cardinality estimation and physical plan execution cost are provided, and plans can be effectively enumerated in the huge search space, the database can formulate the best query plan in a reasonable time. At present, cardinality estimation methods mainly include traditional cardinality estimation and learning cardinality estimation. Traditional cardinality estimation can be divided into two types: statistics-based and sampling-based, while learning cardinality estimation can be divided into three types: data-driven, query-driven, and data-driven and query-driven hybrid.

[0003] The core of the statistics-based method in traditional cardinality estimation is to use some data structure, such as histogram and data portrait to fit the data distribution on the table, and use data portrait to estimate the number of different elements in the data set. Using random hash function and based on random assumptions, any data set can be mapped into a random value sequence that obeys a uniform distribution for estimation. The core of the sampling-based method is to estimate the overall distribution of the data by sampling from the original data set to a small data set to reflect the association between different tables. Different from the statistical cardinality estimation method, the sampling-based cardinality estimation method does not rely on specific assumptions. It collects tuples from different tables according to a certain sampling method, and further uses these tuples to estimate the cardinality.

[0004] Query-driven cardinality estimation in learning cardinality estimation mainly learns the relationship between database query load and query return results in order to estimate its cardinality without actually executing the query. Query-driven cardinality estimation methods usually use machine learning algorithms to parse query features and establish a mapping relationship 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, thereby improving the performance of the database system when processing complex queries. In data-driven cardinality estimation, the core of the model is to learn the joint distribution of multiple columns of data in the database, so as to estimate the proportion of data that meets specific query conditions; hybrid-driven cardinality estimation technology combines the advantages of query-driven and data-driven, and further improves the accuracy and applicability of cardinality estimation by combining query features with data distribution information.

[0005] However, in traditional cardinality estimation, when dealing with complex and difficult query scenarios involving multi-column, multi-predicate queries, and multi-table joins, it often leads to high prediction deviations. The query-driven method is highly dependent on a large number of historical queries and labeled data of their real cardinality for training. When the query pattern changes or new types of queries appear, the model often cannot generalize, resulting in a decrease in prediction accuracy. The data-driven model is trained based on the existing data distribution and may not be able to adapt well to new types of query patterns, especially when the query structure or complex predicates have not been seen before, the model's generalization ability is poor. Therefore, at this stage, traditional cardinality estimation and learning cardinality estimation often have high prediction deviations when performing cardinality estimation due to the poor generalization ability of the model when the query pattern changes or complex query types appear. Summary of the invention

[0006] The embodiments of the present invention provide a database cardinality estimation method, apparatus, device and medium, which can solve the problem in the prior art that the current traditional cardinality estimation and learning cardinality estimation often have high prediction deviation when performing cardinality estimation due to poor generalization ability of the model when the query mode changes or complex query types appear.

[0007] An embodiment of the present invention provides a method for estimating the cardinality of a database, comprising the following steps:

[0008] Obtain table information and column information of the database data table, generate scan operator nodes and join operator nodes according to the table information 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 the graph neural network GNN, message passing and hidden state update 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 vector is input into the multi-layer perceptron MLP to obtain the cardinality estimation of the database.

[0011] Preferably, generating scan operator nodes and join operator nodes according to table information and column information, and constructing a query graph, includes:

[0012] Generate scan operator node S (Sid, Tn, Cn, P, St, E) and join operator node J (Jid, Lid, Rid, JP, Jt, E) according to the table information and column information of the database data table;

[0013] Among them: Sid and Jid represent the unique 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 IDs of the nodes connected by the left and right subtrees of the join node, JP represents the connection condition, Jt represents the connection method used by the join node, and E represents the real cardinality of the scan operator;

[0014] The information involved in the scan operation and join operation executed by the generated scan operator node and the join operator node is set as a node, and the association between the scan operation and the join operation is set as an edge, so as to construct a query graph G = (V, E).

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

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

[0017] The multi-layer perceptron MLP is used to map the feature vector corresponding to each node to obtain the initial hidden state corresponding to each node.

[0018] Preferably, the message transmission and hidden state update for each node includes:

[0019] The message passing mechanism of the graph neural network GNN is used to pass messages and update hidden states of each node, wherein the message passing includes message generation, message aggregation and node state update;

[0020] When generating a message, each node starts from its own state, determines the content of the message to be transmitted through the edges between it and its neighbors, and generates the message to be transmitted 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 corresponding to each node; v represents the neighbor node of the node; m uv Indicates the generated message; f message A 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 the aggregation function; Represents all neighbor nodes of node v;

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

[0027]

[0028] in: Represents the new hidden state of the node after updating; Indicates the current hidden state of the node; m v Represents an aggregated message.

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

[0030] After the message passing and hidden state update are completed, each node in the query graph generates an updated feature vector. The dimension-by-dimension summation method is used for global aggregation. The updated feature vectors of all nodes in the query graph are added dimension by dimension to obtain a graph vector of fixed length. The dimension-by-dimension 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, the dimension of the feature vector of each node is d, so the dimension of the graph vector after global aggregation is also d.

[0034] Preferably, obtaining the estimated cardinality value includes:

[0035] The graph vector is input into the multi-layer perceptron MLP, and the graph vector h graph After nonlinear mapping of multiple fully connected layers, a cardinality estimate is output; the mapping equation of the multi-layer 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 represents the fixed-dimensional vector obtained after message passing and global aggregation; W (1) and W (2) Represent the weight matrices of the first and second layers respectively; b (1) and b (2) represents the bias vector; σ represents the nonlinear activation function (ReLU); h (1) and h (2) Represents the middle layer after activation;

[0039] The output layer of the multi-layer perceptron MLP generates a scalar value as the cardinality estimate, whose equation is:

[0040]

[0041] Where: W (3) and b (3) Represent the weight and bias of the output layer respectively; Represents the final estimated cardinality.

[0042] The embodiment of the present invention further provides a database cardinality estimation device, comprising:

[0043] A construction module is used to obtain table information and column information of a database data table, generate scan operator nodes and join operator nodes according to the table information 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 a feature vector 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 update 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 changed;

[0045] The cardinality estimation module is used to input the graph vector into the multi-layer perceptron MLP to obtain the cardinality estimation value of the database.

[0046] An embodiment of the present invention further 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 implement the steps of the above-mentioned method for estimating the cardinality of a database when executing the computer program stored in the memory.

[0049] An embodiment of the present invention further provides a computer-readable storage medium for storing a computer program, wherein when the computer program is executed by a processor, the steps of the database cardinality estimation method as described above are implemented.

[0050] The embodiments of the present invention provide a database cardinality estimation method, device, equipment and medium. Compared with the prior art, the advantages thereof are as follows:

[0051] The present invention first defines unique scan operator nodes and join operator nodes to clearly represent complex query types and query patterns, and then constructs a query graph that can represent the entire data table, performs feature encoding and feature initialization on the nodes in the query graph, and then inputs the graph neural network GNN for message passing and hidden state update. After the message passing is completed, the embedded representations of all nodes are merged into a graph vector through global aggregation. The graph vector contains the global structure and semantic information of the entire query graph, and finally uses the multi-layer perceptron MLP to output the final cardinality estimate. This process clearly displays the complex query types and query patterns in the database, thereby constructing a query graph that can represent the entire data table, and uses the graph neural network to embed the hidden information between the nodes in the query graph. It has a very strong model generalization ability for complex query structures and eliminates the prediction bias in cardinality estimation. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0053] Figure 2 A schematic diagram of an example of generating a scan operator and a join operator for a database cardinality estimation method provided by an embodiment of the present invention;

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

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

[0056] Figure 5 A schematic diagram of global aggregation and MLP processing in a GNN architecture of a database cardinality estimation method provided in an embodiment of the present invention. DETAILED DESCRIPTION

[0057] In order to make the above-mentioned objects, features and advantages of the present invention more obvious and easy to understand, the specific embodiments of the present invention are described in detail below in conjunction with the accompanying drawings. In the following description, many specific details are set forth to facilitate a full understanding of the present invention. However, the present invention can be implemented in many other ways different from those described herein, and those skilled in the art can make similar improvements without violating the connotation of the present invention, so the present invention is not limited by the specific embodiments disclosed below.

[0058] See also Figure 1 , an embodiment of the present invention provides a database cardinality estimation method, comprising the following steps:

[0059] Step S1: Data collection and graph construction; input data table related information and column related information, establish scan operators and join operators for column attributes in each table through sampling algorithm, and convert them into graph G.

[0060] Step S2: Feature encoding and feature initialization: Initialize the generated graph G and convert the relevant information of each node in the graph into a form suitable for GNN model processing.

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

[0062] Step S4: Global aggregation: After updating the 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 and ensure that the final vector representation contains complete query structure information.

[0063] Step S5: Multi-layer perceptron cardinality prediction: The aggregated graph vector is input into a multi-layer perceptron (MLP), and a single value is output through further nonlinear mapping as the cardinality estimate of the query.

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

[0065] Specifically:

[0066] Step S1 specifically includes:

[0067] Obtain the table information and column information of the data set 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 identification id of each node, Tn represents the table information, Cn represents the column information, St represents the scanning method adopted 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 connection condition, and Jt represents the connection method adopted by the join node; for the predicate P of the scan operator node, the K-means clustering algorithm is used to first divide the column into different intervals, and then randomly select one of the intervals as the predicate of the query, and the number of data contained in the interval is used as the real cardinality E of the scan operator; at the same time, the information involved in the scan operation and the join operation is designed as nodes, and the associations between the operations are designed as edges, thereby forming a query graph G = (V, E); as shown Figure 2 The following is an example of constructing a scan operator and a join operator from a query table.

[0068] For the generation of 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 characteristics of the entire data set.

[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 real cardinality E of the scan operator. The specific execution steps include:

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

[0071] ② Secondly, calculate the distance between each data point and the initial cluster center and assign the data point 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, there is a column of attribute values ​​​​{2,3,5,6,8,9,11,12,14,15,30,31,32,33,34}, set the value of K to 3, and randomly select 3 data points as the initial centers. Here, {5,12,32} will be selected.

[0075] According to the above steps, we first calculate the distance between each data point and the cluster center, and assign the data point to the cluster with the closest distance. Then, we update the position of the cluster center by the average value. We repeat this operation until the cluster center converges. Then, we can get three clusters, which can be expressed as:

[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] For the generation of join operators, the corresponding join operators are generated according to 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 features initialized.

[0082] Feature encoding: For the query graph generated previously, in order to enable the graph neural network to process these categorical features, it is necessary to encode the table name, column name, predicate, scan operation type, connection condition, and connection type involved and convert them into numerical form. Based on this, sampling One-Hot encoding converts these discrete features into numerical form, such as Figure 3 shown.

[0083] like Figure 2 As shown, there are:

[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 above 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 connection 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 predicates, >, <, = are encoded as [1,0,0], [0,1,0] and [0,0,1] respectively. The data values ​​after the predicate are assumed to be 1, 2, and 3, then their encodings are [1,0,0], [0,1,0] and [0,0,1] respectively.

[0094] After encoding, the features of each node are represented as a vector, which contains all the numerical features and digitized categorical features of the node; then the generated feature vectors are concatenated, and each node will perform this operation to concatenate all processed features to form a complete feature vector.

[0095] After obtaining a complete feature vector, the node feature vector is processed by multi-layer perceptron (MLP) mapping. During the initialization process, the feature vector of each node is mapped through a multi-layer perceptron (MLP) or linear layer to obtain the initial hidden state of the node, and 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 implementation formula of the initial hidden state of the node is:

[0097]

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

[0099] In the mapping process, a nonlinear activation function (such as ReLU) is usually used to increase the nonlinear representation ability of the model. The formula is:

[0100]

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

[0102] Step S3 specifically includes:

[0103] Message passing and status update: Message passing is the process of transmitting information between nodes through edges, which helps each node obtain information from its neighbors and enables each node to accumulate context information. The core work of the message passing layer includes message generation, message aggregation and node status update, such as Figure 4 shown.

[0104] Message generation: In the message generation phase, each node generates a message to be transmitted to its neighbors based on its current state. Starting from its own state, the node determines the content of the message to be transmitted through the edges between it 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 the neighbor node v; f message Indicates the function of message generation; e uv Represents the features of the edge, usually a linear transformation or a multi-layer perceptron (MLP); the features of the edge may contain the type of edge (such as connection, scan) or other information related to the query structure.

[0107] Message aggregation: Message aggregation refers to the process in which each node receives messages from all its neighboring nodes and integrates these messages. This is one of the key steps in message transmission. Receive the message m generated by each uv ;These messages contain the status information of neighbor nodes and edge information;

[0108] All received messages are merged through an aggregation function to generate a comprehensive message vector m v , the formula is:

[0109]

[0110] Among them: AGG represents the aggregation function.

[0111] Aggregation methods include:

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

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

[0114] Max Pooling: Take the maximum value of all messages and select the strongest feature.

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

[0116] Node state update: In 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 neighbor nodes.

[0117] The new hidden state of the node From the current hidden state and the aggregated message m v It is updated by the multi-layer perceptron MLP, and its formula is:

[0118]

[0119] The present invention introduces a nonlinear activation function ReLU in the process of state updating to introduce nonlinearity to enhance the representation ability of the model. The formula is:

[0120]

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

[0122] State updating is an iterative process, which is usually repeated for multiple rounds. In each round of iteration, each node will gradually obtain more information about distant nodes, so that the final hidden state contains the global information of 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, which contains the local information of each node and the aggregated information from its neighbors; In order to facilitate the cardinality estimation of the multi-layer perceptron (MLP), the present invention integrates the local information of each node into a unified vector representation to capture the overall structure and content of the query graph, such as Figure 5 shown.

[0125] For the query graph that has previously undergone message passing, the present invention adopts a dimension-by-dimensional 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 that 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 of dimension d.

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

[0127]

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

[0129] Through the dimension-by-dimensional summation operation, the present invention aggregates the information of all nodes contained in the vector and captures the overall characteristics of the entire graph structure; and after the dimension-by-dimensional summation, the output graph representation vector dimension is fixed, so that the model is 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 combines the global information of the entire graph. This global representation can reflect the overall structure of the query graph, the connection pattern of the nodes, the density of the graph and other characteristics.

[0131] Step S5 specifically includes:

[0132] A multi-layer perceptron is used for cardinality prediction; after the message passing and global aggregation stages, the query graph is represented as a fixed-length graph vector, and the comprehensive information of the nodes and edges of the query graph is combined to capture the overall structure and content of the graph. For the fixed-length graph vector, the present invention uses a multi-layer perceptron MLP for cardinality estimation.

[0133] In the process of cardinality estimation using multi-layer perceptron (MLP), the graph representation vector h graph It will go through several fully connected layers of nonlinear mapping and finally output 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 graphrepresents the fixed-dimensional vector obtained after message passing and global aggregation; W (1) and W (2) Represent the weight matrices of the first and second layers respectively; b (1) and b (2) represents the bias vector; σ represents the nonlinear activation function (ReLU); h (1) and h (2) Represents the intermediate layer representation after activation.

[0138] Finally, the output layer of the multi-layer perceptron (MLP) generates a scalar value as the logarithmic cardinality estimate, which is formulated as:

[0139]

[0140] Where: W (3) and b (3) Represent the weight and bias of the output layer respectively; Represents the final cardinality estimate.

[0141] For the loss function and optimization of the model, in order to train the model, the present invention uses the mean square error (MSE) loss to measure the difference between the predicted value and the true cardinality logarithm, and the formula is:

[0142]

[0143] in: represents the estimated cardinality of the model prediction; y i Represents the true cardinality value; and log(y i ) represent the logarithm of the predicted value and the true value, respectively.

[0144] By minimizing the mean square error loss, the model can continuously adjust its parameters so that the prediction results gradually approach the true value, thereby optimizing the accuracy of cardinality estimation; after several rounds of iterative training, the GNN model has been able to accurately model the query graph and extract effective global features from the query graph for cardinality prediction. The trained model will be applied to the new query graph in the inference stage for real-time cardinality prediction.

[0145] Step S6 specifically includes:

[0146] A query is input, and the corresponding scan nodes and join nodes are generated according to the query statement. The features are encoded and initialized, and then processed by the trained graph neural network. The GNN model will pass messages to the query graph again to update the representation of each node. After the message passing is completed, the embedded 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 will be input into the multi-layer perceptron (MLP). The MLP processes the input vector through several layers of full connection and non-linear activation functions (such as ReLU) and outputs the final cardinality estimate.

[0147] For example, enter a query, which is represented as:

[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 by the present invention is represented as:

[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 feature initialized, and input into the previously trained graph neural network for message passing to update the representation of each node. After the message passing is completed, the embedded 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 will be input into the multi-layer perceptron (MLP), which processes the input vector through several layers of full connection and nonlinear activation functions (such as ReLU) and outputs the final cardinality estimate.

[0161] The above-mentioned embodiments only express several implementation methods of the present invention, and the descriptions thereof are relatively specific and detailed, but they cannot be understood as limiting the scope of the invention patent. It should be pointed out that, for ordinary technicians in this field, several variations and improvements can be made without departing from the concept of the present invention, and these all belong to the protection scope of the present invention. Therefore, the protection scope of the patent of the present invention shall be subject to the attached claims.

Claims

1. A database cardinality estimation method, characterized in that: The following steps are involved: Obtain table information and column information of the database data table, generate scan operator nodes and join operator nodes according to the table information 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 the graph neural network GNN, message passing and hidden state update 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 vector is input into the multi-layer perceptron MLP to obtain the cardinality estimation of the database.

2. A database cardinality estimation method according to claim 1, characterized in that: The step of generating scan operator nodes and join operator nodes according to table information and column information, and constructing a query graph, includes: Generate scan operator node S (Sid, Tn, Cn, P, St, E) and join operator node J (Jid, Lid, Rid, JP, Jt, E) according to the table information and column information of the database data table; Among them: Sid and Jid represent the unique 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 IDs of the nodes connected by the left and right subtrees of the join node, JP represents the connection condition, Jt represents the connection method used by the join node, and E represents the real cardinality of the scan operator; The information involved in the scan operation and join operation executed by the generated scan operator node and the join operator node is set as a node, and the association between the scan operation and the join operation is set as an edge, so as to construct a query graph G = (V, E).

3. A database cardinality estimation method according to claim 1, characterized in that: The feature of each node in the query graph is represented as a feature vector, and the initial hidden state of each node is obtained, including: One-Hot encoding is used to encode the feature information represented by each node in the query graph, and the feature information is converted into numerical form to form multiple vectors corresponding to each node; the multiple vectors corresponding to each node are feature concatenated to form a feature vector corresponding to each node; The multi-layer perceptron MLP is used to map the feature vector corresponding to each node to obtain the initial hidden state corresponding to each node.

4. A database cardinality estimation method according to claim 1, characterized in that: The message transmission and hidden state update for each node include: The message passing mechanism of the graph neural network GNN is used to pass messages and update hidden states of each node, wherein the message passing includes message generation, message aggregation and node state update; When generating a message, each node starts from its own state, determines the content of the message to be transmitted through the edges between it and its neighbors, and generates the message to be transmitted 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 corresponding to each node; v represents the neighbor node of the node; m uv Indicates the generated message; f message A function representing 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 the aggregation function; Represents all neighbor nodes of node v; When the node state is updated, each node updates its own new hidden state based on the aggregated neighbor messages and its own hidden state. The state update formula is: in: Represents the new hidden state of the node after updating; Indicates the current hidden state of the node; m v Represents an aggregated message.

5. A database cardinality estimation method according to claim 1, characterized in that: The globally aggregating the updated hidden state and feature vector of each node includes: After the message passing and hidden state update are completed, each node in the query graph generates an updated feature vector. The dimension-by-dimension summation method is used for global aggregation. The updated feature vectors of all nodes in the query graph are added dimension by dimension to obtain a graph vector of fixed length. The dimension-by-dimension 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, the dimension of the feature vector of each node is d, so the dimension of the graph vector after global aggregation is also d .

6. A database cardinality estimation method according to claim 1, characterized in that: The acquisition of the estimated cardinality value includes: The graph vector is input into the multi-layer perceptron MLP. h graph After nonlinear mapping of multiple fully connected layers, a cardinality estimate is output; the mapping equation of the multi-layer perceptron MLP is: h (1) =σ(W (1) h graph +b (1) ) h (2) =σ(W (2) h (1) +b (2) ) Where: h graph represents the fixed-dimensional vector obtained after message passing and global aggregation; W (1) and W (2) Represent the weight matrices of the first and second layers respectively; b (1) and b (2) represents the bias vector; σ represents the nonlinear activation function (ReLU); h (1) and h (2) Represents the middle layer after activation; The output layer of the multi-layer perceptron MLP generates a scalar value as the cardinality estimate, whose equation is: Where: W (3) and b (3) Represent the weight and bias of the output layer respectively; Represents the final estimated cardinality.

7. A database cardinality estimation device, characterized in that: include: A construction module is used to obtain table information and column information of a database data table, generate scan operator nodes and join operator nodes according to the table information 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 a feature vector 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 update 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 changed; The cardinality estimation module is used to input the graph vector into the multi-layer perceptron MLP to obtain the cardinality estimation value of the database.

8. An electronic device, characterized in that: include: Memory and processor; The memory is used to store computer programs; The processor is used to implement the steps of a database cardinality estimation method as described in any one of claims 1 to 6 when executing the computer program stored in the memory.

9. 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 claimed in any one of claims 1 to 6.

Images