Cost estimation model construction method, cost estimation system and estimation method
By constructing a cost estimation model, using single-hot encoding and word embedding encoding to process the feature information of the query plan, and through the combination of embedding layer, presentation layer and fully connected units, the problem of inaccurate cost estimation in the prior art is solved, and the prediction accuracy and coding efficiency of the query plan in the computing power network scenario are improved.
Patent Information
- Application Number
- CN202411613241.3
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-11-13
- Publication Date
- 2025-04-04
AI Technical Summary
The existing cost estimation method is difficult to accurately predict the execution time of the query plan under the cross-domain distributed system architecture of computing power network. Traditional methods are difficult to determine the cost factor due to inconsistent hardware configuration and complexity of the network environment. However, machine learning-based methods do not fully consider the complexity of operator execution in different data centers, resulting in insufficient feature information and inaccurate prediction results.
By obtaining the historical query plan, the training data set is constructed, and the operator type information, operator position information, operator metadata information, operator sample bitmap information and predicate information are encoded. The initial model includes an embedding layer, a presentation layer and a fully connected unit. The model parameters are updated through multiple rounds of iterative training to obtain the cost estimation model.
The prediction accuracy of query plans in computing power network scenarios is improved. By additionally extracting the features of operator position information and introducing word embedding encoding, the representation ability and coding efficiency of the cost estimation model are enhanced, and the use of computing resources is reduced.
Smart Images
Figure CN119759946A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the field of computer technology, specifically to cost estimation technology in the field of computer technology, and more specifically to a cost estimation model construction method, a cost estimation system, and an estimation method. Background Art
[0002] The query plan cost estimation method is used to predict the execution time of the query plan. The database query optimizer generates multiple candidate query plans for the SQL (Structured Query Language) statement to be executed, and selects the query plan with the shortest execution time from the multiple candidate query plans based on the cost estimation results of the query plan.
[0003] There are two mainstream cost estimation methods. One is the traditional cost estimation method: the statistical information collected by the database query optimizer (such as the number of rows in the table, the selectivity of the index, etc.) is used to estimate the number of I / O operations, CPU computing, memory usage, and possible network transmission costs required for each query plan, and the various resource consumptions are converted into time costs to predict the execution time of each query plan. The other is a cost estimation method based on machine learning: first collect the execution history records of different queries in the database, where the execution history records include features such as query statements, database structure, index information, query plans, and actual execution time; then select a suitable machine learning model based on the collected data, such as random forest or gradient boosting tree, and train the machine learning model with the collected data so that the model learns the relationship between different query features and execution time; finally, integrate the trained machine learning model into the database query optimizer to predict the execution time of the query plan.
[0004] Although existing cost estimation methods can predict the execution time of query plans, they are not without flaws. Traditional cost estimation methods are based on the design of single-machine database system architecture. In the cross-domain distributed system architecture of the computing power network, due to the inconsistency of data center hardware configuration and the complexity of the network environment, the cost factor is difficult to determine, resulting in the inability of traditional cost estimation methods to accurately predict the execution time of query plans. In contrast, although the cost estimation method based on machine learning is suitable for the cross-domain distributed system architecture of the computing power network, this method does not fully consider the complexity of the execution of operators in different data centers in the computing power network scenario, resulting in insufficient extracted feature information, which in turn affects the accuracy of the prediction results.
[0005] It should be noted that this background technology is only used to introduce the relevant information of the present invention to help understand the technical solution of the present invention, but it does not mean that the relevant information is necessarily the prior art. If there is no evidence that the relevant information has been disclosed before the application date of the present invention, the relevant information shall not be regarded as the prior art. Summary of the invention
[0006] Therefore, the purpose of the present invention is to overcome the defects of the above-mentioned prior art and provide a cost estimation model construction method, a query plan cost estimation system and a query plan cost estimation method.
[0007] The objectives of the present invention are achieved through the following technical solutions.
[0008] According to a first aspect of the present invention, a method for constructing a cost estimation model is provided, the method comprising: step S1, obtaining executed historical query plans to construct a training data set, the training data set comprising multiple historical query plans and their corresponding labels, the label of each historical query plan being the actual execution time of the historical query plan; wherein each historical query plan comprises operator type information, operator position information, operator metadata information, operator sample bitmap information and predicate information; step S2, encoding the operator type information, operator position information, operator metadata information and operator sample bitmap information in the historical query plan using a one-hot encoding method to obtain an operator type information vector, an operator position information vector, an operator metadata information vector and an operator sample bitmap information vector, and encoding the predicate information in the historical query plan using a word embedding encoding method to obtain predicate information vector, wherein the operator type information vector, the operator position information vector, the operator metadata information vector, the operator sample bitmap information vector and the predicate information vector constitute the initial vector representation of the historical query plan; step S3, constructing an initial model, the initial model comprising an embedding layer, a representation layer and a fully connected unit; wherein: the embedding layer is used to perform feature extraction on the initial vector representation of the query plan to obtain the key feature representation of the query plan; the representation layer is used to perform recursive learning on the key feature representation of the query plan to obtain the deep key feature representation of the query plan; the fully connected unit is used to perform prediction processing on the deep key feature representation of the query plan to predict the execution cost of the query plan; step S4, taking the initial vector representation of the historical query plan as input and the execution cost of the historical query plan as output, performing multiple rounds of iterative training and updating the initial model parameters according to the preset objective function until convergence to obtain a cost estimation model.
[0009] In some embodiments of the present aspect, the embedding layer includes a first processing module and a second processing module connected in parallel, and a connection module connected after the first processing module and the second processing module, wherein: the first processing module is a single-layer fully connected neural network, which is used to extract features of the operator type information vector, the operator position information vector, the operator metadata information vector and the operator sample bitmap information vector in the initial vector representation of the query plan to obtain key feature representations of the operator type information, the operator position information, the operator metadata information and the operator sample bitmap information; the second processing module includes a maximum pooling layer, a minimum pooling layer and a fully connected layer connected in series in sequence, which are used to extract features of the predicate information vector in the initial vector representation of the query plan to obtain key feature representations of the predicate information; the connection module is used to perform splicing processing on the key feature representations of the operator type information, the operator position information, the operator metadata information, the operator sample bitmap information and the predicate information to obtain the key feature representations of the query plan.
[0010] In some embodiments of this aspect, the representation layer is a tree-like long short-term memory neural network model, a recurrent neural network model, or a graph neural network model.
[0011] In some embodiments of this aspect, the fully connected unit is a two-layer fully connected neural network.
[0012] In some embodiments of this aspect, the preset objective function is a mean square error loss function.
[0013] In some embodiments of the present aspect, a historical query plan is obtained in the following manner: a connection graph is constructed based on an existing database, the connection graph including multiple nodes and multiple edges connecting two nodes, wherein a node represents a data table, and the data table includes multiple columns of numerical type and multiple columns of string type; an edge represents a primary key and a foreign key relationship between two data tables; according to the constructed connection graph, a group of data tables that need to be connected are randomly selected; a column with a numerical type is randomly selected from the selected data table, and a numerical value is selected from the column according to a preset method, and an operator is randomly selected to construct a numerical comparison expression based on the randomly selected column of numerical type and the numerical value selected from the column; a column with a string type is randomly selected from the selected data table, and a string value is randomly selected from the column, and an operator is randomly selected to construct a string expression based on the randomly selected column of string type and the string value selected from the column; the constructed numerical comparison expression and string expression are aggregated into a query statement through a logical operator, and multiple query plans are generated based on the constructed query statement and each query plan is executed to obtain the execution time corresponding to each query plan.
[0014] According to a second aspect of the present invention, there is provided a query plan cost estimation system for predicting the execution cost of executing a query plan, the system comprising: a feature extractor for encoding operator type information, operator position information, operator metadata information, and operator sample bitmap information in a query plan based on a one-hot encoding method, and encoding predicate information in a query plan using a word embedding encoding method to obtain an initial vector representation of the query plan; a cost estimation model constructed by the method described in the first aspect of the present invention, for performing prediction processing based on the initial vector representation of the query plan to predict the execution cost of the query plan.
[0015] According to a third aspect of the present invention, a query plan cost estimation method is provided for predicting the execution cost of executing a query plan, the method comprising: step T1, obtaining a query plan to be processed; step T2, using the system as described in the second aspect of the present invention to perform predictive processing on the query plan to be processed to predict the execution cost of the query plan to be processed.
[0016] Compared with the prior art, the advantages of the present invention are: (1) when extracting feature information, the features of the operator position information are additionally extracted, so that the cost estimation model can more comprehensively represent the execution characteristics of the query plan in the computing power network scenario, thereby improving the prediction accuracy of the query plan; (2) the word embedding coding method is introduced to encode the predicate conditions, which not only improves the encoding efficiency of the predicate conditions, but also reduces the computing resources occupied by the cost estimation model during prediction without sacrificing accuracy. BRIEF DESCRIPTION OF THE DRAWINGS
[0017] The embodiments of the present invention are further described below with reference to the accompanying drawings, in which:
[0018] Figure 1 A schematic diagram of a cost estimation model construction method according to an embodiment of the present invention;
[0019] Figure 2 This is a diagram of a query plan example;
[0020] Figure 3 A schematic diagram of an example of query plan encoding processing according to an embodiment of the present invention;
[0021] Figure 4 A schematic diagram of the initial model structure according to an embodiment of the present invention;
[0022] Figure 5 A schematic diagram of the composition of the embedding layer of the initial model according to an embodiment of the present invention;
[0023] Figure 6 A schematic diagram of an example of a cost estimation model construction process according to an embodiment of the present invention;
[0024] Figure 7 This is an example schematic diagram of a query plan cost estimation system interacting with a TiDB kernel physical optimizer to perform a cost estimation task according to an embodiment of the present invention. DETAILED DESCRIPTION
[0025] In order to make the purpose, technical solutions and advantages of the present invention more clearly understood, the present invention is further described in detail below through specific embodiments in conjunction with the accompanying drawings. It should be understood that the specific embodiments described herein are only used to explain the present invention and are not intended to limit the present invention.
[0026] As mentioned in the background technology section, the traditional cost estimation method is based on the design of a single-machine database system architecture. In the cross-domain distributed system architecture of the computing power network, due to the inconsistency of the hardware configuration of the data center and the complexity of the network environment, the cost factor is difficult to determine, resulting in the inability of the traditional cost estimation method to accurately predict the execution time of the query plan. In contrast, although the cost estimation method based on machine learning is suitable for the cross-domain distributed system architecture of the computing power network, this method does not fully consider the complexity of the execution of operators in different data centers in the computing power network scenario, resulting in insufficient extracted feature information, which in turn affects the accuracy of the prediction results.
[0027] In order to solve the above problems, the inventors studied the cost estimation method based on machine learning and found that the method has two main defects. First, the feature information extraction is insufficient. The feature information extracted by the existing cost estimation method based on machine learning is not comprehensive enough and fails to fully reflect the complexity of the execution of different operators in the query plan in different data centers, resulting in low accuracy of the prediction results. Second, the predicate information encoding is poor. When encoding the predicate information, the existing cost estimation method based on machine learning uses the traditional one-hot encoding method for encoding. This encoding method not only leads to information sparseness, but also affects computing efficiency and storage space. Based on the above analysis, the inventors proposed a new model building scheme to construct a cost estimation model to predict the execution time of a query plan. In this scheme, when extracting feature information, the features of the operator position information are additionally extracted, so that the cost estimation model can more comprehensively represent the execution characteristics of the query plan in the computing power network scenario, which helps to improve the prediction accuracy; further, a word embedding coding method (WordEmbedding) is introduced to encode the predicate conditions. The word embedding coding method is used to uniformly process the numeric type and string type data in the predicate conditions, so that the predicate conditions with similar semantics are close to each other in the vector space. This encoding method can not only improve the encoding efficiency of the predicate conditions, but also reduce the computing resources occupied by the cost estimation model during prediction without sacrificing accuracy.
[0028] In summary, if Figure 1 As shown, the present invention provides a cost estimation model construction method, the method comprising: step S1, obtaining executed historical query plans to construct a training data set, the training data set comprising multiple historical query plans and their corresponding labels, the label of each historical query plan is the actual execution time of the historical query plan; wherein each historical query plan comprises operator type information, operator position information, operator metadata information, operator sample bitmap information and predicate information; step S2, using a one-hot encoding method to encode the operator type information, operator position information, operator metadata information, and operator sample bitmap information in the historical query plan to obtain an operator type information vector, an operator position information vector, an operator metadata information vector and an operator sample bitmap information vector, and using a word embedding encoding method to encode the predicate information in the historical query plan to obtain a predicate information vector The operator type information vector, the operator position information vector, the operator metadata information vector, the operator sample bitmap information vector and the predicate information vector constitute the initial vector representation of the historical query plan; step S3, constructing an initial model, the initial model includes an embedding layer, a representation layer and a fully connected unit; wherein: the embedding layer is used to perform feature extraction on the initial vector representation of the query plan to obtain the key feature representation of the query plan; the representation layer is used to perform recursive learning on the key feature representation of the query plan to obtain the deep key feature representation of the query plan; the fully connected unit is used to perform prediction processing on the deep key feature representation of the query plan to predict the execution cost of the query plan; step S4, taking the initial vector representation of the historical query plan as input and the execution cost of the historical query plan as output, performing multiple rounds of iterative training and updating the initial model parameters according to the preset objective function until convergence to obtain a cost estimation model.
[0029] Before specifically introducing the embodiments of the present invention, some of the terms used therein are explained as follows:
[0030] Query plan: tree-structured data consisting of multiple nodes and multiple edges connecting two nodes, where nodes represent operators and edges between nodes represent data flows, that is, the output of one operator to the input of another operator. Operators represent an abstract representation of a series of operation steps used to execute queries, and operators define how data is processed and transformed.
[0031] Operator type: various calculations and operations involved in the query plan execution process, such as hash join operator (HashJoin), table read operator (TableReader), selection operator (Selection), full table scan operator (TableFullScan), etc.
[0032] Operator location: The geographical location of the operators involved in the query plan in the computing network scenario. Due to network latency, bandwidth limitations, and heterogeneity of hardware configurations between data centers, the execution cost of the same operator in different data centers may vary significantly.
[0033] Operator metadata: information that describes the attributes, characteristics, behaviors, and relationships of an operator with other operators.
[0034] Operator sample bitmap: An efficient data structure used as a tool to estimate the selectivity of predicates during query optimization. It consists of a fixed-length binary array, where each bit of the array corresponds to a tuple in the data set, reflecting whether the tuple meets the operator's predicate condition. When the data tuple matches the predicate, the corresponding bit is marked as 1; if it does not match, it is marked as 0.
[0035] Predicate information: The predicate conditions involved in the query plan execution process, where the predicate conditions are used to filter or select records that meet the conditions in the data set.
[0036] In order to better understand the present invention, each step is described in detail below in conjunction with specific embodiments.
[0037] 1. Step S1
[0038] In step S1, the executed historical query plans are obtained to construct a training data set, which includes multiple historical query plans and their corresponding labels. The label of each historical query plan is the actual execution time of the historical query plan; wherein each historical query plan includes operator type information, operator location information, operator metadata information, operator sample bitmap information and predicate information.
[0039] In one embodiment of the present invention, a historical query plan is obtained in the following manner: a connection graph is constructed based on an existing database, the connection graph including multiple nodes and multiple edges connecting two nodes, wherein a node represents a data table, and the data table includes multiple columns of numerical type and multiple columns of string type; an edge represents a primary key and a foreign key relationship between two data tables; according to the constructed connection graph, a group of data tables that need to be connected are randomly selected; a column with a numerical type is randomly selected from the selected data table, and a numerical value is selected from the column according to a preset method, and an operator is randomly selected based on the randomly selected column of numerical type and the numerical value selected from the column to construct a numerical comparison expression; a column with a string type is randomly selected from the selected data table, and a string value is randomly selected from the column, and an operator is randomly selected based on the randomly selected column of string type and the string value selected from the column to construct a string expression; the constructed numerical comparison expression and string expression are aggregated into a query statement through a logical operator, and multiple query plans are generated based on the constructed query statement and each query plan is executed to obtain the execution time corresponding to each query plan.
[0040] In order to better understand the query plan construction process described in the above embodiment, a specific query plan construction example is used for explanation below. The specific construction process is as follows.
[0041] First, a connection graph is constructed using the existing database, and a group of data tables that need to be connected are randomly selected based on the constructed connection graph.
[0042] Then randomly select a column with a numeric type from the selected data table, such as an integer, a floating point number, a date, etc., and select a value from the column by random sampling, statistical distribution sampling, or boundary value sampling. Randomly select an operator from the operator set {'>', '<', '=', '!='} to construct a numeric comparison expression. For example, if the selected column is "price" and the randomly selected value is "100", the constructed numeric expression can be "price>100"; for another example, if the selected column is "stock" and the randomly selected value is "50", the constructed numeric expression can be "stock<50". Among them, random sampling is to randomly select a value from the value range of the column, statistical distribution sampling is to select a representative value (average, maximum, minimum or median) according to the statistical information corresponding to the column, and boundary value sampling is to select the boundary value (maximum or minimum value) of the column.
[0043] Then randomly select a column with a string type from the selected data table, such as "VARCHAR", "CHAR", etc., and randomly select a string value from the column, and randomly select an operator from the operator set {'=', '!=', 'LIKE', 'NOT LIKE', 'IN'} to construct a string expression. For example, if the selected column is "name", and the string value randomly selected from the column is "%Smith%", the constructed string expression can be "name LIKE'%Smith%"; for another example, if the selected column is "email", and the string value randomly selected from the column is "example@gmail.com", the constructed string expression can be "email=example@gmail.com"; for another example, if the selected column is "category", and the string value randomly selected from the column is "Electronics", the constructed string expression can be "category='Electronics'".
[0044] Finally, the constructed numerical comparison expression and string expression are aggregated into a query statement through logical operators, multiple query plans are generated based on the constructed query statement, and each query plan is executed to obtain the execution time corresponding to each query plan. For example, the numerical expression "price>100", the numerical expression "stock<50" and the string expression "category='Electronics'" can be aggregated by using the logical operator "AND" to obtain (price>100)AND(stock<50)AND(category='Electronics'); the logical operator "OR" can also be used to obtain (price>100)OR(stock<50)OR(category='Electronics'); the logical operators "AND" and "OR" can also be mixed to obtain (price>100)AND((stock<50)OR(category='Electronics')). It should be noted that the SQL query statement obtained after the aggregation of the numerical expression and the string expression cannot be directly used as a training sample, and further processing is required to convert the SQL query statement into multiple query plans. The conversion process is: first, the query statement is converted into a structured and hierarchical expression by a parser, and then the parsed query statement is verified by a verifier, and then the verified query statement is processed by a logical optimizer to obtain a logical plan, and finally the physical optimizer is used to process the logical plan to generate multiple query plans, and each query plan is executed by an executor to obtain the execution time of each query plan.
[0045] To understand the query plan more intuitively, Figure 2 The query plan example shown is for illustration. Figure 2 As shown in the query plan example, node 4 queries all the data in the movie_companies table, node 3 filters the data queried by node 4 according to the set filtering condition (mc.id>100AND(mc.year)), node 2 gets the filtered data in the movie_companies table and sends it to node 1; node 6 queries all the data in the movie table, node 5 receives all the data in the movie table and sends it to node 1; node 1 connects the two table data received according to the set hash key. Among them, the operator type of node 1 is the hash join operator (Hash The connection filter condition of this node is "(mc.movie_id=m.id)", and the operator location information is City A; the operator type of Node 2 is TableReader, the name of the data table read by this node is "movie_companies", and the operator location information is City A; the operator type of Node 3 is Selection, the name of the data table operated by the node is "movie_companies", the operator location information is City B, and the filter condition is mc.id>100AND(m c.year); the operator type of node 4 is the full table scan operator (TableFullScan), the name of the data table operated by this node is "movie_companies", and the location information of the operator is city B; the operator type of node 5 is the table read operator (TableReader), the name of the data table operated by this node is "movie", and the location information of the operator is city A; the operator type of node 6 is the full table scan operator (TableFullScan), the name of the data table operated by this node is "movie", and the location information of the operator is city C.
[0046] Step S2
[0047] In step S2, the operator type information, operator position information, operator metadata information, and operator sample bitmap information in the historical query plan are encoded using a one-hot encoding method to obtain an operator type information vector, an operator position information vector, an operator metadata information vector, and an operator sample bitmap information vector, and the predicate information in the historical query plan is encoded using a word embedding encoding method to obtain a predicate information vector. The operator type information vector, the operator position information vector, the operator metadata information vector, the operator sample bitmap information vector, and the predicate information vector constitute an initial vector representation of the historical query plan. It should be noted that the query plan is a tree structure data composed of multiple nodes and multiple edges connecting two nodes. Operator type information, operator position information, operator metadata information, operator sample bitmap information, and predicate information are extracted from each node in the query plan, and these information are encoded using a one-hot encoding method or a word embedding encoding method, so that a tree vector representation consistent with the tree structure of the query plan can be obtained. It should be noted that the features contained in the operator type information, operator position information, operator metadata information, and operator sample bitmap information are relatively simple. The use of One-Hot encoding can very intuitively capture the features contained in this information and will not lead to information sparsity. However, the predicate information contains a wide variety of predicate conditions. The use of One-Hot encoding will lead to information sparsity and fail to capture the deep semantic information contained in the predicate conditions. Therefore, word embedding encoding is introduced to encode the predicate information.
[0048] To better understand the query plan encoding process, Figure 3 The query plan coding example shown is used to illustrate this. Figure 3 As shown in the figure, the left side is the query plan without encoding (simplified representation, only the operator type of each node is given), and the right side is the initial vector representation of the query plan. Figure 3 It can be seen that the query plan contains Scan operators, hash join operators and aggregation operators. The one-hot encoding method is used to perform position encoding, operator type encoding, metadata encoding and sample bitmap encoding on each operator in the query plan, and the word embedding encoding method is used to perform predicate encoding on each operator in the query plan to obtain an initial vector representation consistent with the tree structure of the query plan on the left.
[0049] Among them, the one-hot encoding method is a method of converting categorical variables into binary vectors. In this encoding method, each category value is represented as a binary vector, except for one position representing the category, which is 1, and the rest of the positions are 0. Taking the operator position encoding as an example, the encoding process is shown in Table 1. As can be seen from Table 1, there are three categories of operator locations, namely City A, City B and City C, among which City A is encoded as 010, City B is encoded as 001, and City C is encoded as 100.
[0050] Table 1
[0051] Operation Location One-Hot Encoding City A 010 City B 001 City C 100
[0052] Word embedding is a method of encoding words or categories into dense vectors. These vectors can capture the potential semantics or relationship information between words or categories. Taking predicate encoding as an example, the encoding process is shown in Table 2. As can be seen from Table 2, each predicate condition is converted into a continuous vector of fixed length.
[0053] Table 2
[0054] Column Embedding Encoding mc.movie_id [0.18,1.31,…,0.89,2.56] m.id [1.01,2.24,…,0.77,1.68] mc_id [1.10,1.28,…,2.03,0.39] mc.year [0.19,1.84,…,0.27,0.99] Operator Embedding Encoding ＝ [1.24,3.27,…,1.50,0.61] > [1.02,0.81,…,1.42,0.68] < [0.81,2.10,…,0.61,0.30] Operand Embedding Encoding 50 [1.58,0.28,…0.64,1.71] 100 [0.07,0.84,…,-2.55,0.94] 200 [0.41,1.39,…,2.13,0.84]
[0055] Step S3
[0056] In step S3, an initial model is constructed, such as Figure 4 As shown, the initial model includes an embedding layer, a representation layer, and a fully connected unit; wherein: the embedding layer is used to perform feature extraction on the initial vector representation of the query plan to obtain the key feature representation of the query plan; the representation layer is used to perform recursive learning on the key feature representation of the query plan to obtain the deep key feature representation of the query plan; the fully connected unit is used to perform prediction processing on the deep key feature representation of the query plan to predict the execution cost of the query plan. It should be noted that the embedding layer is used to extract the key feature representation (vector) in each node in the query plan and connect these key feature representations together; the representation layer is used to learn the deep key features of different nodes in the query plan and the intrinsic connection between the nodes, and through a layer-by-layer iteration method, the deep key feature representation (vector) of the query plan obtained by the representation layer contains the key feature information of the entire query plan; the prediction layer is used to predict the execution time of the query plan based on the deep key feature representation of the query plan obtained by the representation layer. The following is an introduction to each component of the initial model.
[0057] According to one embodiment of the present invention, Figure 5As shown, the embedded layer includes a first processing module and a second processing module connected in parallel, and a connection module connected after the first processing module and the second processing module.
[0058] Among them, the first processing module is a single-layer fully connected neural network, which is used to extract features from the operator type information vector, operator position information vector, operator metadata information vector and operator sample bitmap information vector in the initial vector representation of the query plan to obtain key feature representations of operator type information, key feature representations of operator position information, key feature representations of operator metadata information and key feature representations of operator sample bitmap information. It should be noted that the features contained in the operator type information vector, operator position information vector, operator metadata information vector and operator sample bitmap information vector are relatively simple, and a single-layer fully connected neural network is used, and ReLU activation function and rectified linear unit are applied to quickly capture linear features.
[0059] The second processing module includes a maximum pooling layer, a minimum pooling layer and a fully connected layer connected in series, which are used to extract features from the predicate information vector in the initial vector representation of the query plan to obtain the key feature representation of the predicate information. It should be noted that the predicate information vector contains features that express logical conditions or semantics, which usually involve a combination of multiple logical relationships, such as AND and OR. In order to extract the features contained in the predicate information vector, Min-MaxPooling is used for feature extraction. For example, for a compound predicate with AND semantics, a minimum pooling layer (Min PoolingLayer) is used to more accurately estimate the number of results that meet the predicate by selecting the minimum value of the estimated results of the two atomic predicates that meet the conditions; for a compound predicate with OR semantics, a maximum pooling layer (Max Pooling Layer) is used to evaluate the number of results that meet the predicate by selecting the maximum value of the estimated results of the two atomic predicates that meet the conditions. It should also be noted that a ReLU activation function and a rectified linear unit are provided on the maximum pooling layer, and a ReLU activation function is provided on the minimum pooling layer.
[0060] The connection module is used to perform splicing processing on the key feature representation of operator type information, the key feature representation of operator position information, the key feature representation of operator metadata information, the key feature representation of operator sample bitmap information and the key feature representation of predicate information to obtain the key feature representation of the query plan. It should be noted that splicing processing refers to splicing two vectors together. For example, there is a feature representation as a vector with a dimension of p, and a feature representation as a vector of o. These two feature representations are spliced together. The vector dimension of the new feature representation after splicing is p+o, and the new feature representation can be E=[p,o].
[0061] According to one embodiment of the present invention, the representation layer is a tree-like long short-term memory neural network model, a recurrent neural network model or a graph neural network model. It should be noted that the network model adopted by the representation layer can handle the information attenuation problem faced when long-distance dependent information is used, and through a recursive learning method, gradually learn the deep key features of each node in the query plan and the intrinsic connection between the nodes, and obtain a comprehensive deep key feature representation through a layer-by-layer iteration method. The deep key feature representation summarizes the key information of the entire query plan and can help the prediction layer to effectively estimate the cost of the query plan.
[0062] According to one embodiment of the present invention, the fully connected unit is a two-layer fully connected neural network, wherein the first layer of the fully connected neural network is provided with a ReLU activation function and a rectified linear unit, and the second layer of the fully connected neural network is provided with a ReLU activation function and a sigmoid output function.
[0063] Step S4
[0064] In step S4, the initial vector representation of the historical query plan is used as input, the execution cost of the historical query plan is used as output, multiple rounds of iterative training are performed, and the initial model parameters are updated according to the preset objective function until convergence to obtain a cost estimation model. According to one embodiment of the present invention, the preset objective function is a mean square error loss function.
[0065] In order to better understand the cost estimation model construction process described in the above steps S1-S4, Figure 6 The model building process diagram shown is used as an example to illustrate. Figure 6 It can be seen that the specific model construction process is: first obtain data from the database to construct a query statement; then use the query optimizer to generate multiple query plans based on the query statement and then execute each query plan to obtain the execution cost of each query plan (actual execution time); then use one-hot encoding or word embedding encoding to encode the query plan to obtain the initial vector representation of the query plan (tree vector); finally, use the initial vector representation of the query plan as input and the execution cost (cost estimate) of the historical query plan as output, perform multiple rounds of iterative training and update the initial model parameters according to the preset objective function until convergence to obtain a cost estimation model.
[0066] Based on the cost estimation model constructed in the aforementioned embodiment, the present invention further proposes a query plan cost estimation system for predicting the execution cost of executing a query plan, the system comprising: a feature extractor for encoding the operator type information, operator position information, operator metadata information, and operator sample bitmap information in the query plan based on a one-hot encoding method, and encoding the predicate information in the query plan using a word embedding encoding method to obtain an initial vector representation of the query plan; a cost estimation model constructed by the method described in the aforementioned embodiment is used to perform prediction processing based on the initial vector representation of the query plan to predict the execution cost of the query plan. The query plan cost estimation system proposed in the present invention can interact with the TiDB (distributed relational database) kernel physical optimizer to perform cost estimation tasks. Figure 7 As shown in the figure, it shows the flow chart of the query plan cost estimation system and the TiDB kernel physical optimizer interacting to perform cost estimation. Figure 7 It can be seen that the specific execution process is: the SQL query statement is first processed by the parser to convert the SQL statement into a structured, hierarchical representation; then, the verifier verifies the parsed SQL statement; the logical optimizer processes the verified SQL statement to generate a logical plan; then, the physical optimizer processes the logical plan to generate multiple query plans, and sends the multiple query plans to the query plan cost estimation system to predict the execution cost (estimated cost) of each query plan; the physical optimizer selects the query plan with the lowest execution cost based on the execution cost of each query plan; the TiDB executor executes the query plan with the lowest cost and sends the execution result to the distributed coprocessor; the distributed coprocessor sends the execution result to the memory for storage.
[0067] Based on the cost estimation system constructed by the aforementioned embodiment, the present invention also proposes a query plan cost estimation method for predicting the execution cost of executing a query plan, the method comprising: step T1, obtaining a query plan to be processed; step T2, using the cost estimation system as described in the aforementioned embodiment to perform predictive processing on the query plan to be processed to predict the execution cost of the query plan to be processed.
[0068] The beneficial effects of the present invention are: (1) when extracting feature information, the features of the operator position information are additionally extracted, so that the cost estimation model can more comprehensively represent the execution characteristics of the query plan in the computing power network scenario, thereby improving the prediction accuracy of the query plan; (2) the word embedding coding method (Word Embedding) is introduced to encode the predicate conditions, which not only improves the encoding efficiency of the predicate conditions, but also reduces the computing resources occupied by the cost estimation model during prediction without sacrificing accuracy.
[0069] It should be noted that although the above describes the various steps in a specific order, it does not mean that the various steps must be executed in the above specific order. In fact, some of these steps can be executed concurrently or even in a different order as long as the required functions can be achieved.
[0070] The present invention may be a system, a method and / or a computer program product. The computer program product may include a computer-readable storage medium carrying computer-readable program instructions for causing a processor to implement various aspects of the present invention.
[0071] Computer readable storage medium can be a tangible device that holds and stores instructions used by an instruction execution device. Computer readable storage medium can include, for example, but is not limited to, an electrical storage device, a magnetic storage device, an optical storage device, an electromagnetic storage device, a semiconductor storage device, or any suitable combination thereof. More specific examples (non-exhaustive list) of computer readable storage medium include: a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), a static random access memory (SRAM), a portable compact disk read-only memory (CD-ROM), a digital versatile disk (DVD), a memory stick, a floppy disk, a mechanical encoding device, for example, a punch card or a protruding structure in a groove on which instructions are stored, and any suitable combination thereof.
[0072] The embodiments of the present invention have been described above, and the above description is exemplary, not exhaustive, and is not limited to the disclosed embodiments. Many modifications and variations will be apparent to those of ordinary skill in the art without departing from the scope and spirit of the described embodiments. The selection of terms used herein is intended to best explain the principles of the embodiments, practical applications, or technical improvements in the market, or to enable other persons of ordinary skill in the art to understand the embodiments disclosed herein.
Claims
1. A cost estimation model construction method, characterized in that: The method comprises: Step S1, obtaining executed historical query plans to construct a training data set, wherein the training data set includes multiple historical query plans and their corresponding labels, and the label of each historical query plan is the actual execution time of the historical query plan; wherein each historical query plan includes operator type information, operator location information, operator metadata information, operator sample bitmap information and predicate information; Step S2, using one-hot encoding to encode the operator type information, operator position information, operator metadata information, and operator sample bitmap information in the historical query plan to obtain an operator type information vector, an operator position information vector, an operator metadata information vector, and an operator sample bitmap information vector, and using word embedding encoding to encode the predicate information in the historical query plan to obtain a predicate information vector, wherein the operator type information vector, the operator position information vector, the operator metadata information vector, the operator sample bitmap information vector, and the predicate information vector constitute an initial vector representation of the historical query plan; Step S3: construct an initial model, wherein the initial model includes an embedding layer, a representation layer, and a fully connected unit; wherein: The embedding layer is used to extract features from the initial vector representation of the query plan to obtain the key feature representation of the query plan; The representation layer is used to recursively learn the key feature representation of the query plan to obtain the deep key feature representation of the query plan; The fully connected unit is used to perform prediction processing on the deep key feature representation of the query plan to predict the execution cost of the query plan; Step S4: take the initial vector representation of the historical query plan as input and the execution cost of the historical query plan as output, perform multiple rounds of iterative training and update the initial model parameters according to the preset objective function until convergence to obtain a cost estimation model.
2. The method according to claim 1, characterized in that The embedded layer includes a first processing module and a second processing module connected in parallel, and a connection module connected after the first processing module and the second processing module, wherein: The first processing module is a single-layer fully connected neural network, which is used to extract features from the operator type information vector, the operator position information vector, the operator metadata information vector, and the operator sample bitmap information vector in the initial vector representation of the query plan to obtain key feature representations of the operator type information, the operator position information, the operator metadata information, and the operator sample bitmap information; The second processing module includes a maximum pooling layer, a minimum pooling layer, and a fully connected layer connected in series, and is used to extract features from the predicate information vector in the initial vector representation of the query plan to obtain key feature representations of the predicate information; The connection module is used to concatenate the key feature representation of operator type information, the key feature representation of operator position information, the key feature representation of operator metadata information, the key feature representation of operator sample bitmap information and the key feature representation of predicate information to obtain the key feature representation of the query plan.
3. The method according to claim 2, characterized in that The representation layer is a tree-like long short-term memory neural network model, a recurrent neural network model or a graph neural network model.
4. The method according to claim 3, characterized in that The fully connected unit is a double-layer fully connected neural network.
5. The method according to claim 4, characterized in that The preset objective function is a mean square error loss function.
6. The method according to claim 5, characterized in that To obtain historical query plans, follow these steps: A connection graph is constructed based on an existing database, wherein the connection graph includes multiple nodes and multiple edges connecting two nodes, wherein a node represents a data table, and the data table includes multiple columns of numerical type and multiple columns of string type; an edge represents a primary key and a foreign key relationship between two data tables; According to the constructed connection graph, a group of data tables that need to be connected are randomly selected; Randomly select a column with a numeric type from a selected data table, and select a value from the column in a preset manner, and randomly select an operator to construct a numeric comparison expression based on the randomly selected numeric type column and the value selected from the column; Randomly select a column with a string type from the selected data table, and randomly select a string value from the column, and randomly select an operator to build a string expression based on the randomly selected column with a string type and the string value selected from the column; The constructed numeric comparison expression and string expression are aggregated into a query statement through logical operators, multiple query plans are generated based on the constructed query statement, and each query plan is executed to obtain the execution time corresponding to each query plan.
7. A query plan cost estimation system for predicting the execution cost of executing a query plan, characterized in that: The system comprises: A feature extractor is used to encode the operator type information, operator position information, operator metadata information, and operator sample bitmap information in the query plan based on a one-hot encoding method, and to encode the predicate information in the query plan using a word embedding encoding method, so as to obtain an initial vector representation of the query plan; The cost estimation model constructed by the method as described in any one of claims 1 to 6 is used to perform prediction processing based on the initial vector representation of the query plan to predict the execution cost of the query plan.
8. A query plan cost estimation method for predicting the execution cost of executing a query plan, characterized in that: The method comprises: Step T1, obtaining the query plan to be processed; Step T2: Use the system as described in claim 7 to perform prediction processing on the query plan to be processed to predict the execution cost of the query plan to be processed.
9. A computer-readable storage medium, characterized in that: A computer program is stored thereon, and the computer program can be executed by a processor to implement the steps of the method in any one of claims 1-6 and 8.
10. An electronic device, characterized in that: include: one or more processors; as well as A memory, wherein the memory is used to store executable instructions; The one or more processors are configured to implement the steps of the method of any one of claims 1-6 and 8 by executing the executable instructions.