Deep Learning-Based Distributed Node Cardinality Estimation Method, Apparatus, and Equipment

By encoding and extracting features from the distributed execution plan using a deep learning-based Tree-LSTM network, the problem of insufficient accuracy of traditional cardinality estimation methods in distributed database systems is solved. This enables accurate prediction of the result set size for each distributed physical node, improving the decision quality of the query optimizer and the efficiency of system resource utilization.

CN118410064BActive Publication Date: 2026-11-10WUHAN UNIV
View PDF 0 Cites 0 Cited by

Patent Information

Application Number
CN202410448952.3
Authority / Receiving Office
CN · China
Patent Type
Patents(China)
Current Assignee / Owner
Filing Date
2024-04-15
Publication Date
2026-11-10
Estimated Expiration
2044-04-15

AI Technical Summary

Technical Problem

In distributed database systems, traditional cardinality estimation methods are difficult to accurately reflect the complexity and uncertainty of data distribution, resulting in inaccurate estimates of resource consumption and time in parallel execution environments, which affects the decision quality of the query optimizer and the effective utilization of system resources.

Method used

A deep learning-based approach is adopted, using a Tree-LSTM network to encode and extract features of the distributed execution plan, generating feature vectors of the data distribution. The neural network parameters are updated through a loss function to accurately predict the cardinality of each distributed physical node.

Benefits of technology

It improves the accuracy of distributed node cardinality estimation, enabling the cost model to more accurately assess the cost of parallel execution and data processing, thereby selecting the optimal execution plan and improving query efficiency and resource utilization efficiency.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN118410064B_ABST
    Figure CN118410064B_ABST
Patent Text Reader

Abstract

This invention discloses a method, apparatus, and device for distributed node cardinality estimation based on deep learning, relating to the field of database query optimization. The method includes acquiring a distributed execution plan and encoding table statistics, TableSchema information, and physical distribution information to generate feature vectors for the data distribution; encoding each node of the distributed execution plan and generating feature vectors for physical operator features, table features, column features, join condition features, and selection condition features respectively; integrating the generated feature vectors and inputting them into a Tree-LSTM to predict the cardinality of the distributed execution plan on each distributed physical node; and calculating the loss value between the estimated cardinality and the true cardinality based on a loss function to update the parameters in the neural network. This application can accurately extract key information about the data distribution, providing comprehensive and accurate data support for distributed node cardinality estimation.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] This application relates to the field of database query optimization, specifically to a distributed node cardinality estimation method, apparatus, and device based on deep learning. Background Technology

[0002] Cardinality estimation plays a crucial role in database query optimization, its fundamental purpose being to improve query processing efficiency. By accurately predicting the number of rows returned by each operation node in the query execution plan, cardinality estimation allows the query optimizer to evaluate and compare the resource consumption of different query execution strategies, thereby selecting the optimal query execution plan. An accurate cardinality estimate enables the query optimizer to effectively select indexes, determine the best join order and join operators, and the appropriate data scanning method. Therefore, the accuracy of cardinality estimation directly affects the decision quality of the query optimizer, thus impacting query execution efficiency and the effective utilization of system resources. However, the challenge of cardinality estimation lies in the complexity and uncertainty of data distribution. Real-world data often does not satisfy simple statistical assumptions of independence and uniform distribution, but rather exhibits rich correlations and dependencies. This data skew and correlation make it difficult for statistical cardinality estimation methods to accurately reflect the true data distribution, thus affecting the accuracy of the estimation.

[0003] Furthermore, in distributed database systems, data tables may be partitioned and stored on different physical nodes, and execution plans can be executed in parallel across multiple nodes. However, traditional distributed query optimizers may only predict the overall result set size of the entire data table, rather than the result set size of each distributed physical node. Therefore, distributed cost models lack accuracy in calculating the resource consumption of the execution plan and the cost of parallel execution. This is because, in a parallel execution environment, the data distribution and processing capabilities of different nodes can significantly impact the overall resource consumption and time of query execution, and these parallel factors are not adequately considered in cardinality estimations that only predict the overall result set size. Therefore, how to effectively perform distributed node cardinality estimation has become a pressing problem to be solved. Summary of the Invention

[0004] This application provides a method, apparatus, and device for distributed node cardinality estimation based on deep learning, which can accurately extract key information about data distribution and provide comprehensive and accurate data support for distributed node cardinality estimation.

[0005] In a first aspect, embodiments of this application provide a distributed node cardinality estimation method based on deep learning, the distributed node cardinality estimation method based on deep learning comprising:

[0006] Obtain the distributed execution plan, encode the table's statistics, TableSchema information, and physical distribution information, and generate a feature vector of the data distribution;

[0007] Each node of the distributed execution plan is encoded, and feature vectors are generated for physical operator features, table features, column features, join condition features, and selection condition features, respectively.

[0008] The generated feature vectors are integrated and input into Tree-LSTM to predict the cardinality of the distributed execution plan on each distributed physical node;

[0009] The loss function is used to calculate the loss value between the estimated cardinality and the true cardinality in order to update the parameters in the neural network.

[0010] In conjunction with the first aspect, in one implementation, the step of obtaining the distributed execution plan, encoding the table's statistical information, TableSchema information, and physical distribution information, and generating a feature vector of the data distribution specifically includes:

[0011] Obtain the distributed execution plan, encode the histogram statistics of the table, and generate feature vectors;

[0012] The TableSchema information of the table is encoded, and feature vectors are generated.

[0013] The distribution keys and distribution algorithms of the table are encoded, and feature vectors are generated.

