A base number estimation method, device and storage medium

Abstract

The application discloses a cardinality estimation method and device and a medium. The method encodes a to-be-predicted query statement and inputs the to-be-predicted query statement into a deep learning model trained based on historical data to predict a cardinality. If the predicted value exceeds a preset threshold, the predicted value is adopted as a final estimation result; if the predicted value does not exceed the preset threshold, an accurate cardinality calculated according to a multi-way tree forest structure constructed by database records is taken as the final estimation result. The application fully utilizes the advantages of a hybrid model, and improves the accuracy and reliability of cardinality estimation prediction.

Description

TECHNICAL FIELD

[0001] The present application relates to the field of cardinality estimation, and in particular to a cardinality estimation method, device and storage medium. BACKGROUND

[0002] Cardinality estimation is an important part of database query optimization, which helps the database query optimizer to select the best query execution plan by estimating the number of query results (cardinality value) in advance, and plays an important role in improving the performance of database queries. Traditional cardinality estimation mainly includes two types: histogram-based method, which obtains the result by multiplying the attribute proportion value; sampling-based method, which estimates the overall cardinality from a small sample. With the development of deep learning technology, new cardinality estimation methods are divided into two categories: data-based and query-based. The former decomposes and predicts the cardinality probability value through an autoregressive model, and the latter treats the task as a regression problem and uses encoding and neural networks to learn and predict the query statement. Histogram-based cardinality estimation ignores the correlation between attributes, resulting in large errors when dealing with highly correlated attributes. The sampling-based method is affected by the quality of the sampling algorithm, and is difficult to adapt to frequent changes in database data. Although the learning-based model has better performance, there are still some shortcomings.

[0003] The existing cardinality estimation model has shortcomings in evaluation indicators, processing low cardinality queries and database dynamics: the Q error evaluation indicator leads the model to focus on training low cardinality queries; the learning model is easily misled by a single high cardinality condition, affecting the accuracy of low cardinality queries; static training models are difficult to adapt to dynamic updates of databases, and an effective update mechanism is needed to maintain performance; these shortcomings result in inaccurate estimation of cardinality. SUMMARY

[0004] The present application provides a cardinality method, device and storage medium to solve the problem of inaccurate cardinality estimation in the prior art.

[0005] In a first aspect, the present application provides a cardinality estimation method, comprising:

[0006] Obtaining a query statement to be predicted;

[0007] Encoding the query statement to be predicted and inputting it into a deep learning model to obtain a cardinality estimation result, wherein the deep learning model is obtained by training an initial deep learning model on encoded historical query statements;

[0008] If the cardinality estimation result is greater than a preset threshold, the cardinality estimation result is taken as the final cardinality estimation result; otherwise, the accurate cardinality output by the forest structure is taken as the final cardinality estimation result; wherein the forest structure is obtained by constructing a multi-way tree according to database record data.

[0009] The application combines a learning model and an accurate solution structure into a cardinality estimation hybrid model. The hybrid model makes it possible to use a deep learning model to estimate a high-cardinality query and use a forest structure to accurately solve a low-cardinality query, thereby solving the problem of being unable to accurately estimate cardinality in the prior art.

[0010] As a preferred embodiment of the first aspect, the deep learning model is obtained by training an initial deep learning model on the encoded historical query statements, specifically:

[0011] The encoded historical query statements are obtained to obtain historical query vectors;

[0012] The initial deep learning model is built using two consecutive fully connected layers and a sigmoid function.

[0013] The historical query vectors are obtained by the initial deep learning model and are trained, and a single-attribute cardinality estimation upper bound loss function is used to optimize the initial deep learning model during the training process to obtain the deep learning model.

[0014] As a preferred embodiment of the first aspect, the optimization using the single-attribute cardinality estimation upper bound loss function during the training process is specifically:

[0015] The expression of the single-attribute cardinality estimation upper bound loss function is:

[0016]

[0017] In the formula, P represents a predicted value, L represents a label, and Bnd represents a single-attribute cardinality upper bound.

[0018] During the training process, it is continuously monitored whether the predicted value of the cardinality estimation is close to or exceeds a pre-set single-attribute cardinality upper bound value.

[0019] When it is detected that the predicted value of the cardinality estimation exceeds or is expected to exceed the single-attribute cardinality upper bound value, a corresponding penalty is automatically imposed by the single-attribute cardinality estimation upper bound loss function.

[0020] In this preferred embodiment, the initial deep learning model is built using two consecutive fully connected layers and a sigmoid function, which enhances the learning ability of the model for complex query statements. The use of the single-attribute cardinality estimation upper bound loss function to optimize the initial deep learning model during the training process can limit the prediction space of the learning model using the single-attribute cardinality upper bound, thereby improving the accuracy of the deep learning model.

[0021] As a preferred embodiment of the first aspect, the forest structure is obtained by constructing a multi-way tree according to database record data, specifically:

[0022] obtaining database record data;

[0023] analyzing attributes of the database record data, and identifying a number of each attribute;

[0024] constructing a plurality of multi-way trees through the number of each attribute value, each tree representing a value range of an attribute;

[0025] combining all the constructed multi-way trees to obtain a forest structure.

[0026] In this preferred embodiment, the forest structure based on multi-way trees is constructed, which can effectively handle different attribute restriction conditions in the database record data and provide accurate cardinality estimation for the to-be-predicted query statement.

[0027] As a preferred embodiment of the first aspect, the preset threshold value is adaptively calculated by balancing the effects and overheads of a group of historical query statements on the deep learning model and the forest structure through a self-defined loss function;

[0028] The formula of the self-defined loss function is:

[0029]

[0030] wherein, the loss function Cost(η) is composed of t(η) and mqe(η); t(η) represents the time overhead of the forest structure; mqe(η) represents the average Q error value of the deep learning model and the forest structure in the historical data;

[0031] In this preferred embodiment, the threshold value is adaptively determined through the loss function, which balances the fast response of the deep learning model and the accuracy of the forest structure, so that the hybrid model can intelligently select the most suitable estimation strategy according to the historical data.

[0032] As a preferred embodiment of the first aspect, the forest structure is used to output an accurate cardinality as a final cardinality estimation result, specifically:

[0033] obtaining the to-be-predicted query statement to obtain a to-be-predicted query vector;

[0034] identifying the specified attribute and the corresponding restriction condition in the to-be-predicted query vector;

[0035] using a binary search method to find a sub-node range in the multi-way tree forest structure node that satisfies the specified attribute and the corresponding restriction condition;

[0036] recursively searching in the multi-way tree forest structure node until all leaf nodes or terminal nodes that satisfy the query condition are found;

[0037] Accumulate the statistical variables in the leaf nodes or terminal nodes satisfying the condition as the final accurate cardinality, and the accurate cardinality is the final cardinality estimation result.

[0038] In this preferred embodiment, the binary search and recursive search techniques are used to improve the efficiency of searching nodes satisfying the condition in the forest structure, and ensure the fast output of the accurate cardinality.

[0039] As a preferred embodiment of the first aspect, it further comprises: when the data of the database record is updated, adding the updated structure to the initial deep learning model and the forest structure;

[0040] The initial deep learning network model updates the single attribute cardinality upper bound, the sample vector and the label before training.

[0041] The newly added database record data is inserted into the forest structure one by one, and the statistical variables of each node of the multi-way tree in the forest structure are updated in turn before training.

[0042] In this preferred embodiment, the application introduces an update mechanism in the deep learning network and the forest structure, which can adapt to the dynamic changes of the database and maintain the timeliness and accuracy of the cardinality estimation result.

[0043] As a preferred embodiment of the first aspect, the encoded query statement to be predicted is input into the deep learning model to obtain the cardinality estimation result, specifically:

[0044] The query statement to be predicted is encoded to form an input vector, and the input vector includes three parts, which are attribute vector, predicate vector and sample vector.

[0045] The attribute vector uses one-hot encoding to indicate the attribute position.

[0046] The predicate vector is used to encode the predicate type and the restriction space.

[0047] The sample vector is used to represent the bitmap of whether the sample data meets the query statement.

[0048] The deep learning model obtains the encoded input vector.

[0049] The deep learning model predicts the cardinality estimation value to obtain the cardinality estimation result.

[0050] In this preferred embodiment, the application encodes the query statement into a vector containing attribute, predicate and sample information, and the vector of different information attributes improves the model's ability to capture the features of the query statement, provides more rich input information for the deep learning model, and thus improves the accuracy of the cardinality prediction.

[0051] In a second aspect, the application provides a cardinality estimation device. The cardinality estimation device comprises an acquisition module, a prediction module and a comparison module.

[0052] The acquisition module is configured to acquire a query statement to be predicted.

[0053] The prediction module is configured to input the encoded query statement to be predicted into a deep learning model to obtain a cardinality estimation result, wherein the deep learning model is obtained by training an initial deep learning model with encoded historical query statements.

[0054] The comparison module is configured to compare the cardinality estimation result with a preset threshold value. If the cardinality estimation result is greater than the preset threshold value, the cardinality estimation result is taken as a final cardinality estimation result. Otherwise, an accurate cardinality output by a forest structure is taken as the final cardinality estimation result, wherein the forest structure is obtained by constructing a multi-way tree according to database record data.

[0055] The device can better discover and handle problems in the cardinality estimation device by using three modules to work in a coordinated manner. The device can comprehensively utilize historical and real-time data, quickly generate a prediction result through the coordinated work of a deep learning network and a forest structure, and make an intelligent decision according to a dynamically determined threshold value, thereby ensuring the optimality of the output result.

[0056] As a preferred embodiment of the second aspect, the deep learning model is obtained by training an initial deep learning model with encoded historical query statements, specifically as follows.

[0057] The encoded historical query statements are acquired to obtain historical query vectors.

[0058] An initial deep learning model is built by using two consecutive fully connected layers and a sigmoid function.

[0059] The historical query vectors are acquired by the initial deep learning model and are trained. In the training process, a single-attribute cardinality estimation upper bound loss function is used to optimize the initial deep learning model to obtain the deep learning model.

[0060] As a preferred embodiment of the second aspect, the optimization in the training process is performed by using a single-attribute cardinality estimation upper bound loss function, specifically as follows.

[0061] The expression of the single-attribute cardinality estimation upper bound loss function is as follows.

[0062]

[0063] In the formula, P represents a predicted value, L represents a label, and Bnd represents a single-attribute cardinality upper bound.

[0064] continuously monitoring whether the predicted value of the cardinality estimation approaches or exceeds the preset single-attribute cardinality upper bound value during the training process;

[0065] when it is detected that the cardinality estimation predicted value exceeds or is expected to exceed the single-attribute cardinality upper bound value, automatically imposing a corresponding penalty through the single-attribute cardinality estimation upper bound loss function.

[0066] In this preferred embodiment, the present application uses two consecutive fully connected layers and a sigmoid function to construct an initial deep learning model, which enhances the learning ability of the model for complex query statements; the single-attribute cardinality estimation upper bound loss function is used to optimize the initial deep learning model during the training process, which can limit the prediction space of the learning model using the single-attribute cardinality upper bound, thereby improving the accuracy of the deep learning model.

[0067] As a preferred embodiment of the second aspect, the forest structure is obtained by constructing a multi-ary tree according to the database record data, specifically:

[0068] obtaining database record data;

[0069] analyzing the attributes of the database record data and identifying the number of each attribute;

[0070] constructing a plurality of multi-ary trees through the number of values of each attribute, and each tree representing the value range of an attribute;

[0071] combining all the constructed multi-ary trees to obtain the forest structure.

[0072] In this preferred embodiment, the present application constructs a forest structure based on multi-ary trees, which can effectively handle different attribute constraint conditions in the database record data and provide accurate cardinality estimation for the to-be-predicted query statement.

[0073] As a preferred embodiment of the second aspect, the preset threshold value is adaptively calculated by balancing the effects and overheads of a group of historical query statements on the two components of the deep learning model and the forest structure using a custom loss function;

[0074] The formula of the custom loss function is:

[0075]

[0076] wherein, the loss function Cost(η) is composed of two parts of t(η) and mqe(η); t(η) represents the time overhead of the forest structure; mqe(η) represents the average Q error value of the deep learning model and the forest structure in the historical data;

[0077] In this preferred embodiment, the application determines the threshold value through a loss function self-adaptation, balances the fast response of the deep learning model and the accuracy of the forest structure, and enables the hybrid model to intelligently select the most suitable estimation strategy according to historical data.