[0014] In conjunction with the first aspect, in one implementation, the encoding of each node of the distributed execution plan, and the generation of feature vectors for physical operator features, table features, column features, join condition features, and selection condition features, specifically includes:

[0015] The distributed execution plan identifies and extracts information from each node, identifies the type of each node, and extracts the relevant table features, column features, selection condition features, join condition features, and physical operator features based on the node type.

[0016] The extracted physical operator features are encoded and feature vectors are generated; the extracted column features are encoded and feature vectors are generated.

[0017] The extracted table features are encoded and feature vectors are generated. The extracted selection condition features and connection condition features are also encoded and feature vectors are generated.

[0018] In conjunction with the first aspect, in one implementation method,

[0019] The column features include the table name associated with the current column and selection predicate information;

[0020] The extracted column features are encoded and feature vectors are generated. In this process, the selection predicate is encoded by assigning a unique numerical code to each type of predicate and encoding the operands on the other end of the predicate.

[0021] In conjunction with the first aspect, in one implementation, the step of integrating the generated feature vectors and inputting them into a Tree-LSTM to predict the cardinality of the distributed execution plan across each distributed physical node specifically includes:

[0022] The generated feature vectors are integrated according to the tree structure of the distributed execution plan to obtain the feature representation of the distributed execution plan;

[0023] The generated feature vectors are input into Tree-LSTM to predict the cardinality of distributed physical nodes in the distributed execution plan.

[0024] In conjunction with the first aspect, in one implementation, the step of calculating the loss value between the estimated cardinality and the true cardinality based on the loss function to update the parameters in the neural network specifically includes:

[0025] The estimated and actual values ​​of the distributed node cardinality, as well as the loss value between the estimated and actual values, are calculated based on the loss function.

[0026] The gradient backpropagation algorithm is used to continuously adjust the weights of the parameters in the neural network in order to minimize the error between the output of the neural network and the actual value.

[0027] Secondly, embodiments of this application provide a deep learning-based distributed node cardinality estimation device, the deep learning-based distributed node cardinality estimation device comprising:

[0028] The acquisition module is used to acquire the distributed execution plan, encode the table's statistical information, TableSchema information, and physical distribution information, and generate a feature vector of the data distribution.

[0029] The generation module is used to encode each node of the distributed execution plan and generate feature vectors for physical operator features, table features, column features, join condition features, and selection condition features, respectively.

[0030] The integration module is used to integrate the generated feature vectors and input them into the Tree-LSTM to predict the cardinality of the distributed execution plan on each distributed physical node;

[0031] The update module is used to calculate the loss value between the estimated cardinality and the true cardinality based on the loss function, so as to update the parameters in the neural network.

[0032] In conjunction with the second aspect, in one implementation, the step of obtaining the distributed execution plan, encoding the table's statistical information, TableSchema information, and physical distribution information, and generating a feature vector of the data distribution specifically includes:

[0033] Obtain the distributed execution plan, encode the histogram statistics of the table, and generate feature vectors;

[0034] The TableSchema information of the table is encoded, and feature vectors are generated.

[0035] The distribution keys and distribution algorithms of the table are encoded, and feature vectors are generated.

[0036] In conjunction with the second aspect, in one implementation, the encoding of each node in the distributed execution plan, and the generation of feature vectors for physical operator features, table features, column features, join condition features, and selection condition features, specifically includes:

[0037] The distributed execution plan identifies and extracts information from each node, identifies the type of each node, and extracts the relevant table features, column features, selection condition features, join condition features, and physical operator features based on the node type.

[0038] The extracted physical operator features are encoded and feature vectors are generated; the extracted column features are encoded and feature vectors are generated.

[0039] The extracted table features are encoded and feature vectors are generated. The extracted selection condition features and connection condition features are also encoded and feature vectors are generated.

[0040] Thirdly, embodiments of this application provide a deep learning-based distributed node cardinality estimation device, which includes a processor, a memory, and a deep learning-based distributed node cardinality estimation program stored in the memory and executable by the processor. When the deep learning-based distributed node cardinality estimation program is executed by the processor, it implements the steps of the deep learning-based distributed node cardinality estimation method described above.

[0041] The beneficial effects of the technical solutions provided in this application include:

[0042] Each node of the execution plan is meticulously encoded and its feature vectors are extracted. A Tree-LSTM network is used to naturally capture the hierarchy and tree structure of the query plan through its tree-like network structure, which is extremely helpful for understanding and evaluating the nesting relationships and dependencies in the query execution plan. Through reasonable encoding methods, feature vectors of table schema information, table partition information, and statistical information are generated, which can more accurately extract key information about data distribution and provide comprehensive and accurate data support for distributed node cardinality estimation. A distributed node cardinality estimation model is proposed. By predicting the size of the result set of the execution plan on each physical node of the distributed database, the cost model can more accurately evaluate the cost of parallel execution and data processing cost, and select a better execution plan. Attached Figure Description

[0043] Figure 1 This is a flowchart illustrating the distributed node cardinality estimation method based on deep learning proposed in this application.

[0044] Figure 2 This is a schematic diagram of the functional modules of the deep learning-based distributed node cardinality estimation device of this application;

[0045] Figure 3 This is a schematic diagram of the hardware structure of the deep learning-based distributed node cardinality estimation device of this application. Detailed Implementation

[0046] To enable those skilled in the art to better understand the present application, the technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present application, and not all embodiments. Based on the embodiments in the present application, all other embodiments obtained by those of ordinary skill in the art without creative effort are within the scope of protection of the present application.