[0078] As a preferred embodiment of the second aspect, the forest structure output accurate cardinality is used as the final cardinality estimation result, specifically:

[0079] Obtaining the encoded query statement to be predicted to obtain the query vector to be predicted;

[0080] Identifying the specified attribute and the corresponding restriction condition in the query vector to be predicted;

[0081] Using a binary search method to find a sub-node range in the multi-way tree forest structure node that meets the specified attribute and the corresponding restriction condition;

[0082] Recursively searching in the multi-way tree forest structure node until all leaf nodes or terminal nodes that meet the query condition are found;

[0083] Accumulating the statistical variables in the leaf nodes or terminal nodes that meet the condition as the final accurate cardinality, and the accurate cardinality is the final cardinality estimation result.

[0084] In this preferred embodiment, the application uses binary search and recursive search techniques to improve the efficiency of searching for nodes that meet the conditions in the forest structure, ensuring the fast output of the accurate cardinality.

[0085] As a preferred embodiment of the second aspect, it also includes: when the data of the database records is updated, adding an update structure to the initial deep learning model and the forest structure;

[0086] Updating the single attribute cardinality upper bound, the sample vector, and the label before training the initial deep learning network model;

[0087] Inserting the newly added database record data into the forest structure one by one, updating the statistical variables of each node of the multi-way tree in the forest structure, and then training.

[0088] In this preferred embodiment, the application introduces an update mechanism in the deep learning network and the forest structure, which can adapt to the dynamic changes of the database and maintain the timeliness and accuracy of the cardinality estimation result.

[0089] As a preferred embodiment of the second aspect, the encoded query statement to be predicted is input into the deep learning model to obtain the cardinality estimation result, specifically:

[0090] The query statement to be predicted is encoded to form an input vector, and the input vector includes three parts, namely an attribute vector, a predicate vector and a sample vector;

[0091] The attribute vector uses one-hot encoding to indicate the attribute position;

[0092] The predicate vector is used to encode the predicate type and the limit space;

[0093] The sample vector is used to represent the bit map of whether the sample data meets the query statement;

[0094] The deep learning model obtains the encoded input vector;

[0095] The cardinality estimation result is obtained by predicting the cardinality estimation value through the deep learning model.

[0096] In the preferred embodiment, the query statement is encoded into a vector containing attribute, predicate and sample information. The vector of different information attributes improves the model's ability to capture the characteristics of the query statement, provides more rich input information for the deep learning model, and thus improves the accuracy of the cardinality prediction.

[0097] In a third aspect, the present application provides a computer readable storage medium, which includes a stored computer program, wherein the computer program controls the device where the computer readable storage medium is located to execute the cardinality estimation method as described when the computer program runs. The beneficial effects are the same as those of the cardinality estimation method provided in the first aspect of the present application. BRIEF DESCRIPTION OF DRAWINGS

[0098] Figure 1 : a flowchart of an embodiment of the cardinality estimation method provided by the present application;

[0099] Figure 2 : a structure diagram of the initial deep learning model structure provided by the present application;

[0100] Figure 3 : a structure diagram of the accurate cardinality solution forest structure building process provided by the present application;

[0101] Figure 4 : a structure diagram of the input query statement encoding vectorization provided by the present application;

[0102] Figure 5 : a structure diagram of an embodiment of the cardinality estimation device provided by the present application; DETAILED DESCRIPTION

[0103] With reference to the drawings of the embodiments of the present application, the technical solutions in the embodiments of the present application will be described clearly and completely. Obviously, the described embodiments are only a part of the embodiments of the present application, rather than all the embodiments. Based on the embodiments in the present application, all other embodiments obtained by those of ordinary skill in the art without creative work fall within the scope of the present application.

[0104] Embodiment one

[0105] Please refer to Figure 1 A cardinality estimation method is provided for the embodiments of the present application.

[0106] In the present embodiment, the process of the cardinality estimation method in the present application is described in detail through steps S01-S03.

[0107] S01: Obtain a query statement to be predicted;

[0108] S02: Input the encoded query statement to be predicted into a deep learning model to obtain a cardinality estimation result, wherein the deep learning model is obtained by training the initial deep learning model on the encoded historical query statement;

[0109] As a preferred embodiment of embodiment one, the deep learning model is obtained by training the initial deep learning model on the encoded historical query statement, specifically:

[0110] Obtain the historical query vector from the encoded historical query statement;

[0111] Build an initial deep learning model using two consecutive fully connected layers and a sigmoid function;

[0112] Obtain the historical query vector through the initial deep learning model and train it, and use a single attribute cardinality estimation upper bound loss function to optimize the initial deep learning model during the training process to obtain the deep learning model.

[0113] The initial deep learning model is extended from MSCN. Generally, the initial deep learning model of the present application includes a fully connected layer and a sigmoid activation function, as shown in Figure 2 .

[0114] During the training of the initial deep learning model, the initial learning rate is set to 0.001, reduced by 20% every 5 epochs, and a total of 100 rounds of training are performed. The Adam optimizer is selected for the initial deep learning model training.

[0115] As a preferred embodiment of embodiment one, the single attribute cardinality estimation upper bound loss function is used for optimization during the training of the initial deep learning model, specifically:

[0116] The expression of the single attribute cardinality upper bound loss function is:

[0117]

[0118] In the formula, P represents a predicted value, L represents a label, and Bnd represents a single attribute cardinality upper bound.

[0119] During the training process, whether the predicted value of the cardinality estimation is close to or exceeds the pre-set single attribute cardinality upper bound value is continuously monitored;

[0120] When it is detected that the predicted value of the cardinality estimation exceeds or is expected to exceed the single attribute cardinality upper bound value, a corresponding penalty is automatically imposed by the single attribute cardinality upper bound loss function.

[0121] The definition of the single attribute cardinality upper bound (ACUB) is as follows: given a table T containing u records (tuples) and x attributes θ1, θ2,..., θx; given a query statement Q, the restriction conditions corresponding to the x attributes are γ1, γ2,..., γx, and thus the cardinality upper bound of the attribute θi can be expressed as the proportion of the number of records in the table T that satisfy the restriction condition γi:

[0122] BoundQ(θi) = cardγi(T) / u

[0123] Obviously, the cardinality value of a query statement is the number of records in T that satisfy all the restriction conditions, that is:

[0124] cardQ(T) = cardγ1γ2...γx(T)

[0125] Therefore, even in the best case, the cardinality value of a query statement is less than the minimum value in the single cardinality upper bound, that is:

[0126] BoundQ(T) = cardγ1γ2...γx(T) / u ≤ min(cardγi(T) / u)

[0127] Therefore, the single attribute cardinality upper bound can be used to limit the prediction space of the learning model, thereby improving the accuracy of the model.

[0128] Calculation method: the single attribute cardinality upper bound can be quickly obtained through a histogram;

[0129] (1) Point predicate (=): for this case, since the histogram stores the number of occurrences of each value on a certain attribute, the histogram can be used for indexing to obtain

[0130] (2) Range predicates (≥, ≤, []): For these cases, the concepts of more-than-or-equal-to histogram (MH) and less-than-or-equal-to histogram (LH) are introduced. MH / LH stores the number of records that are greater / less than a value for each value on an attribute. For example, given an attribute, MHT[vi] represents the number of records in table T whose attribute position is greater than or equal to value vi. Thus, the single-attribute upper bound on the cardinality of a range predicate [vi, vj] can be quickly computed according to the following formula:

[0131] Bound[vi, vj](T) = (MHA[vi] + LHA[vj]) / u-1

[0132] S03: If the cardinality estimation result is greater than a preset threshold, the cardinality estimation result is taken as the final cardinality estimation result; otherwise, the accurate cardinality output by the forest structure is taken as the final cardinality estimation result; wherein the forest structure is obtained by constructing a multi-way tree according to database record data.

[0133] As a preferred embodiment of embodiment one, the preset threshold is adaptively calculated by balancing the effects and overheads of a set of historical query statements on the two components of the deep learning model and the forest structure by using a self-defined loss function;

[0134] The formula of the self-defined loss function is:

[0135]

[0136] Wherein, the loss function Cost(η) is composed of two parts of t(η) and mqe(η); t(η) represents the time overhead of the forest structure; mqe(η) represents the average Q error value of the deep learning model and the forest structure in the historical data;

[0137] The minimum value point of the self-defined loss function is found by using the nonlinear least squares method and the sequential least squares algorithm, and the abscissa of the minimum value point is taken as the threshold.

[0138] As a preferred embodiment of embodiment one, the forest structure is obtained by constructing a multi-way tree according to database record data, specifically:

[0139] Obtaining database record data;

[0140] Analyzing the attributes of the database record data, and identifying the number of each attribute;

[0141] A plurality of multi-way trees are built by the number of each attribute value, each tree representing the value domain of an attribute;

[0142] All the built multi-way trees are combined to obtain the forest structure.

[0143] More specifically, the construction steps of the forest structure are:

[0144] Input: n tuples with x attributes, the attribute order order is sorted in ascending order of the number of values on each attribute;

[0145]

[0146]

[0147] That is, given a record (tuple) with x attributes, the values on each attribute are v1, v2,..., vx respectively. The attributes are sorted in ascending order of the number of different values on each attribute to obtain the attribute order order. The tree building process starts from the root node Nroot of the tree (line 21).

[0148] Determine whether the value v1 has been stored in a child node of Nroot (line 6). If yes, add 1 to the statistical variable of the child node Nv1 (line 7); otherwise, add a new node Nv1 as a child node of Nroot, and initialize its statistical variable to 1 (lines 8-10);

[0149] Then, enter the child node Nv1 (line 12), and then determine whether the second value v2 has been stored in a child node of Nv1 (line 6). If yes, add 1 to the statistical variable of the child node Nv2 (line 7); otherwise, add a new node Nv2 as a child node of Nv1, and initialize its statistical variable to 1 (lines 8-10). Repeat this step until all values of the record are stored in the tree structure (line 2). At this point, the first tree structure in the forest structure is built;

[0150] Next, starting from order[2:], the second tree is built according to the above steps for x-1 attributes; starting from order[3:], the third tree is built according to the above steps for x-2 attributes, and so on. The final forest structure contains x tree structures.

[0151] Figure 3A building example of forest structure is given in the following. First, attributes are sorted according to the number of different values in ascending order. Since there is only one value in θ1z, two values in θ0, and three values in θ2, the attribute order is [θ1, θ0, θ2]. Then according to the tree building process, the first tree contains attribute range [θ1, θ0, θ2], the second tree contains range [θ0, θ2], and the third tree is [θ2]. For a given query statement, one can determine which tree to use to find the exact solution according to the predicate occurrence. For example, if the query statement has the restriction condition 'where θ0=a and θ2=b', then one should use the second tree in the forest to find the exact solution. Figure 3

[0152] As a preferred embodiment of embodiment one, the forest structure is used to output the exact cardinality as the final cardinality estimation result, which is specifically:

[0153] Obtaining the encoded query statement to be predicted to obtain a query vector to be predicted;

[0154] Identifying the specified attribute and the corresponding restriction condition in the query vector to be predicted;

[0155] Using a binary search method to find a sub-node range in the multi-way tree forest structure node that satisfies the specified attribute and the corresponding restriction condition;

[0156] Recursively searching in the multi-way tree forest structure node until all leaf nodes or terminal nodes that satisfy the query condition are found;

[0157] Accumulating the statistical variables in the leaf nodes or terminal nodes that satisfy the condition as the final exact cardinality, which is the final cardinality estimation result.

[0158] More specifically, the forest structure uses the following steps to find the exact solution of the query statement:

[0159] Input: a query Q with x attributes, where Q[j][-1] represents the upper bound value of the restriction condition on the jth attribute, and Q[j][-2] represents the lower bound value of the restriction condition on the jth attribute; exact solution forest structure Forest; attribute order order arranged in ascending order according to the number of values appearing on each attribute;

[0160]

[0161]

[0162] ​First, the selection of the tree in the forest structure is made according to the attributes appearing in the query statement (lines 16-19). Starting from the root node of the tree, the binary search is used to find the start node and the last node in the child nodes that satisfy the restriction condition on the first attribute (lines 7, 8). Then, for each of the child nodes, the binary search is continued to find the start node and the last node in their child nodes that satisfy the restriction condition on the second attribute (lines 9, 10). This search process is iterated until the restriction condition is used up (line 3). Finally, the values of the statistical variables stored in all the nodes that satisfy the condition are added up as the final accurate solution (line 4).