[0047] First, some of the technical terms used in this application will be explained to help those skilled in the art understand this application.

[0048] Tree-LSTM (Tree-LSTM): Tree-LSTM is a deep learning model that extends LSTM (Long Short-Term Memory) to better handle data with a tree-like hierarchical structure. This network model allows information to flow along a tree structure, not just a simple linear sequence, making it particularly suitable for representing execution plans. The advantage of Tree-LSTM is that it can process time-series data from the bottom up, while capturing long-distance dependencies and complex data structure dependencies.

[0049] Distributed Databases and Data Sharding: With the explosive growth of data volume and the accelerating demand for real-time processing, distributed databases have become crucial in supporting high scalability, availability, and fault tolerance. Distributed databases can handle higher transaction loads and query volumes, making them ideal for big data and high-concurrency applications. Data sharding, a key aspect of distributed databases, involves partitioning data across multiple nodes to optimize query performance and resource utilization. It allows data to be physically distributed across different servers, thus distributing query and transaction loads across multiple nodes, improving system processing power and response speed.

[0050] To make the objectives, technical solutions, and advantages of this application clearer, the embodiments of this application will be described in further detail below with reference to the accompanying drawings.

[0051] A database represents a structured collection of data that is organized, described, and stored long-term in a computer system to support shared access. By employing a carefully designed data model, databases aim to minimize data redundancy while maximizing data independence and system scalability.

[0052] Query operations are a key function in a Database Management System (DBMS), enabling users to retrieve information stored in the database and obtain the desired result set by executing query statements. Query performance directly impacts the overall efficiency of the database system; therefore, implementing query optimization—that is, reducing query response time by constructing the most cost-effective execution plan—is a crucial strategy for improving database performance.

[0053] Cardinality estimation is a crucial step in database query optimization. Its goal is to predict the size of the result set returned by a database operation (especially a query), which is essentially the number of distinct elements in the result set (cardinality). Accurate cardinality estimation is essential for a database management system (DBMS) to select the most efficient query execution plan. In query optimization, the objective of selecting the optimal execution plan is to determine which plan has the lowest cost using a cost model. Therefore, cardinality estimation directly impacts the accuracy of the cost model, and consequently, the final execution plan selection.

[0054] The challenge of cardinality estimation lies in the complexity and uncertainty of data distribution. Real-world data often does not satisfy simple statistical assumptions of independence and uniform distribution, but rather exhibits rich correlations and dependencies. This data skewness and correlation make it difficult for simple statistical cardinality estimation methods to accurately reflect the true data distribution, thus affecting the accuracy of the estimation.

[0055] Furthermore, in distributed database systems, to improve data access efficiency and system scalability, data tables are typically partitioned (or divided) and distributed across different physical nodes. Partitioned tables allow query plans to be executed in parallel across multiple nodes, theoretically significantly improving query processing speed. However, the efficiency of parallel execution depends not only on the execution plan itself but also on the data distribution on each node and the node's processing capacity. Traditional distributed query optimizers, when exploring execution plans, only predict the size of the entire query result set using cardinality estimation, without refining to the result set size for each node. This approach ignores the potential uneven distribution of data across different nodes and the differences in processing capabilities between nodes.

[0056] Because traditional optimizers primarily focus on the overall result set size in their cardinality estimation, cost models may fail to accurately assess the impact of uneven data distribution and varying processing capabilities across different nodes on resource consumption and execution time in a parallel execution environment. In such cases, even with parallel execution, inaccurate optimizer estimations may prevent the full utilization of the distributed environment's advantages, leading to uneven resource utilization and prolonged query response times.

[0057] To improve the query performance and resource utilization efficiency of distributed database systems, it is necessary to refine the cardinality estimation technique of the distributed query optimizer, enabling it to more accurately predict the result set size of each node. This allows the cost model to more precisely assess the cost of parallel execution, data processing costs, and potential performance improvements. Therefore, we propose a machine learning-based approach to better understand and utilize the differences in data distribution and node capabilities in a distributed environment.

[0058] Based on the above problems, the first aspect of this application provides a distributed node cardinality estimation method based on deep learning, namely a distributed node cardinality estimation method based on Tree-LSTM network. This method can more accurately predict the size of the result set of database operations on each physical node of the distributed database, thereby providing more detailed cardinality information to the distributed cost model to predict the cost of the execution plan.

[0059] To address the cardinality estimation methods used in traditional distributed databases, Tree-LSTM is introduced to learn the tree structure dependencies and node characteristic information of the distributed execution plan. More database statistics and table distribution information on physical nodes are also incorporated to effectively estimate the cardinality of the distributed execution plan on each distributed node.

[0060] This application employs a Tree-LSTM-based distributed node cardinality estimation method, which includes a training phase and a validation phase. In the training phase, training set data is acquired, and data distribution information such as table statistics, table schema information, partition keys, and partitioning algorithms are encoded to generate feature vectors representing the data distribution. Then, the input distributed execution plan is processed, encoding the tree structure and individual nodes of the execution plan. These feature vectors are then integrated and input into a tree convolutional neural network to predict the cardinality of the distributed execution plan at each distributed node. Finally, the loss between the estimated cardinality and the true cardinality is calculated based on a set loss function to update the parameters in the neural network. In the validation phase, the trained model is applied to a new distributed execution plan dataset to estimate the cardinality of the test set distributed execution plans and to evaluate the accuracy of its distributed node cardinality estimation.