[0163] As a preferred embodiment of the first embodiment, when the data of the database records is updated, an update structure is added to the initial deep learning model and the forest structure;

[0164] The initial deep learning network model updates the single-attribute cardinality upper bound, the sample vector, and the label before training. It is noted that only the single-attribute cardinality upper bound and the sample vector information in the input vector will change with the update of the data. Thus, the update of the single-attribute cardinality upper bound can be achieved by first updating the single-attribute histogram and then updating the single-attribute cardinality upper bound through the single-attribute histogram. Further, the reservoir sampling algorithm is used for dynamic update of the sample vector, which can ensure that the probability of each data being selected as a sample is equal in the dynamic data stream. For the label of the training data, since the forest structure can obtain the accurate cardinality value of the query statement, the update can be performed by creating a new forest structure for the updated data. The present application only considers the addition of data, so it is assumed that the forest structure constructed by the added data is Fnew. Given a query statement Q, its accurate cardinality value is Fnew(Q), and obviously labelnew = labelold + Fnew(Q). Thus, the label can be quickly updated. After the training data and the training label are updated, the adam optimizer is used to update the deep learning model, the initial learning rate is set to 0.001, it is reduced by 20% every 2 rounds, and a total of 40 rounds are trained.

[0165] The update of the forest structure is to insert the added database record data into the forest structure one by one, update the statistical variables of each node of the multi-way tree in the forest structure, and then train.

[0166] As a preferred embodiment of the first embodiment, the query statement to be predicted is encoded and input into the deep learning model to obtain the cardinality estimation result, specifically:

[0167] The query statement to be predicted is encoded to form an input vector, and the input vector includes three parts, namely an attribute vector, a predicate vector, and a sample vector;

[0168] The attribute vector uses one-hot encoding to indicate the attribute position;

[0169] The predicate vector is used to encode the predicate type and the limit space;

[0170] The sample vector is used to represent the bitmap of whether the sample data meets the query statement;

[0171] The deep learning model obtains the encoded input vector;

[0172] The cardinality estimation result is obtained by predicting the cardinality estimation value through the deep learning model.

[0173] Figure 4 The input query statement encoding vectorization is shown, wherein the attribute vector is a one-hot vector, which is used to indicate the position of the used attribute; the predicate vector indicates the predicate type (>, <, =, [] and the like) at the position and the range of the predicate limit space; the sample vector is a bitmap, and each bit represents whether the sample data meets the query statement, and 1 means that it meets the query statement, and 0 means that it does not meet the query statement. For multiple tables, there are also a table vector and a connection vector, which are one-hot vectors, the table vector is used to indicate which table is used; the connection vector is used to indicate the connection mode. An example of encoding is shown in Figure 3 For example, the attribute θ0 is the first attribute, and the attribute vector encoding is [1, 0, 0]; the predicate 'θ0≤1.834' does not contain lower limit information of the limit condition, so the first row is encoded as [0, 0, 0], the value part is also 0, and 'UB' is the upper bound value of the single attribute cardinality.

[0174] Embodiment one of the present application combines the learning model and the accurate solution structure into a mixed model for cardinality estimation. By means of the mixed model, the deep learning model is used for estimation for a high-cardinality query statement, and the forest structure is used for accurate solution for a low-cardinality query statement. Compared with the prior art which cannot accurately estimate the cardinality, the mixed model of the present application can accurately estimate the query of different cardinalities.

[0175] Embodiment two

[0176] Please refer to Figure 5 , a cardinality estimation device provided by the embodiment of the present application.

[0177] In the embodiment, the cardinality estimation device includes an acquisition module 10, a prediction module 20, and a comparison module 30.

[0178] The acquisition module 10 is used for a query statement to be predicted;

[0179] The prediction module 20 is configured to input the query statement to be predicted into a deep learning model after encoding, to obtain a cardinality estimation result, wherein the deep learning model is obtained by training an initial deep learning model on the encoded historical query statement.

[0180] As a preferred embodiment of the second embodiment, the deep learning model is obtained by training an initial deep learning model on the encoded historical query statement, specifically as follows:

[0181] The encoded historical query statement is obtained to obtain a historical query vector.

[0182] The initial deep learning model is built using two consecutive fully connected layers and a sigmoid function.

[0183] The historical query vector is obtained by the initial deep learning model and is trained, and a single attribute cardinality estimation upper bound loss function is used to optimize the initial deep learning model during the training process, to obtain the deep learning model.

[0184] The initial deep learning model is extended from MSCN, and generally, the initial deep learning model of the application includes a fully connected layer and a sigmoid activation function, as shown in Figure 2 .

[0185] During the training of the initial deep learning model, the initial learning rate is set to 0.001, reduced by 20% every 5 epochs, and a total of 100 rounds of training are performed. The Adam optimizer is selected for the training of the initial deep learning model.

[0186] As a preferred embodiment of the second embodiment, the single attribute cardinality estimation upper bound loss function is used to optimize the initial deep learning model during the training process, specifically as follows:

[0187] The expression of the single attribute cardinality estimation upper bound loss function is as follows:

[0188]

[0189] In the formula, P represents a predicted value, L represents a label, and Bnd represents a single attribute cardinality upper bound.

[0190] During the training process, it is continuously monitored whether the predicted value of the cardinality estimation is close to or exceeds the pre-set single attribute cardinality upper bound value.

[0191] When it is detected that the predicted value of the cardinality estimation exceeds or is expected to exceed the single attribute cardinality upper bound value, a corresponding penalty is automatically applied by the single attribute cardinality estimation upper bound loss function.

[0192] The definition of attribute cardinality upper-bound (ACUB) is as follows: given a table T containing u tuples and x attributes θ1, θ2,..., θx; given a query Q with restriction conditions γ1, γ2,..., γx on the x attributes, the upper bound of the attribute θi can be expressed as the proportion of the number of tuples in T satisfying the restriction condition γi:

[0193] BoundQ(θi) = cardγi(T) / u

[0194] Obviously, the cardinality of a query is the number of tuples in T satisfying all the restriction conditions, i.e.,

[0195] cardQ(T) = cardγ1γ2...γx(T)

[0196] Therefore, even in the best case, the cardinality of a query is less than the minimum value of the single attribute cardinality upper bound, i.e.,

[0197] BoundQ(T) = cardγ1γ2...γx(T) / u ≤ min(cardγi(T) / u)

[0198] Therefore, the single attribute cardinality upper bound can be used to limit the prediction space of the learning model, thereby improving the accuracy of the model.

[0199] Calculation method: the single attribute cardinality upper bound can be quickly obtained by using a histogram;

[0200] (1) Point predicate (=): for this case, since the histogram stores the number of occurrences of each value in a certain attribute, the index can be obtained by using the histogram;

[0201] (2) Range predicate (≥, ≤, []): for these cases, the concepts of more-than-or-equal-to histogram (MH) and less-than-or-equal-to histogram (LH) are introduced. MH / LH stores the number of records greater / less than a certain value for each value in a certain attribute. For example, given an attribute, MHT[vi] represents the number of records in table T whose attribute position is greater than or equal to the value vi. Therefore, the single attribute cardinality upper bound for the range predicate [vi, vj] can be quickly calculated according to the following formula:

[0202] Bound[vi, vj](T) = (MHA[vi] + LHA[vj]) / u - 1

[0203] The comparison module 30 is configured to compare the cardinality estimation result with a preset threshold value, and if the cardinality estimation result is greater than the preset threshold value, the cardinality estimation result is taken as a final cardinality estimation result; otherwise, an accurate cardinality output by the forest structure is taken as the final cardinality estimation result; wherein the forest structure is obtained by constructing a multi-way tree according to database record data.

[0204] As a preferred embodiment of the second embodiment, the preset threshold value is adaptively calculated by balancing the effects and overheads of a set of historical query statements on the deep learning model and the forest structure through a self-defined loss function;

[0205] The formula of the self-defined loss function is:

[0206]

[0207] Wherein, the loss function Cost(η) is composed of t(η) and mqe(η); t(η) represents the time overhead of the forest structure; mqe(η) represents the average Q error value of the deep learning model and the forest structure in the historical data;

[0208] The minimum value point of the self-defined loss function is found by using the nonlinear least squares method and the sequential least squares algorithm, and the abscissa of the minimum value point is taken as the threshold value.

[0209] As a preferred embodiment of the second embodiment, the forest structure is obtained by constructing a multi-way tree according to database record data, specifically:

[0210] Obtaining database record data;

[0211] Analyzing the attributes of the database record data and identifying the number of each attribute;

[0212] Constructing a plurality of multi-way trees through the number of values of each attribute, and each tree represents the value range of an attribute;

[0213] Combining all the constructed multi-way trees to obtain the forest structure.

[0214] More specifically, the construction steps of the forest structure are:

[0215] Input: n tuples with x attributes, arranged in ascending order according to the number of values appearing on each attribute, and the attribute order order;

[0216]

[0217]

[0218] Given a tuple with x attributes, each attribute has a value v1, v2,..., vx. The attributes are sorted in ascending order of the number of different values in each attribute, and the order of the attributes is obtained. The tree construction starts from the root node Nroot (line 21).

[0219] It is determined whether the value v1 has been stored in a child node of Nroot (line 6). If yes, the statistic variable of the child node Nv1 is incremented (line 7); otherwise, a new node Nv1 is added as a child node of Nroot, and its statistic variable is initialized to 1 (lines 8-10).

[0220] Then, the child node Nv1 is entered (line 12), and it is determined whether the second value v2 has been stored in a child node of Nv1 (line 6). If yes, the statistic variable of the child node Nv2 is incremented (line 7); otherwise, a new node Nv2 is added as a child node of Nv1, and its statistic variable is initialized to 1 (lines 8-10). This step is repeated until all values of the tuple are stored in the tree structure (line 2). At this point, the first tree structure in the forest structure is constructed.

[0221] Next, the second tree is constructed for the x-1 attributes starting from order[2:] according to the above steps, and the third tree is constructed for x-2 attributes starting from order[3:] according to the above steps, and so on. Finally, the forest structure contains x tree structures.

[0222] Figure 3 An example of constructing a forest structure is given in the following. First, the attributes are sorted in ascending order of the number of different values in each attribute. Since there is only one value in θ1z, there are two values in θ0, and there are three values in θ2, the attribute order is [θ1, θ0, θ2]. Then, according to the above tree construction process, the first tree contains the attribute range [θ1, θ0, θ2], the second tree contains the range [θ0, θ2], and the third tree is [θ2]. For a given query statement, it can be determined which tree to use for accurate solution according to the occurrence of its predicates. For example, the query statement has the restriction condition 'where θ0 = a and θ2 = b', then the second tree in should be used for accurate solution. Figure 3

[0223] As a preferred embodiment of the second embodiment, the forest structure is used to output the accurate cardinality as the final cardinality estimation result, specifically:

[0224] The encoded query statement to be predicted is obtained to obtain the query vector to be predicted.​

[0225] identifying the specified attribute in the query vector to be predicted and the corresponding limit condition;

[0226] finding the sub-node range in the multi-way tree forest structure node that satisfies the specified attribute and the corresponding limit condition using the binary search method;

[0227] recursively searching in the multi-way tree forest structure node until all leaf nodes or terminal nodes that satisfy the query condition are found;

[0228] accumulating the statistical variables in the leaf nodes or terminal nodes that satisfy the condition as the final accurate cardinality, which is the final cardinality estimation result.

[0229] More specifically, the solving steps of the forest structure for the accurate solution of the query statement are as follows:

[0230] Input: a query Q, the number of attributes of which is x, wherein Q[j][-1] represents the upper limit value of the limit condition on the jth attribute, and Q[j][-2] represents the lower limit value of the limit condition on the jth attribute; an accurate solution solving forest structure Forest; an attribute order order arranged in ascending order according to the number of values appearing on each attribute;

[0231]

[0232]

[0233] First, the trees in the forest structure are selected according to the attributes appearing in the query statement (lines 16-19). Starting from the root node of the tree, binary search is used to find the start node and the last node in the child nodes that satisfy the limit condition on the first attribute (lines 7, 8). Then, for each of the child nodes, binary search is continued on their child nodes according to the limit condition on the second attribute to find the start node and the last node in the second layer of child nodes that satisfy the limit condition on the second attribute (lines 9, 10). This search process is iterated until the limit condition is used up (line 3). Finally, the values of the statistical variables stored in all the nodes that satisfy the condition are accumulated as the final accurate solution (line 4).