[0061] In one embodiment, reference is made to Figure 1 , Figure 1 This is a flowchart illustrating the distributed node cardinality estimation method based on deep learning proposed in this application. Figure 1 As shown, deep learning-based distributed node cardinality estimation methods include:

[0062] S1: Obtain the distributed execution plan, encode the table's statistical information, TableSchema information, and physical distribution information, and generate a feature vector of the data distribution;

[0063] Furthermore, in one embodiment, a distributed execution plan is obtained, and the statistical information, TableSchema information, and physical distribution information of the table are encoded to generate a feature vector of the data distribution, specifically including:

[0064] S101: Obtain the distributed execution plan, encode the histogram statistics of the table, and generate feature vectors; after obtaining the distributed execution plan, the table statistics, TableSchema information, and physical distribution information can be obtained from the distributed database.

[0065] Specifically, the histogram statistics of the table are encoded. Assuming each column has n buckets, a vector F(stat) of size 3×n×k is constructed, and each bucket corresponds to a vector b of length 3. i =(max i ′,min i ′,count i ′), max i min i These represent the maximum boundary values ​​of the bucket, respectively. iThe maximum value can be obtained by normalization. i ′:

[0066]

[0067] Here, max and min are the maximum and minimum values ​​in the entire dataset, respectively. Similarly, for min... i Normalization will yield the min value. i In this way, all boundary values ​​will be scaled to between 0 and 1. i ' represents the number of records in this bucket (count) i The result obtained after logarithmic transformation:

[0068] count i ′=log(count i +1)

[0069] The three-dimensional vector (b1, b2, ..., b) of n buckets in a column. n The combination yields a vector h of size 3×n. ci ,F(h ci The vector representing the histogram statistics of the i-th column is then used to calculate the vector of the k columns (h). c1 ,h c2 ,…,h cn The histogram statistical information vector F(stat) of the table is obtained by combining the data.

[0070] S102: Encode the TableSchema (database name) information of the table and generate feature vectors;

[0071] Specifically, the TableSchema information of a table is encoded as follows: For a table t with k columns, a vector F(st) of shape 3×k is constructed. In this vector, the first row is responsible for encoding the index information, the second row is responsible for encoding the row number information, and the third row is responsible for encoding the number of different values.

[0072] For each column in table t, a binary bit indicates whether an index has been created for that column: if an index has been created, the bit is set to 1; otherwise, it is set to 0. For the number of rows (row_count) and the number of unique values ​​(unique_values), since these features can vary greatly in magnitude, a logarithmic transformation is used to reduce the difference in magnitude: x′=log(1+x), where x represents the number of rows or unique values ​​in each column, and x′ is the result after the logarithmic transformation. This yields the TableSchema information vector F(ts) for each column. ci ), and the vector F(ts) of the k columns ciThe combination yields the TableSchema information vector F(st) of the table.

[0073] S103: Encode the distribution key and distribution algorithm of the table, and generate feature vectors.

[0074] Similarly, for encoding the sharding information, a vector F(p) of size k is constructed. For each column in the table, there is a 1-bit sharding presence flag, which indicates whether the column has been sharded (0 for no sharding, 1 for sharding). Likewise, 1 bit is used to indicate the sharding type: 0 for hash partitioning, 1 for range partitioning. For hash sharding, we use 8 bits to represent the modulo value, with a maximum acceptable modulo value of 255. For range sharding, we use 8 bits to represent the number of ranges, which can be divided into a maximum of 255 ranges.

[0075] For example: If no sharding is established for this column, the feature vector bits are: 0|0|00000000 (sharding existence flag|sharding type|sharding parameters).

[0076] If a hash partition is established on this column and the modulus is 5, then the feature vector can be: 1|0|00000101.

[0077] If the column is divided into three ranges, the feature vector could be: 1|1|00000011.

[0078] The k-column fragmented information vector F(p) ci These are combined to obtain the slice vector code F(p).

[0079] By taking the steps described above, we can generate feature vectors for the table's TableSchema information, sharding information, and statistical information. This allows us to extract key information about the data distribution more accurately, providing accurate and comprehensive data features for subsequent steps.

[0080] S2: Encode each node of the distributed execution plan and generate feature vectors for physical operator features, table features, column features, join condition features, and selection condition features respectively;

[0081] Furthermore, in one embodiment, each node of the distributed execution plan is encoded, and feature vectors are generated for physical operator features, table features, column features, join condition features, and selection condition features, specifically including:

[0082] S201: Identify and extract information from each node of the distributed execution plan, identify the type of each node, and extract relevant table features, column features, selection condition features, join condition features, and physical operator features based on the node type. Column features include the table name associated with the current column and selection predicate information. Encode the extracted column features and generate feature vectors. Specifically, encode the selection predicates by assigning a unique numerical code to each type of predicate (e.g., >, <, =, etc.) and encode the operands on the other end of the predicate.

[0083] Specifically, different nodes in a distributed execution plan have different types, and the information inside each node is also different. The primary function of a Scan node is to read the contents of a table and filter it based on selection criteria. Internal information includes the table name, selection criteria, and the Scan physical operator. The Scan physical operator indicates the scanning method used, such as Index Scan or Sequential Scan. A Join node indicates that it is a join node, and its main function is to join two tables based on join criteria. Internal information includes the join criteria and the Join physical operator. The Join physical operator indicates the join method used, such as Broadcast Hash Join, Broadcast Nexted Loop Join, Redistributed Hash Join, and Redistributed Sort-Merge Join. The primary function of a Sort node is to sort tables by the values ​​of a specific column. Internal information includes the table and column names to be sorted, as well as the Sort physical operator. The Sort physical operator indicates the sorting method used, such as Hash Sort or Merge Sort. Sort); The main function of an aggregation node is to perform aggregation operations based on a certain column. The internal information of the node includes the table and column name on which the aggregation is based, as well as the aggregation physical operator. Among them, the aggregation physical operator represents which aggregation method the node uses, such as hash aggregation or plain aggregation.