[0234] As a preferred embodiment of Embodiment Two, when the data of the database records is updated, an update structure is added to the initial deep learning model and the forest structure;

[0235] The initial deep learning network model updates the single attribute cardinality upper bound, the sample vector and the label before training. It is noted that only the single attribute cardinality upper bound and the sample vector information in the input vector will change with the update of data. In this way, the update of the single attribute cardinality upper bound can be realized by first updating the one-dimension histogram and then updating the single attribute cardinality upper bound through the one-dimension histogram. In addition, the reservoir sampling algorithm is used for dynamic update of the sample vector, which can ensure that the probability of each data being selected as a sample is equal in the dynamic data stream. For the label of the training data, since the forest structure can obtain the accurate cardinality value of the query statement, a new forest structure can be created for the updated data to update the label. The present application only considers the addition operation of data, so it is assumed that the forest structure constructed by the added data is Fnew, and given a query statement Q, the accurate cardinality value of which is Fnew(Q), obviously labelnew=labelold+Fnew(Q). In this way, the label can be quickly updated. After updating the training data and the training label, the adam optimizer is used to update the deep learning model, the initial learning rate is set to 0.001, it is reduced by 20% every 2 rounds, and a total of 40 rounds are trained.

[0236] The update of the forest structure is to insert the added database record data into the forest structure one by one, update the statistical variables of each node of the multi-way tree in the forest structure in turn, and then train.

[0237] As a preferred embodiment of embodiment two, the query statement to be predicted is encoded and input into the deep learning model to obtain the cardinality estimation result, specifically:

[0238] The query statement to be predicted is encoded to form an input vector, and the input vector includes three parts, which are an attribute vector, a predicate vector and a sample vector;

[0239] The attribute vector uses one-hot encoding to indicate the attribute position;

[0240] The predicate vector is used to encode the predicate type and the restriction space;

[0241] The sample vector is used to represent the bitmap of whether the sample data meets the query statement;

[0242] The deep learning model obtains the encoded input vector;

[0243] The deep learning model predicts the cardinality estimation value to obtain the cardinality estimation result.

[0244] Figure 4The input query statement encoding vectorization is shown, in which the attribute vector is a one-hot vector, indicating the position of the used attribute; the predicate vector indicates the predicate type (>, <, =, [] and the like) at the position and the range of the predicate's limit space; the sample vector is a bitmap, each bit indicating whether the sample data meets the query statement, 1 for meeting and 0 for not meeting. For multiple tables, there are also a table vector and a connection vector, which are one-hot vectors. The table vector is used to indicate which table is used; the connection vector is used to indicate the connection method. An example of encoding is shown in Figure 3 For example, the attribute θ0 is the first attribute, and its attribute vector is encoded as [1, 0, 0]; the predicate 'θ0≤1.834' does not contain lower bound information of the limit condition, so the first row is encoded as [0, 0, 0], the value part is also 0, and 'UB' is the upper bound value of the single attribute base.

[0245] The second embodiment of the present application uses three modules to work together to better discover and handle problems in the base estimation device. The device can comprehensively use historical and real-time data, quickly generate prediction results through the cooperative work of the deep learning network and the forest structure, and make intelligent decisions according to the dynamically determined threshold, to ensure the optimality of the output results.

[0246] Embodiment three:

[0247] The embodiment of the present application provides a computer readable storage medium, which comprises a stored computer program, wherein when the computer program runs, the device where the computer readable storage medium is located executes the base estimation method.

[0248] The base number estimation method can be stored in a computer readable storage medium if it is implemented in the form of a software function unit and used as an independent product. Based on this understanding, all or part of the processes in the above-mentioned embodiment methods can also be completed by a computer program instructing related hardware. The computer program can be stored in a computer readable storage medium. When the computer program is executed by a processor, the steps of the above-mentioned various method embodiments can be implemented. The computer program includes computer program code, which can be in the form of source code, object code, executable files or some intermediate forms, etc. The computer readable medium can include any entity or device capable of carrying the computer program code, recording medium, U disk, mobile hard disk, magnetic disk, optical disk, computer memory, read-only memory (ROM), random access memory (RAM), electric carrier signal, telecommunication signal and software distribution medium, etc.

[0249] The above specific embodiments further illustrate the purpose, technical solutions and beneficial effects of the present application. It should be understood that the above description is only for specific embodiments of the present application and is not intended to limit the protection scope of the present application. It is particularly pointed out that any modification, equivalent replacement, improvement, etc. made by those skilled in the art within the spirit and principles of the present application shall be included in the protection scope of the present application.

Claims

1. A method of estimating a cardinality, characterized by, The method comprises the following steps: acquiring a query statement to be predicted; encoding the query statement to be predicted and inputting the encoded query statement to a deep learning model to obtain a cardinality estimation result, wherein the deep learning model is obtained by training an initial deep learning model based on encoded historical query statements, and the training process comprises the following steps: acquiring the encoded historical query statements to obtain historical query vectors; building the initial deep learning model by using two successive fully connected layers and a sigmoid function; training the historical query vectors by using the initial deep learning model, and optimizing the initial deep learning model by using a single-attribute cardinality estimation upper bound loss function during the training process to obtain the deep learning model; the optimization by using the single-attribute cardinality estimation upper bound loss function during the training process comprises the following steps: the expression of the single-attribute cardinality estimation upper bound loss function is as follows: wherein P represents a predicted value, L represents a label, and Bnd represents a single-attribute cardinality upper bound; continuously monitoring whether the predicted value of the cardinality estimation is close to or exceeds a pre-set single-attribute cardinality upper bound value during the training process; when it is detected that the predicted value of the cardinality estimation exceeds or is expected to exceed the single-attribute cardinality upper bound value, automatically applying a corresponding penalty to the deep learning model by using the single-attribute cardinality estimation upper bound loss function; if the cardinality estimation result is greater than a pre-set threshold, taking the cardinality estimation result as a final cardinality estimation result; otherwise, taking an accurate cardinality output by a forest structure as the final cardinality estimation result, wherein the forest structure is obtained by constructing a multi-way tree based on database record data.

2. The method of estimating a population size according to claim 1, wherein, the forest structure is obtained by constructing a multi-way tree based on database record data, and the construction comprises the following steps: acquiring the database record data; analyzing attributes of the database record data and identifying the number of each attribute; constructing a plurality of multi-way trees based on the number of each attribute value, and each tree represents a value range of an attribute; combining all the constructed multi-way trees to obtain the forest structure.

3. The method of claim 1, wherein, the accurate cardinality output by the forest structure is taken as the final cardinality estimation result, and the process comprises the following steps: acquiring the encoded query statement to be predicted to obtain a query vector to be predicted; identifying a specified attribute and a corresponding restriction condition in the query vector to be predicted; finding a sub-node range satisfying the specified attribute and the corresponding restriction condition in the multi-way tree forest structure by using a binary search method; recursively searching in the multi-way tree forest structure until all leaf nodes or terminal nodes satisfying the query condition are found; accumulating statistical variables in the leaf nodes or the terminal nodes satisfying the condition as a final accurate cardinality, and the accurate cardinality is taken as the final cardinality estimation result.

4. The method of claim 1, wherein, The method further comprises the following steps: when the database record data is updated, adding an update structure to the initial deep learning model and the forest structure; updating a single-attribute cardinality upper bound, a sample vector and a label before training the initial deep learning network model; inserting the newly added database record data into the forest structure one by one, updating statistical variables of each node of the multi-way tree in the forest structure, and then training the forest structure.

5. The method of estimating a population size according to claim 1, wherein, the encoding of the query statement to be predicted and the inputting of the encoded query statement to the deep learning model to obtain the cardinality estimation result comprise the following steps: encoding the query statement to be predicted to form an input vector, and the input vector comprises three parts, i.e., an attribute vector, a predicate vector and a sample vector; The attribute vector indicates attribute positions using one-hot encoding; The predicate vector is used to encode predicate types and restriction spaces; The sample vector is used to represent a bitmap of whether sample data meets the query statement; The deep learning model obtains the encoded input vector; The cardinality estimation result is obtained by predicting the cardinality estimation value through the deep learning model.

6. A radix estimation device characterized by comprising: The method comprises an obtaining module, a predicting module, and a comparing module; The obtaining module is used to obtain a query statement to be predicted; The predicting module is used to input the query statement to be predicted into a deep learning model after encoding to obtain a cardinality estimation result, wherein the deep learning model is obtained by training an initial deep learning model based on encoded historical query statements, and the deep learning model is obtained by: Obtaining the historical query vector based on the encoded historical query statement; Building the initial deep learning model using two consecutive fully connected layers and a sigmoid function; Obtaining the historical query vector through the initial deep learning model and training the historical query vector, and optimizing the initial deep learning model using a single attribute cardinality estimation upper bound loss function during the training process to obtain the deep learning model; The optimization using the single attribute cardinality estimation upper bound loss function during the training process is specifically: The expression of the single attribute cardinality estimation upper bound loss function is: In the formula, P represents a predicted value, L represents a label, and Bnd represents a single attribute cardinality upper bound; During the training process, it is continuously monitored whether the predicted value of the cardinality estimation is close to or exceeds a pre-set single attribute cardinality upper bound value; When it is detected that the predicted value of the cardinality estimation exceeds or is expected to exceed the single attribute cardinality upper bound value, a corresponding penalty is automatically applied through the single attribute cardinality estimation upper bound loss function; The comparing module is used to compare the cardinality estimation result with a pre-set threshold value, and if the cardinality estimation result is greater than the pre-set threshold value, the cardinality estimation result is taken as a final cardinality estimation result; otherwise, an accurate cardinality output by a forest structure is taken as the final cardinality estimation result, wherein the forest structure is obtained by constructing a multi-way tree based on database record data.

7. A device for estimating a radix according to claim 6, wherein The forest structure is obtained by constructing a multi-way tree based on database record data, and the construction is specifically: Obtaining the database record data; Analyzing attributes of the database record data and identifying the number of each attribute; Constructing a plurality of multi-way trees based on the number of each attribute value, and each tree represents a value range of an attribute; Combining all the constructed multi-way trees to obtain the forest structure; Finding a minimum value point of a self-defined loss function using a nonlinear least square method and a sequential least square algorithm, and taking the abscissa of the minimum value point as the threshold value.

8. The device for estimating a radix according to claim 6, wherein The accurate cardinality output by the forest structure is taken as the final cardinality estimation result, and the process is specifically: Obtaining a query vector to be predicted based on the encoded query statement to be predicted; Identifying specified attributes and corresponding restriction conditions in the query vector to be predicted; Finding a sub-node range that meets the specified attributes and corresponding restriction conditions in the multi-way tree forest structure node using a binary search method; Recursively searching in the multi-way tree forest structure node until all leaf nodes or terminal nodes that meet the query conditions are found; Accumulating statistical variables in the leaf nodes or terminal nodes that meet the conditions as a final accurate cardinality, and taking the accurate cardinality as the final cardinality estimation result.

9. The device for estimating a radix according to claim 6, wherein The method further comprises: When the data of the database record is updated, an update structure is added to the initial deep learning model and the forest structure; Before training, the initial deep learning network model updates the single attribute cardinality upper bound, the sample vector and the label; The newly added database record data is inserted into the forest structure one by one, and the statistical variables of each node of the multi-way tree in the forest structure are updated in turn and then trained.

10. The device for estimating a radix according to claim 6, wherein The query statement to be predicted is encoded and input into the deep learning model to obtain a cardinality estimation result, and the specific process is as follows: The query statement to be predicted is encoded to form an input vector, and the input vector includes three parts, which are an attribute vector, a predicate vector and a sample vector; The attribute vector uses one-hot encoding to indicate the attribute position; The predicate vector is used to encode the predicate type and the restriction space; The sample vector is used to represent the bit map of whether the sample data meets the query statement; The deep learning model acquires the encoded input vector; The deep learning model predicts the cardinality estimation value to obtain the cardinality estimation result.

11. A computer readable storage medium characterized by, The computer readable storage medium includes a stored computer program, wherein when the computer program runs, the computer readable storage medium controls the device where the computer readable storage medium is located to execute the cardinality estimation method in any one of claims 1 to 5.

Images