[0084] S202: Encode the extracted physical operator features and generate feature vectors; encode the extracted column features and generate feature vectors.

[0085] Specifically, one-hot encoding is used for physical operators, with each physical operator occupying one bit.

[0086] The encoding of physical operators is shown in Table 1 below.

[0087] Table 1

[0088] Physical operators (Operation) One-hot encoding Index Scan 0000000001 Seq Scan 0000000010 Broadcast Hash Join 0000000100 Broadcast Nexted Loop Join 0000001000 Redistributed Hash Join 0000010000 Redistributed Sort-Merge Join 0000100000 Hash Sort 0001000000 Merge Sort 0010000000 Hash Aggregation 0100000000 Plain Aggregation 1000000000

[0089] The encoding F(Operation) of the physical operator is obtained through the above method.

[0090] Furthermore, the extracted column features are encoded, and feature vectors are generated. Specifically, a selection predicate is a triple of (column name; operator; operand). Taking the predicate "mi.id = 2080" as an example, mi.id is the column name, the operator is "=", and the operand is "2080". Encoding the selection predicate requires encoding the column name, operator, and operand separately.

[0091] The column names and operators are encoded using one-hot encoding. Taking the IMDB dataset as an example, one-hot encoding is used to encode all column names in all tables. Each column is assigned a unique position, where the corresponding position is 1, and all other positions are 0.

[0092] The coding for column names is shown in Table 2 below:

[0093] Table 2

[0094]

[0095]

[0096] Similarly, one-hot encoding is used for operators, with each operator occupying one bit.

[0097] The encoding of operators is shown in Table 3 below.

[0098] Table 3

[0099] Operator One-hot encoding = 000001 > 000010 < 000100 != 001000 >= 010000 <= 100000

[0100] For the encoding of operand num, normalization is used to adjust the data to between 0 and 1:

[0101]

[0102] max and min are the maximum and minimum values ​​in the column, respectively.

[0103] Taking the predicate "mi.id>2000" as an example, assuming the maximum value of mi.id is 3000 and the minimum value is 1000, then the encoding of "mi.id>2000" is:

[0104] Column name | Operator | Operand 0000...000001 000010 0.5

[0106] The encoding F(predicate) of the selection predicate is obtained through the above method.

[0107] S203: Encode the extracted table features and generate feature vectors; encode the extracted selection condition features and connection condition features and generate feature vectors.

[0108] The information in the tables is also encoded using one-hot encoding, as shown in Table 4 below, with each table occupying one bit of the one-hot vector.

[0109] Table 4

[0110] Table name (table) One-hot encoding aka_name 000...001 aka_title 000...010 cast_info 000...100 ... ... movie_companies 001...000 movie_info 010...000 movie_keyword 100...000

[0111] The encoding F(t) of the table name is obtained through the above method.

[0112] The join condition is a triple of (column name; =; column name), so the encoding of the join condition is simply a combination of these triples. For example, in the predicate "mi.id = mc.movie_id", the encoding of the mi.id column is 0000...000001, the encoding of the "=" operator is 000001, and the encoding of mc.movie_id is 0001...000000. Therefore, the encoding of the predicate "mi.id = mc.movie_id" is: 0000...000001 000001 0001....000000

[0114] The encoding of the join condition F (join) is obtained through the above method.

[0115] The above steps are to successfully transform the core content of the distributed execution plan into feature vectors during the data preprocessing stage, meeting the data requirements of machine learning models, and comprehensively and accurately capturing key information in the physical plan. This provides comprehensive and accurate data support for distributed node cardinality estimation.

[0116] S3: Integrate the generated feature vectors and input them into the Tree-LSTM to predict the cardinality of the distributed execution plan across each distributed physical node. This involves integrating the generated feature vectors according to node type to obtain the feature vector for each node. The vector for each node is then input into the Tree-LSTM according to the tree structure of the distributed connection plan to predict the cardinality of the distributed execution plan across each distributed node. Finally, the integrated feature vector of the execution plan and the feature vector of the data physical distribution are input into the Tree-LSTM network, outputting an estimate of the cardinality of each distributed node.

[0117] Furthermore, in one embodiment, the generated feature vectors are integrated and input into a Tree-LSTM to predict the cardinality of the distributed execution plan on each distributed physical node, specifically including:

[0118] S301: Integrate the generated feature vectors according to the tree structure of the distributed execution plan to obtain the feature representation of the distributed execution plan;

[0119] The feature vectors generated above are then integrated according to node type to obtain the feature vector of each node.

[0120] Specifically, the encoded information of each node is combined according to the type of each node, and then the Tree-LSTM network is organized according to the tree structure of the execution plan.

[0121] For the Scan node in the execution plan, the features include: the predicate vector F(predicate), the physical operator vector F(Operation), the table vector F(t), and the histogram statistical information vector F(h) of the predicate column. c ), Select the TableSchema information vector F(ts) of the predicate column c The ReLU activation function is used to process these encodings and transform them into feature vectors.

[0122] R(predicate)=ReLU(F(predicate)×w1+o1)

[0123] R(Opertion)=ReLU(F(Opertion)×w2+o2)

[0124] R(t) = ReLU(F(t) × w³ + o³)

[0125] R(h c )=ReLU(F(h c )×w4+o4)

[0126] R(ts c ) = ReLU(F(ts)c )×w5+o5)

[0127] Among them, w1, w2, w3, w4, w5 and o1, o2, o3, o4, o5 are learnable parameters.

[0128] These five vectors are concatenated to form the overall vector of the Scan node.

[0129] F(scan)=[R(predicate); R(Opertion); R(t); R(h c ); R(ts) c )]

[0130] The representation R(scan) of the scan node is obtained using a fully connected layer.

[0131] R(scan)=Sigmod(W1×F(scan)+b1)

[0132] Where W1 is the weight matrix of the fully connected layer, and b1 is the bias vector.

[0133] Similarly, the Join node in the execution plan contains the following features: the join condition vector F(join), the physical operator vector F(Operation), and the sharding information vector F(p) of the two join condition columns. c1 ) and F(p c2 The ReLU activation function is used to process these encodings and transform them into feature vectors.

[0134] R(join)=ReLU(F(join)×w6+o6)

[0135] R(Opertion)=ReLU(F(Opertion)×w7+o7)

[0136] R(p c1 ) = ReLU(F(p c1 )×w8+o8)

[0137] R(p c2 )=ReLU(F(p c2 )×w9+o9)

[0138] Among them, w6, w7, w8, w9 and o6, o7, o8, o9 are learnable parameters.

[0139] These five vectors are concatenated to form the vector of the join node.

[0140] F(join)=[R(join); R(Opertion); R(p c1 ); R(p c2 )]

[0141] The representation R(join) of the join node is obtained using a fully connected layer.

[0142] R(join)=Sigmod(W2×F(join)+b2)

[0143] Where W2 is the weight matrix of the fully connected layer, and b2 is the bias vector.

[0144] S302: Input the generated feature vector into Tree-LSTM to predict the cardinality of distributed physical nodes in the distributed execution plan.

[0145] In the Tree-LSTM model, each node in the execution plan corresponds to an LSTM unit, and each LSTM unit has three inputs: the node's own vector R(node), the output vector of its left child node, and the output vector of its left child node. Output vector of the right child node The output is (G) t ,R t The vector of a node itself is the same as the vector shown in step S301, such as R(scan) for the scan node and R(join) for the join node. For leaf nodes of the execution plan tree, since they have no child nodes, the vectors from the left and right child nodes in the input are replaced by zero vectors.

[0146] With this organizational approach, when processing tree-structured execution plans, by integrating LSTM units into the nodes of the tree structure, each node can effectively combine information from its left and right subtrees, as well as information from the node itself, to update its state, thereby better predicting distributed cardinality.

[0147] By passing information from the bottom up, the result output by the root node (G) is... t ,R t The cardinality prediction for each distributed physical node is obtained through a fully connected layer:

[0148] [card1′,card2′,…,card n ′]=tanh([G t ;R t ]×W3+b3)

[0149] Where tanh() is the activation function, W3 is the weight matrix, and b3 is the bias vector.

[0150] S4: Calculate the loss value between the estimated cardinality and the true cardinality based on the loss function to update the parameters in the neural network.

[0151] Furthermore, in one embodiment, the loss value between the estimated cardinality and the true cardinality is calculated based on the loss function to update the parameters in the neural network, specifically including:

[0152] S401: Calculate the estimated and actual values ​​of the distributed node cardinality based on the loss function, as well as the loss value between the estimated and actual values;

[0153] Define the loss function as:

[0154]

[0155] Among them, card i The true value of the cardinality of the i-th physical node, card i ′ is the predicted value of the base number of the i-th physical node.

[0156] S402: The weights of the parameters in the neural network are continuously adjusted through the gradient backpropagation algorithm to minimize the error between the output of the neural network and the actual value.

[0157] During model training, the model parameters are continuously updated iteratively to minimize the MSE loss, thereby improving the accuracy of predictions.

[0158] Repeat the above steps until the loss value reaches a satisfactory level. The trained model is the optimized distributed node cardinality estimation model.

[0159] This model can be used in the query optimizer of a distributed database to predict the size of the result set of each query operation on different physical nodes. This allows the cost model to more accurately evaluate the cost of parallel execution, data processing cost, and potential performance improvement, thereby selecting the distributed execution plan with the lowest cost and improving query efficiency.

[0160] Secondly, embodiments of this application also provide a distributed node cardinality estimation device based on deep learning.

[0161] In one embodiment, reference is made to Figure 2 , Figure 2 This is a schematic diagram of the functional modules of the deep learning-based distributed node cardinality estimation device of this application. Figure 2 As shown, the deep learning-based distributed node cardinality estimation device includes an acquisition module, a generation module, an integration module, and an update module.

[0162] The acquisition module is used to acquire the distributed execution plan and encode the table's statistical information, TableSchema information, and physical distribution information, and generate feature vectors for the data distribution. The generation module is used to encode each node of the distributed execution plan and generate feature vectors for physical operator features, table features, column features, join condition features, and selection condition features, respectively. The integration module is used to integrate the generated feature vectors and input them into the Tree-LSTM to predict the cardinality of the distributed execution plan on each distributed physical node. The update module is used to calculate the loss value between the estimated cardinality and the true cardinality based on the loss function to update the parameters in the neural network.

[0163] In this application, a distributed execution plan is obtained, and the statistical information, TableSchema information, and physical distribution information of the tables are encoded to generate a feature vector of the data distribution, specifically including:

[0164] Obtain the distributed execution plan, encode the histogram statistics of the table, and generate feature vectors;

[0165] The TableSchema information of the table is encoded, and feature vectors are generated.

[0166] The distribution keys and distribution algorithms of the table are encoded, and feature vectors are generated.

[0167] In this application, each node of the distributed execution plan is encoded, and feature vectors are generated for physical operator features, table features, column features, join condition features, and selection condition features, specifically including:

[0168] The distributed execution plan identifies and extracts information from each node, identifies the type of each node, and extracts the relevant table features, column features, selection condition features, join condition features, and physical operator features based on the node type.

[0169] The extracted physical operator features are encoded and feature vectors are generated; the extracted column features are encoded and feature vectors are generated.

[0170] The extracted table features are encoded and feature vectors are generated. The extracted selection condition features and connection condition features are also encoded and feature vectors are generated.

[0171] The functions of each module in the aforementioned deep learning-based distributed node cardinality estimation device correspond to the steps in the aforementioned deep learning-based distributed node cardinality estimation method embodiment, and their functions and implementation processes will not be described in detail here.

[0172] Thirdly, embodiments of this application provide a distributed node cardinality estimation device based on deep learning. The distributed node cardinality estimation device based on deep learning can be a device with data processing capabilities, such as a personal computer (PC), a laptop, or a server.

[0173] Reference Figure 3 , Figure 3 This is a schematic diagram of the hardware structure of a deep learning-based distributed node cardinality estimation device involved in the embodiments of this application. In the embodiments of this application, the deep learning-based distributed node cardinality estimation device may include a processor, a memory, a communication interface, and a communication bus.

[0174] The communication bus can be of any type and is used to interconnect the processor, memory, and communication interface.

[0175] The communication interface includes input / output (I / O) interfaces, physical interfaces, and logical interfaces used for interconnecting devices within the deep learning-based distributed node cardinality estimation device, as well as interfaces used for interconnecting the deep learning-based distributed node cardinality estimation device with other devices (such as other computing devices or user equipment). Physical interfaces can be Ethernet interfaces, fiber optic interfaces, ATM interfaces, etc.; user equipment can be displays, keyboards, etc.

[0176] Memory can be various types of storage media, such as random access memory (RAM), read-only memory (ROM), non-volatile RAM (NVRAM), flash memory, optical storage, hard disk, programmable ROM (PROM), erasable PROM (EPROM), electrically erasable PROM (EEPROM), etc.

[0177] The processor can be a general-purpose processor, which can call a deep learning-based distributed node cardinality estimation program stored in memory and execute the deep learning-based distributed node cardinality estimation method provided in the embodiments of this application. For example, the general-purpose processor can be a central processing unit (CPU). The method executed when the deep learning-based distributed node cardinality estimation program is called can be referred to the various embodiments of the deep learning-based distributed node cardinality estimation method of this application, and will not be repeated here.

[0178] Those skilled in the art will understand that Figure 3 The hardware structure shown does not constitute a limitation of this application and may include more or fewer components than shown, or combine certain components, or have different component arrangements.

[0179] It should be noted that the sequence numbers of the embodiments in this application are for descriptive purposes only and do not represent the superiority or inferiority of the embodiments.

[0180] The terms "comprising" and "having," and any variations thereof, in the specification, claims, and accompanying drawings of this application are intended to cover non-exclusive inclusion. For example, a process, method, system, product, or apparatus that includes a series of steps or units is not limited to the listed steps or units, but may optionally include steps or units not listed, or may optionally include other steps or units inherent to such process, method, product, or apparatus. The terms "first," "second," and "third," etc., are used to distinguish different objects, etc., and do not indicate a sequence, nor do they limit "first," "second," and "third" to different types.

[0181] In the description of the embodiments of this application, terms such as "exemplary," "for example," or "for instance" are used to indicate examples, illustrations, or explanations. Any embodiment or design described as "exemplary," "for example," or "for instance" in the embodiments of this application should not be construed as being more preferred or advantageous than other embodiments or designs. Specifically, the use of terms such as "exemplary," "for example," or "for instance" is intended to present the relevant concepts in a concrete manner.

[0182] In the description of the embodiments of this application, unless otherwise stated, " / " means "or". For example, A / B can mean A or B. The "and / or" in the text is merely a description of the relationship between related objects, indicating that there can be three relationships. For example, A and / or B can mean: A exists alone, A and B exist simultaneously, and B exists alone. In addition, in the description of the embodiments of this application, "multiple" means two or more.

[0183] In some processes described in the embodiments of this application, multiple operations or steps are included in a specific order. However, it should be understood that these operations or steps may not be executed in the order they appear in the embodiments of this application, or they may be executed in parallel. The sequence number of the operation is only used to distinguish different operations, and the sequence number itself does not represent any execution order. In addition, these processes may include more or fewer operations, and these operations or steps may be executed sequentially or in parallel, and these operations or steps may be combined.

[0184] Through the above description of the embodiments, those skilled in the art can clearly understand that the methods of the above embodiments can be implemented by means of software plus necessary general-purpose hardware platforms. Of course, they can also be implemented by hardware, but in many cases the former is a better implementation method. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk) as described above, and includes several instructions to cause a terminal device to execute the methods described in the various embodiments of this application.

[0185] The above are merely preferred embodiments of this application and do not limit the patent scope of this application. Any equivalent structural or procedural transformations made using the content of this application's specification and drawings, or direct or indirect applications in other related technical fields, are similarly included within the patent protection scope of this application.

Claims

1. A distributed node cardinality estimation method based on deep learning, characterized in that, The deep learning-based distributed node cardinality estimation method includes: Obtain the distributed execution plan, encode the table's statistics, TableSchema information, and physical distribution information, and generate a feature vector of the data distribution; Each node of the distributed execution plan is encoded, and feature vectors are generated for physical operator features, table features, column features, join condition features, and selection condition features, respectively. The generated feature vectors are integrated and input into Tree-LSTM to predict the cardinality of the distributed execution plan on each distributed physical node; The loss value between the estimated cardinality and the true cardinality is calculated based on the loss function to update the parameters in the neural network; The process of obtaining the distributed execution plan, encoding the table's statistical information, TableSchema information, and physical distribution information, and generating a feature vector for the data distribution specifically includes: Obtain the distributed execution plan, encode the histogram statistics of the table, and generate feature vectors; The TableSchema information of the table is encoded, and feature vectors are generated. The distribution keys and distribution algorithms of the table are encoded, and feature vectors are generated. Specifically, the process of integrating the generated feature vectors and inputting them into a Tree-LSTM to predict the cardinality of the distributed execution plan on each distributed physical node includes: The generated feature vectors are integrated according to the tree structure of the distributed execution plan to obtain the feature representation of the distributed execution plan; The generated feature vectors are input into Tree-LSTM to predict the cardinality of distributed physical nodes in the distributed execution plan; Specifically, the step of calculating the loss value between the estimated cardinality and the true cardinality based on the loss function to update the parameters in the neural network includes: The estimated and actual values ​​of the distributed node cardinality, as well as the loss value between the estimated and actual values, are calculated based on the loss function. The gradient backpropagation algorithm is used to continuously adjust the weights of the parameters in the neural network in order to minimize the error between the output of the neural network and the actual value.

2. The distributed node cardinality estimation method based on deep learning as described in claim 1, characterized in that, The process of encoding each node of the distributed execution plan and generating feature vectors for physical operator features, table features, column features, join condition features, and selection condition features specifically includes: The distributed execution plan identifies and extracts information from each node, identifies the type of each node, and extracts the relevant table features, column features, selection condition features, join condition features, and physical operator features based on the node type. The extracted physical operator features are encoded and feature vectors are generated; the extracted column features are encoded and feature vectors are generated. The extracted table features are encoded and feature vectors are generated. The extracted selection condition features and connection condition features are also encoded and feature vectors are generated.

3. The distributed node cardinality estimation method based on deep learning as described in claim 2, characterized in that: The column features include the table name associated with the current column and selection predicate information; The extracted column features are encoded and feature vectors are generated. In this process, the selection predicate is encoded by assigning a unique numerical code to each type of predicate and encoding the operands on the other end of the predicate.

4. A distributed node cardinality estimation device based on deep learning, characterized in that, The deep learning-based distributed node cardinality estimation device includes: The acquisition module is used to acquire the distributed execution plan, encode the table's statistical information, TableSchema information, and physical distribution information, and generate a feature vector of the data distribution. The generation module is used to encode each node of the distributed execution plan and generate feature vectors for physical operator features, table features, column features, join condition features and selection condition features respectively. The integration module is used to integrate the generated feature vectors and input them into the Tree-LSTM to predict the cardinality of the distributed execution plan on each distributed physical node. The update module is used to calculate the loss value between the estimated cardinality and the true cardinality based on the loss function, so as to update the parameters in the neural network; The process of obtaining the distributed execution plan, encoding the table's statistical information, TableSchema information, and physical distribution information, and generating a feature vector for the data distribution specifically includes: Obtain the distributed execution plan, encode the histogram statistics of the table, and generate feature vectors; The TableSchema information of the table is encoded, and feature vectors are generated. The distribution keys and distribution algorithms of the table are encoded, and feature vectors are generated. Specifically, the process of integrating the generated feature vectors and inputting them into a Tree-LSTM to predict the cardinality of the distributed execution plan on each distributed physical node includes: The generated feature vectors are integrated according to the tree structure of the distributed execution plan to obtain the feature representation of the distributed execution plan; The generated feature vectors are input into Tree-LSTM to predict the cardinality of distributed physical nodes in the distributed execution plan; Specifically, the step of calculating the loss value between the estimated cardinality and the true cardinality based on the loss function to update the parameters in the neural network includes: The estimated and actual values ​​of the distributed node cardinality, as well as the loss value between the estimated and actual values, are calculated based on the loss function. The gradient backpropagation algorithm is used to continuously adjust the weights of the parameters in the neural network in order to minimize the error between the output of the neural network and the actual value.

5. The distributed node cardinality estimation device based on deep learning as described in claim 4, characterized in that, The process of encoding each node of the distributed execution plan and generating feature vectors for physical operator features, table features, column features, join condition features, and selection condition features specifically includes: The distributed execution plan identifies and extracts information from each node, identifies the type of each node, and extracts the relevant table features, column features, selection condition features, join condition features, and physical operator features based on the node type. The extracted physical operator features are encoded and feature vectors are generated; the extracted column features are encoded and feature vectors are generated. The extracted table features are encoded and feature vectors are generated. The extracted selection condition features and connection condition features are also encoded and feature vectors are generated.

6. A distributed node cardinality estimation device based on deep learning, characterized in that, The deep learning-based distributed node cardinality estimation device includes a processor, a memory, and a deep learning-based distributed node cardinality estimation program stored in the memory and executable by the processor, wherein when the deep learning-based distributed node cardinality estimation program is executed by the processor, it implements the steps of the deep learning-based distributed node cardinality estimation method as described in any one of claims 1 to 3.