Maintenance method and device for materialized view, equipment, storage medium and program product

Abstract

The invention provides a materialized view maintenance method and device, equipment, a storage medium and a program product, and relates to the technical field of communication, the method comprises: obtaining historical data and an initial MV set, the initial MV set comprising a plurality of initial MV nodes, the historical data comprising N historical query instructions and a maximum first benefit parameter corresponding to each historical query instruction; under the condition that the sum of the maximum first benefit parameters corresponding to the N historical query instructions is smaller than a set benefit threshold value, creating an empty initial MV subset; calculating a second benefit parameter corresponding to each initial MV node in the initial MV set; adding a plurality of target MV nodes into the initial MV subset based on a second benefit parameter to obtain a target MV subset; and setting the target MV subset as an optimized target MV set. According to the method, the query time can be shortened, and the query accuracy is improved.

Description

Technical Field

[0001] The present invention relates to the field of communication technologies, and in particular, to a method, apparatus, device, storage medium, and program product for maintaining a materialized view. Background Art

[0002] Query rewriting technology is one of the important technologies for processing query instructions. By rewriting query instructions, query time can be reduced and query efficiency can be improved. In related technologies, query rewriting is usually achieved by querying a system configuration of a materialized view (MV) set. However, in related technologies, the MV is a pre-configured static set and does not change during the query rewriting process. During the query process, the load faced by the query system changes, and the reduced query time also changes. However, the static MV set cannot handle the changing situation, resulting in a low query accuracy rate.

[0003] It can be seen that in related technologies, there is a problem that the static MV set cannot handle the changing situation, resulting in a low query accuracy rate. Summary of the Invention

[0004] Embodiments of the present invention provide a method, apparatus, device, storage medium, and program product for maintaining a materialized view to solve the problem in the prior art that the static MV set cannot handle the changing situation, resulting in a low query accuracy rate.

[0005] To solve the above problems, the present invention is implemented as follows: In a first aspect, an embodiment of the present invention provides a method for maintaining a materialized view, including: Obtaining historical data and an initial MV set, where the initial MV set includes a plurality of initial MV nodes, the historical data includes N historical query instructions, and a maximum first benefit parameter corresponding to each historical query instruction, the first benefit parameter is used to characterize the reduction in execution time after rewriting, and N is a positive integer greater than 1; Creating an empty initial MV subset when the sum of the maximum first benefit parameters corresponding to the N historical query instructions is less than a set benefit threshold; Calculating a second benefit parameter corresponding to each initial MV node in the initial MV set; Adding a plurality of target MV nodes to the initial MV subset based on the second benefit parameter to obtain a target MV subset, where the plurality of target MV nodes are nodes in the plurality of initial MV nodes, and the second benefit parameter corresponding to the target MV node is greater than the second benefit parameter corresponding to other MV nodes in the initial MV set; Setting the target MV subset as the optimized target MV set.

[0006] Second aspect, an embodiment of the present invention further provides a materialized view maintenance device, including: An acquisition module, configured to acquire historical data and an initial MV set, the initial MV set includes a plurality of initial MV nodes, the historical data includes N historical query instructions, and the maximum first benefit parameter corresponding to each historical query instruction, the first benefit parameter is used to characterize the reduction of the execution time after rewriting, and N is a positive integer greater than 1; A creation module, configured to create an empty initial MV subset when the sum of the maximum first benefit parameters corresponding to the N historical query instructions is less than a set benefit threshold; A first calculation module, configured to calculate a second benefit parameter corresponding to each initial MV node in the initial MV set; An addition module, configured to add a plurality of target MV nodes to the initial MV subset based on the second benefit parameter to obtain a target MV subset, the plurality of target MV nodes are nodes in the plurality of initial MV nodes, and the second benefit parameter corresponding to the target MV node is greater than the second benefit parameters corresponding to other MV nodes in the initial MV set; A setting module, configured to set the target MV subset as an optimized target MV set.

[0007] Third aspect, an embodiment of the present invention further provides an electronic device, including a transceiver and a processor, The transceiver is configured to acquire historical data and an initial MV set, the initial MV set includes a plurality of initial MV nodes, the historical data includes N historical query instructions, and the maximum first benefit parameter corresponding to each historical query instruction, the first benefit parameter is used to characterize the reduction of the execution time after rewriting, and N is a positive integer greater than 1; The processor is configured to create an empty initial MV subset when the sum of the maximum first benefit parameters corresponding to the N historical query instructions is less than a set benefit threshold; The processor is further configured to calculate a second benefit parameter corresponding to each initial MV node in the initial MV set; The processor is further configured to add a plurality of target MV nodes to the initial MV subset based on the second benefit parameter to obtain a target MV subset, the plurality of target MV nodes are nodes in the plurality of initial MV nodes, and the second benefit parameter corresponding to the target MV node is greater than the second benefit parameters corresponding to other MV nodes in the initial MV set; The processor is further configured to set the target MV subset as an optimized target MV set.

[0008] Fourth aspect, an embodiment of the present invention provides an electronic device, including: a processor, a memory, and a program stored on the memory and executable on the processor. When the program is executed by the processor, the steps of the materialized view maintenance method described in the first aspect above are implemented.

[0009] Fifth aspect, an embodiment of the present invention provides a computer-readable storage medium, on which a computer program is stored. When the computer program is executed by a processor, the steps of the materialized view maintenance method described in the first aspect above are implemented.

[0010] Sixth aspect, the present invention further provides a computer program product, including computer instructions. When the computer instructions are executed by a processor, the steps in the materialized view maintenance method described in the first aspect above are implemented.

[0011] In an embodiment of the present invention, historical data and an initial MV set are obtained. The initial MV set includes a plurality of initial MV nodes. The historical data includes N historical query instructions, and the maximum first benefit parameter corresponding to each historical query instruction. The first benefit parameter is used to characterize the reduction in execution time after rewriting. N is a positive integer greater than 1. In the case where the sum of the maximum first benefit parameters corresponding to the N historical query instructions is less than a set benefit threshold, an empty initial MV subset is created. The second benefit parameter corresponding to each initial MV node in the initial MV set is calculated. Based on the second benefit parameter, a plurality of target MV nodes are added to the initial MV subset to obtain a target MV subset. The plurality of target MV nodes are nodes among the plurality of initial MV nodes, and the second benefit parameter corresponding to the target MV node is greater than the second benefit parameter corresponding to other MV nodes in the initial MV set. The target MV subset is set as the optimized target MV set. In this way, by calculating the second benefit parameter corresponding to each initial MV node, and then adding a plurality of target MV nodes with benefit parameters greater than other nodes to the initial MV subset to obtain a target MV subset, the execution time of each query rewritten by each MV node in the target MV subset can be effectively reduced. At the same time, by using the benefit parameter to update the MV set, the MV set can adapt to the change of the load. Therefore, the query accuracy is improved. Description of the Drawings

[0012] To more clearly illustrate the technical solutions of the embodiments of the present invention, the following will briefly introduce the drawings required for the description of the embodiments of the present invention. Obviously, the drawings in the following description are only some embodiments of the present invention. For those of ordinary skill in the art, other drawings can be obtained based on these drawings without creative efforts.

[0013] Figure 1It is a flowchart of a method for maintaining a materialized view provided by an embodiment of the present invention; Figure 2 It is a schematic diagram of MV set maintenance provided by an embodiment of the present invention; Figure 3 It is a schematic diagram of a node including parameters provided by an embodiment of the present invention; Figure 4 It is a schematic diagram of model training provided by an embodiment of the present invention; Figure 5 It is a structural diagram of a device for maintaining a materialized view provided by an embodiment of the present invention; Figure 6 It is a structural diagram of an electronic device provided by an embodiment of the present invention. Detailed implementation manners

[0014] Next, the technical solutions in the embodiments of the present invention will be clearly and completely described in conjunction with the accompanying drawings in the embodiments of the present invention. Obviously, the described embodiments are some, but not all, of the embodiments of the present invention. All other embodiments obtained by those of ordinary skill in the art based on the embodiments of the present invention without creative efforts shall fall within the protection scope of the present invention.

[0015] The embodiments of the present invention provide a method, device, equipment, storage medium and program product for maintaining a materialized view to solve the problem in the related art that the static MV set cannot cope with changes, resulting in a low accuracy rate of queries.

[0016] Please refer to Figure 1 , Figure 1 which is a flowchart of a method for maintaining a materialized view provided by an embodiment of the present invention. As Figure 1 shown, the method includes the following steps: Step 101, obtain historical data and an initial MV set. The initial MV set includes multiple initial MV nodes. The historical data includes N historical query instructions and the maximum first benefit parameter corresponding to each historical query instruction. The first benefit parameter is used to characterize the reduction in execution time after rewriting, and N is a positive integer greater than 1.

[0017] The above historical data is data obtained by rewriting historical query instructions based on the initial MV set. It should be noted that a materialized view is a special physical table that pre-computes and stores query results. When executing relevant queries, the pre-computed results (i.e., query rewriting) can be automatically reused to improve query performance. When receiving a historical query instruction, the initial MV set is used to achieve the rewriting of the historical query instruction.

[0018] Among them, after rewriting the query instruction, the execution time of the query instruction can be effectively reduced, which is specifically represented by the benefit parameter. After rewriting the historical query instruction based on the initial MV nodes in the initial MV set, the execution time of the requirement before rewriting and the execution time of the requirement after rewriting can be estimated, and the first benefit parameter is calculated through the execution time of the requirement before rewriting and the execution time of the requirement after rewriting. In some embodiments, the first benefit parameter can be specifically calculated by a Graph Neural Network (GNN) MV model.

[0019] The above initial MV nodes are pre-configured MV nodes, and the initial MV nodes are configured with pre-computed results. Different initial MV nodes can be used to rewrite different query instructions to obtain pre-computed results.

[0020] Step 102: When the sum of the maximum first benefit parameters corresponding to the N historical query instructions is less than the set benefit threshold, create an empty initial MV subset.

[0021] The above set benefit threshold is used to determine whether the initial MV set needs to be maintained. Among them, when the sum of the maximum first benefit parameters corresponding to the N historical query instructions is greater than or equal to the set benefit threshold, it is considered that the initial MV set can effectively perform query rewriting at this time, reducing the execution time of the query and no maintenance is required; while when the sum of the maximum first benefit parameters corresponding to the N historical query instructions is less than the set benefit threshold, it is considered that the reduction in the execution time of the query after query rewriting by the initial MV set is less at this time and the expected effect cannot be achieved. At this time, the initial MV set needs to be maintained so that the optimized MV set after maintenance can more effectively reduce the execution time of the query.

[0022] The above initial MV subset is a blank subset, that is, there are no MV nodes in the subset, and new MV nodes need to be added to the initial MV subset to obtain an optimized MV set.

[0023] In some embodiments, when adding new MV nodes to the initial MV subset, the cost and benefit need to be considered. Among them, the cost can be the sum of the time for the execution view and adding MV nodes to the subset, and the benefit can be the reduction in the execution time after query rewriting between the added MV nodes. The MV nodes added to the initial MV subset are determined by the cost and benefit.

[0024] Step 103: Calculate the second benefit parameter corresponding to each initial MV node in the initial MV set.

[0025] The above second benefit parameter is used to characterize the benefit after each initial MV node is added to the initial MV subset. The calculation methods of the second benefit parameter and the first benefit parameter are the same. The initial MV nodes to be added are confirmed through the second benefit parameter, so that the MV nodes can effectively perform query rewriting and reduce the execution time.

[0026] Step 104: Add a plurality of target MV nodes to the initial MV subset based on the second benefit parameter to obtain a target MV subset. The plurality of target MV nodes are nodes among the plurality of initial MV nodes, and the second benefit parameter corresponding to the target MV node is greater than the second benefit parameters corresponding to other MV nodes in the initial MV set.

[0027] In some embodiments, adding a plurality of target MV nodes to the initial MV subset based on the second benefit parameter to obtain a target MV subset may be adding a preset number of target MV nodes to the initial MV subset, that is, adding a preset number of target MV nodes to the initial MV subset based on the second benefit parameter to obtain a target MV subset.

[0028] In some embodiments, adding a plurality of target MV nodes to the initial MV subset based on the second benefit parameter to obtain a target MV subset may also be adding target MV nodes to the initial MV subset in sequence according to the magnitude order of the second benefit until the sum of the second benefit parameters corresponding to all target MV nodes in the initial MV subset is greater than a set threshold.

[0029] Step 105: Set the target MV subset as the optimized target MV set.

[0030] In an embodiment of the present invention, historical data and an initial MV set are obtained. The initial MV set includes a plurality of initial MV nodes. The historical data includes N historical query instructions and the maximum first benefit parameter corresponding to each historical query instruction. The first benefit parameter is used to characterize the reduction in execution time after rewriting. N is a positive integer greater than 1. When the sum of the maximum first benefit parameters corresponding to the N historical query instructions is less than a set benefit threshold, an empty initial MV subset is created. The second benefit parameter corresponding to each initial MV node in the initial MV set is calculated. Based on the second benefit parameter, a plurality of target MV nodes are added to the initial MV subset to obtain a target MV subset. The plurality of target MV nodes are nodes among the plurality of initial MV nodes, and the second benefit parameter corresponding to the target MV node is greater than the second benefit parameter corresponding to other MV nodes in the initial MV set. The target MV subset is set as the optimized target MV set. In this way, by calculating the second benefit parameter corresponding to each initial MV node and then adding a plurality of target MV nodes with a benefit parameter greater than that of other nodes to the initial MV subset to obtain a target MV subset, the execution time of the query can be effectively reduced when each MV node in the target MV subset performs query rewriting. At the same time, the MV set is updated through the benefit parameter, so that the MV set can adapt to the change of the load, thereby improving the query accuracy.

[0031] In one embodiment, the initial MV set further includes a plurality of query nodes. The calculating the second benefit parameter corresponding to each initial MV node in the initial MV set includes: Calculating the benefit parameter between a first MV node and the plurality of query nodes, where the first MV node is one of the plurality of initial MV nodes; Setting the maximum benefit parameter among the benefit parameters between the first MV node and the plurality of query nodes as the second benefit parameter.

[0032] In an embodiment of the present invention, the benefit parameter between a first MV node and the plurality of query nodes is calculated, where the first MV node is one of the plurality of initial MV nodes; the maximum benefit parameter among the benefit parameters between the first MV node and the plurality of query nodes is set as the second benefit parameter, so as to calculate the second benefit parameter corresponding to each initial MV node.

[0033] Further, as Figure 2 shown, after receiving a historical query instruction, a query plan tree is generated, and the initial MV node corresponding to the query instruction is queried through a graph neural network GNN, and the query is rewritten through the initial MV node; and the initial MV set is maintained to obtain a target MV set, so as to realize query rewriting through the target MV set.

[0034] Exemplarily, when a new query instruction is received, the current query graph becomes , which is the subgraph corresponding to the new query instruction. To generate an optimization within a set benefit threshold , the target MV set , GnnMV builds a bipartite graph, where the nodes on one side come from , and the nodes on the other side come from the initial MV set V. Each edge represents the benefit from to (i.e., the second benefit parameter), and is labeled with .

[0035] In some embodiments, the subgraph contains , and the set of nodes (denoted as ) whose distance from at least one node in does not exceed d. Considering the number of iterations K of feature propagation again, only the subgraph containing needs to be extracted, and the features of can be fully propagated to within K iterations.

[0036] In some embodiments, to consider the view materialization cost, for creating a new view, the view materialization cost is subtracted from the benefit of creating the view.

[0037] It should be noted that some MV nodes in the initial MV set conflict with each other. Therefore, when rewriting the query, a matrix is used to represent the relationship between views, where indicates that the initial MV node conflicts (does not conflict) with the initial MV node in the query instruction, and is used to indicate whether to select the initial MV node to rewrite the query instruction . In the present invention, the space of V is represented as |V|. To obtain the maximum total benefit, it is modeled as the following integer programming problem: , , .

[0038] Since the determination of the target MV nodes in the present invention refers to a class of computational problems to which all non-deterministic polynomial (NP) problems can be reduced in polynomial time, a greedy algorithm is adopted in the present invention to select the target MV set with an approximation ratio of 2. The target MV nodes in are represented as , where . If , then . The determination of the second benefit parameter is obtained in the above manner. Therefore, it can be deduced that , where W m in the formula is the query instruction.

[0039] Specifically, the initial MV subset is initialized as an empty set . At this time, the second benefit parameters of different initial MV nodes in the initial MV set are . Then, the initial MV node corresponding to the largest second benefit parameter is selected as the target MV node and added to the initial MV subset , that is, . At the same time, the second benefit parameters of the other initial MV nodes are respectively updated to the benefit parameter corresponding to the query instruction and the benefit parameter corresponding to the MV node, that is, , . The next target MV node , ,... is repeatedly selected until the sum of the second benefit parameters corresponding to the selected target MV nodes is greater than the preset benefit threshold . At this time, the initial MV subset is the target MV set. Finally, the target MV set is output to implement the maintenance of the initial MV set.

[0040] In one embodiment, after setting the target MV subset as the optimized target MV set, the method further includes: Receiving a query instruction; Extracting the first feature corresponding to the query instruction, and the second feature corresponding to each MV node included in the target MV set; Calculating the third benefit parameter between the second feature corresponding to each MV node and the first feature based on a preset graph neural network model; Rewrite the query instruction based on the MV node with the largest third benefit parameter among the multiple MV nodes to obtain a target instruction.

[0041] In an embodiment of the present invention, a query instruction is received; a first feature corresponding to the query instruction and a second feature corresponding to each MV node among the multiple MV nodes included in the target MV set are extracted; a third benefit parameter between the second feature corresponding to each MV node and the first feature is calculated based on a preset graph neural network model; the query instruction is rewritten based on the MV node with the largest third benefit parameter among the multiple MV nodes to obtain a target instruction. In this way, the query instruction is rewritten through the maintained target MV set to obtain a target instruction, which can better reduce the execution time compared to rewriting the query instruction through the initial MV set.

[0042] Among them, the above-mentioned first feature is the feature obtained by extracting the query instruction, and the second feature is the feature obtained by extracting the MV node. It should be noted that after receiving a new query instruction, a query node corresponding to the new query instruction is added to the existing query graph, and then the MV node for query rewriting of the query node is determined. The existing query graph includes MV nodes and query nodes, where the MV nodes are the nodes in the target MV set and the query nodes are the nodes corresponding to the query instructions. After adding the query node, the MV node for query rewriting of the query node is determined according to the first feature and the second feature.

[0043] As Figure 3 shown, the target node (i.e., the query node or the MV node) includes parameters of three parts: operator type, metadata, and predicate. The features are determined through the parameters of the three parts, so that the features can characterize the characteristics of the operator type, metadata, and predicate in three aspects. For example, a certain node is associated with the seq-scan operator, and the main table (author) with the scan predicate a.age>30 is scanned based on this operator. The operator type seq-scan is encoded as a one-hot vector , where the corresponding position of seq-scan has a value of 1. The predicate a.age>30 can be represented as an expression tree, where the root node > has two child nodes a.age and 30. The expression tree is encoded as an embedding vector through a ree-long short-term memory (LSTM) model . The metadata includes parameters such as author, age column, scan cost row, and width, and is encoded into a vector Among them. By encoding features in this way, the extracted node corresponding features (the first feature or the second feature) include a one-hot vector, the corresponding position of column a.age has a value of 1, and other statistical information.

[0044] Calculating the third benefit parameter between the second feature corresponding to each MV node and the first feature based on the preset graph neural network model specifically means calculating the query execution time before query rewriting and the query execution time after query rewriting based on the preset graph neural network model, and calculating the third benefit parameter through the query execution time before rewriting and the query execution time after query rewriting, which is specifically represented by the following formula: , In the formula is the third benefit parameter, is the query execution time of query q, is the query execution time after query q is rewritten. It should be noted that the input of the preset graph neural network model is the first feature and the second feature, and the output is the third benefit parameter, so that the third benefit parameter is calculated through the preset graph neural network model.

[0045] In addition, the first benefit parameter and the second benefit parameter can be obtained by referring to the calculation method of the third benefit parameter, which will not be elaborated here.

[0046] In one embodiment, the preset graph neural network model is obtained in the following manner: Obtain sample data, where the sample data includes a sample query graph and a sample benefit parameter. The sample query graph includes multiple sample query nodes and multiple sample MV nodes, as well as the association relationship between different sample query nodes and different sample MV nodes. The sample benefit parameter is the reduced execution time after rewriting the instruction corresponding to the sample query node according to the sample MV node; Perform feature encoding on the multiple sample query nodes and the multiple sample MV nodes respectively to obtain the first sample features corresponding to the multiple sample query nodes and the second sample features corresponding to the multiple sample MV nodes; Train the initial graph neural network model based on the first sample features corresponding to the multiple sample query nodes, the second sample features corresponding to the multiple sample MV nodes, and the sample benefit parameter to obtain the preset graph neural network model.

[0047] The above preset graph neural network model (GnnMV) is trained according to sample data. The sample data can be historical data collected and preprocessed. By constructing a query graph based on the sample data to train the initial graph neural network model, the preset graph neural network model (GnnMV) is obtained. Specifically, asFigure 4 As shown, after obtaining the sample data, feature encoding is performed on the sample data, and a query graph is constructed. The initial graph neural network model is trained through the query graph to obtain a preset graph neural network model.

[0048] The above sample data can be obtained specifically in the following way: First, construct a query graph corresponding to the set of historical query instructions and sample a group of MV nodes (denoted as ) in the graph and specifically characterize them to cover different execution delays and sizes. For each query instruction q ∈ , and the corresponding v ∈ used to optimize q, calculate the benefit parameter by executing q with / without the MV node v, and regard this pair as a training instance. Among them, the benefit parameter may be negative because MV is not always beneficial to the query. The sample data includes positive samples and negative samples, and the ratio of positive samples to negative samples is 8:2. The model training is achieved through positive samples and negative samples.

[0049] Among them, in one embodiment, feature encoding is respectively performed on the multiple sample query nodes and the multiple sample MV nodes to obtain a first sample feature corresponding to the multiple sample query nodes and a second sample feature corresponding to the multiple sample MV nodes, including: Perform feature encoding on the target node to obtain a first target intermediate feature, where the target node is a node among the multiple sample query nodes or the multiple sample MV nodes; Determine the adjacent nodes of the target node based on the association relationship; Obtain the propagation feature corresponding to the second target intermediate feature of the adjacent node, where the second target intermediate feature is the feature obtained by performing feature encoding on the adjacent node; Aggregate the first target intermediate feature and the propagation feature to obtain a target sample feature, where the target sample feature is the first sample feature or the second sample feature.

[0050] In an embodiment of the present invention, a target node is feature-encoded to obtain a first target intermediate feature, where the target node is a node among the multiple sample query nodes or the multiple sample MV nodes; based on the association relationship, adjacent nodes of the target node are determined; a propagation feature corresponding to a second target intermediate feature of the adjacent nodes is obtained, where the second target intermediate feature is a feature obtained by feature-encoding the adjacent nodes; the first target intermediate feature and the propagation feature are aggregated to obtain a target sample feature, where the target sample feature is the first sample feature or the second sample feature. In this way, by aggregating the first target intermediate feature and the propagation feature, a node can aggregate the features of other adjacent nodes, so that there is a certain correlation between two adjacent nodes. When the target sample feature is processed by the initial graph neural network model, the benefit parameter between nodes can be calculated more accurately.

[0051] In some embodiments, the process of feature-encoding the target node to obtain the first target intermediate feature is the same as the process of extracting the first feature and the second feature, that is, the first target intermediate feature is obtained by feature-encoding with the parameters of three parts: the operator type, metadata, and predicate of the target node.

[0052] In some embodiments, the obtaining of the propagation feature corresponding to the second target intermediate feature of the adjacent nodes includes: Obtaining the propagation feature corresponding to the aggregated second target intermediate feature in sequence according to the set number of propagation times; The aggregating of the first target intermediate feature and the propagation feature to obtain the target sample feature includes: Aggregating the first target intermediate feature and the obtained propagation feature in sequence to obtain the target sample feature.

[0053] The above set number of propagation times can be set according to requirements. Each time a feature propagation is performed, an aggregation is performed. Through multiple feature propagations and aggregations, the features of farther nodes can be fused.

[0054] For example, in the query graph, the association relationship between nodes is a linear relationship of v1-q1-v2-v3-q2, where v1, v2, and v3 are sample MV nodes, and q1 and q2 are sample query nodes. For v3, if the set number of propagation times is 1, the feature corresponding to v3 aggregates the features of the three nodes v2-v3-q2; if the set number of propagation times is 2, the feature corresponding to v3 aggregates the features of the four nodes q1-v2-v3-q2. In this way, by setting the number of propagation times for fusion, the preset graph neural network model obtained through training can determine the association relationship between different nodes according to the target sample features, and then calculate the benefit parameter.

[0055] In one embodiment, the adjacent node is a parent node, an index node, or other nodes. The obtaining the propagation feature corresponding to the second target intermediate feature of the adjacent node includes: When the adjacent node is the parent node, calculate the second target intermediate feature based on a first preset formula to obtain the propagation feature; When the adjacent node is the index node, calculate the second target intermediate feature based on a second preset formula to obtain the propagation feature; When the adjacent node is the other node, calculate the second target intermediate feature based on a third preset formula to obtain the propagation feature.

[0056] It should be noted that the relationships between different nodes are different, such as parent nodes and child nodes, index nodes and indexed nodes, etc. In the query graph, data flows from bottom to top during execution. Therefore, the features of parent nodes and child nodes should be distinguished in the aggregation function. In addition, the differences of child nodes should also be identified. Specifically, if a child node of node v has an index, the execution of the operator in v will be more efficient than without using an index, especially for the join operator. Therefore, the presence / absence of an index for child nodes will significantly affect the execution of the query. To better estimate the benefit parameter, different treatments are performed during aggregation for different relationships, so that the preset graph neural network model can accurately identify different relationships, and further improve the accuracy of the calculated benefit parameter.

[0057] Furthermore, the present invention provides an MV aggregator for feature aggregation on a query graph, which processes parent nodes, index nodes, and other nodes with different linear transformations, respectively represented as , , . Among them, adjacent nodes will pass different features to the nodes in the aggregation through different coefficients such as , , .

[0058] Specifically, the first preset formula, the second preset formula, and the third preset formula can be expressed by the following formulas: , In the formula represents the propagation feature of node v j propagating features to node v i The propagated features for feature propagation, is the second target intermediate feature of node v j , , , are preset constants. The propagation features are determined through the above formulas.

[0059] In some embodiments, aggregating the first target intermediate feature and the propagation feature to obtain the target sample feature can be expressed by the following formula: , , In the formula represents the set composed of all adjacent nodes of node v, is the aggregated target sample feature, is the second target intermediate feature of node i, is the MV set including multiple sample MV nodes, and k is the set number of propagation times. After k times of propagation and aggregation, the target sample feature of each node can be expressed as .

[0060] In one embodiment, training the initial graph neural network model based on the first sample features corresponding to the multiple sample query nodes, the second sample features corresponding to the multiple sample MV nodes, and the sample benefit parameter to obtain the preset graph neural network model includes: Training the initial graph neural network model based on the first sample features corresponding to the multiple sample query nodes, the second sample features corresponding to the multiple sample MV nodes, and the sample benefit parameter to obtain an intermediate neural network model; Calculating the loss value corresponding to the intermediate neural network model based on the sample benefit parameter corresponding to the multiple sample query nodes; When the loss value is less than the set loss threshold, setting the intermediate neural network model as the preset graph neural network model.

[0061] It should be noted that the target sample features of each node can estimate the benefit (i.e., the benefit parameter) brought by each MV node to each query node. By connecting the embeddings of the query q and the view v, and then using a Multilayer Perceptron (MLP) to determine the relationships between different nodes, the benefit parameter is then calculated. .

[0062] Assuming that q and v are represented by and respectively, the benefit parameter can be expressed by the following formula: , In the formula , , , are preset constants.

[0063] Calculating the loss value corresponding to the intermediate neural network model based on the sample benefit parameters corresponding to the multiple sample query nodes can specifically train the model by minimizing the loss function. The goal is to optimize the parameters W and b, which can be specifically expressed by the following formula: , , In the formula is the loss value, represents the set composed of groups, and the node q is the parent node of the node v in the query graph. It should be noted that if the node q is not the parent node of the node v, then the MV node v has no chance to benefit the query node q, so the benefit parameter does not need to be calculated.

[0064] Please refer to Figure 5 , Figure 5 which is the structural diagram of a materialized view maintenance device provided by an embodiment of the present invention. As Figure 5 shown, the materialized view maintenance device 500 includes: An acquisition module 501, configured to acquire historical data and an initial MV set, where the initial MV set includes multiple initial MV nodes, the historical data includes N historical query instructions, and the maximum first benefit parameter corresponding to each historical query instruction, and the first benefit parameter is used to characterize the reduction in execution time after rewriting, and N is a positive integer greater than 1; A creation module 502, configured to create an empty initial MV subset when the sum of the maximum first benefit parameters corresponding to the N historical query instructions is less than a set benefit threshold; A first calculation module 503, configured to calculate the second benefit parameter corresponding to each initial MV node in the initial MV set; An adding module 504, configured to add a plurality of target MV nodes to the initial MV subset based on the second benefit parameter to obtain a target MV subset, where the plurality of target MV nodes are nodes among the plurality of initial MV nodes, and the second benefit parameter corresponding to the target MV node is greater than the second benefit parameter corresponding to other MV nodes in the initial MV set; A setting module 505, configured to set the target MV subset as the optimized target MV set.

[0065] In one embodiment, the initial MV set further includes a plurality of query nodes, and the first calculation module 503 includes: A calculation unit, configured to calculate the benefit parameter between a first MV node and the plurality of query nodes, where the first MV node is one of the plurality of initial MV nodes; A setting unit, configured to set the maximum benefit parameter among the benefit parameters between the first MV node and the plurality of query nodes as the second benefit parameter.

[0066] In one embodiment, the materialized view maintenance device 500 further includes: A receiving module, configured to receive a query instruction; An extraction module, configured to extract a first feature corresponding to the query instruction, and a second feature corresponding to each MV node included in the target MV set; A second calculation module, configured to calculate a third benefit parameter between the second feature corresponding to each MV node and the first feature based on a preset graph neural network model; A rewriting module, configured to rewrite the query instruction based on the MV node with the maximum third benefit parameter among the plurality of MV nodes to obtain a target instruction.

[0067] In one embodiment, the preset graph neural network model is obtained by the following method: Obtain sample data, where the sample data includes a sample query graph and a sample benefit parameter, the sample query graph includes a plurality of sample query nodes and a plurality of sample MV nodes, and an association relationship between different sample query nodes and different sample MV nodes, and the sample benefit parameter is the reduced execution time after rewriting the instruction corresponding to the sample query node according to the sample MV node; Perform feature encoding on the plurality of sample query nodes and the plurality of sample MV nodes respectively to obtain first sample features corresponding to the plurality of sample query nodes, and second sample features corresponding to the plurality of sample MV nodes; Train an initial graph neural network model based on the first sample features corresponding to the multiple sample query nodes, the second sample features corresponding to the multiple sample MV nodes, and the sample benefit parameter to obtain the preset graph neural network model.

[0068] In one embodiment, the separately performing feature encoding on the multiple sample query nodes and the multiple sample MV nodes to obtain the first sample features corresponding to the multiple sample query nodes and the second sample features corresponding to the multiple sample MV nodes includes: Perform feature encoding on a target node to obtain a first target intermediate feature, where the target node is a node among the multiple sample query nodes or the multiple sample MV nodes; Determine adjacent nodes of the target node based on the association relationship; Obtain a propagation feature corresponding to a second target intermediate feature of the adjacent nodes, where the second target intermediate feature is a feature obtained by performing feature encoding on the adjacent nodes; Aggregate the first target intermediate feature and the propagation feature to obtain a target sample feature, where the target sample feature is the first sample feature or the second sample feature.

[0069] In one embodiment, the adjacent nodes are parent nodes, index nodes, or other nodes, and the obtaining the propagation feature corresponding to the second target intermediate feature of the adjacent nodes includes: In the case where the adjacent node is the parent node, calculate the propagation feature based on a first preset formula for the second target intermediate feature; In the case where the adjacent node is the index node, calculate the propagation feature based on a second preset formula for the second target intermediate feature; In the case where the adjacent node is the other node, calculate the propagation feature based on a third preset formula for the second target intermediate feature.

[0070] In one embodiment, the training the initial graph neural network model based on the first sample features corresponding to the multiple sample query nodes, the second sample features corresponding to the multiple sample MV nodes, and the sample benefit parameter to obtain the preset graph neural network model includes: Train the initial graph neural network model based on the first sample features corresponding to the multiple sample query nodes, the second sample features corresponding to the multiple sample MV nodes, and the sample benefit parameter to obtain an intermediate neural network model; Calculate a loss value corresponding to the intermediate neural network model based on the sample benefit parameter corresponding to the multiple sample query nodes; When the loss value is less than the set loss threshold, set the intermediate neural network model as the preset graph neural network model.

[0071] The materialized view maintenance device provided by the embodiments of the present invention can implement each process of the above materialized view maintenance method. The technical features correspond one by one and can achieve the same technical effects. To avoid repetition, they will not be described here again.

[0072] It should be noted that the materialized view maintenance device in the embodiments of the present invention can be a device, or a component, integrated circuit, or chip in an electronic device.

[0073] The embodiments of the present invention also provide an electronic device, including: a processor, a memory, and a program stored on the memory and executable on the processor. When the program is executed by the processor, it implements each process of the above materialized view maintenance method embodiments and can achieve the same technical effects. To avoid repetition, they will not be described here again.

[0074] Specifically, refer to Figure 6 As shown, the embodiments of the present invention also provide an electronic device, including a bus 601, a transceiver 602, an antenna 603, a bus interface 604, a processor 605, and a memory 606.

[0075] The transceiver 602 is used to obtain historical data and an initial MV set. The initial MV set includes multiple initial MV nodes. The historical data includes N historical query instructions, and the maximum first benefit parameter corresponding to each historical query instruction. The first benefit parameter is used to characterize the reduction of the execution time after rewriting. N is a positive integer greater than 1; The processor 605 is used to create an empty initial MV subset when the sum of the maximum first benefit parameters corresponding to the N historical query instructions is less than the set benefit threshold; The processor 605 is further used to calculate the second benefit parameter corresponding to each initial MV node in the initial MV set; The processor 605 is further used to add multiple target MV nodes to the initial MV subset based on the second benefit parameter to obtain a target MV subset. The multiple target MV nodes are nodes in the multiple initial MV nodes, and the second benefit parameter corresponding to the target MV node is greater than the second benefit parameter corresponding to other MV nodes in the initial MV set; The processor 605 is further used to set the target MV subset as the optimized target MV set.

[0076] In one embodiment, the initial MV set further includes a plurality of query nodes, and calculating the second benefit parameter corresponding to each initial MV node in the initial MV set includes: Calculating the benefit parameter between a first MV node and the plurality of query nodes, where the first MV node is one of the plurality of initial MV nodes; Setting the maximum benefit parameter among the benefit parameters between the first MV node and the plurality of query nodes as the second benefit parameter.

[0077] In one embodiment, the transceiver 602 is further configured to receive a query instruction; The processor 605 is further configured to extract the first feature corresponding to the query instruction, and the second feature corresponding to each MV node included in the target MV set; The processor 605 is further configured to calculate the third benefit parameter between the second feature corresponding to each MV node and the first feature based on a preset graph neural network model; The processor 605 is further configured to rewrite the query instruction based on the MV node with the largest third benefit parameter among the plurality of MV nodes to obtain a target instruction.

[0078] In one embodiment, the preset graph neural network model is obtained by the following method: Obtaining sample data, where the sample data includes a sample query graph and a sample benefit parameter, the sample query graph includes a plurality of sample query nodes and a plurality of sample MV nodes, and the association relationship between different sample query nodes and different sample MV nodes, and the sample benefit parameter is the reduced execution time after rewriting the instruction corresponding to the sample query node according to the sample MV node; Performing feature encoding on the plurality of sample query nodes and the plurality of sample MV nodes respectively to obtain the first sample features corresponding to the plurality of sample query nodes and the second sample features corresponding to the plurality of sample MV nodes; Training an initial graph neural network model based on the first sample features corresponding to the plurality of sample query nodes, the second sample features corresponding to the plurality of sample MV nodes, and the sample benefit parameter to obtain the preset graph neural network model.

[0079] In one embodiment, performing feature encoding on the plurality of sample query nodes and the plurality of sample MV nodes respectively to obtain the first sample features corresponding to the plurality of sample query nodes and the second sample features corresponding to the plurality of sample MV nodes includes: Encode the features of the target node to obtain the first target intermediate feature, where the target node is a node among the multiple sample query nodes or the multiple sample MV nodes; Determine the adjacent nodes of the target node based on the association relationship; Obtain the propagation feature corresponding to the second target intermediate feature of the adjacent node, where the second target intermediate feature is the feature obtained by encoding the features of the adjacent node; Aggregate the first target intermediate feature and the propagation feature to obtain the target sample feature, where the target sample feature is the first sample feature or the second sample feature.

[0080] In one embodiment, the adjacent node is a parent node, an index node, or other nodes. The obtaining the propagation feature corresponding to the second target intermediate feature of the adjacent node includes: When the adjacent node is the parent node, calculate the second target intermediate feature based on a first preset formula to obtain the propagation feature; When the adjacent node is the index node, calculate the second target intermediate feature based on a second preset formula to obtain the propagation feature; When the adjacent node is the other node, calculate the second target intermediate feature based on a third preset formula to obtain the propagation feature.

[0081] In one embodiment, the training of the initial graph neural network model based on the first sample features corresponding to the multiple sample query nodes, the second sample features corresponding to the multiple sample MV nodes, and the sample benefit parameter to obtain the preset graph neural network model includes: Train the initial graph neural network model based on the first sample features corresponding to the multiple sample query nodes, the second sample features corresponding to the multiple sample MV nodes, and the sample benefit parameter to obtain an intermediate neural network model; Calculate the loss value corresponding to the intermediate neural network model based on the sample benefit parameter corresponding to the multiple sample query nodes; When the loss value is less than the set loss threshold, set the intermediate neural network model as the preset graph neural network model.

[0082] In Figure 6Among them, a bus architecture (represented by bus 601), bus 601 may include any number of interconnected buses and bridges. Bus 601 links together various circuits including one or more processors represented by processor 605 and a memory represented by memory 606. Bus 601 may also link together various other circuits such as peripheral devices, voltage regulators, and power management circuits, which are well known in the art and thus will not be further described herein. Bus interface 604 provides an interface between bus 601 and transceiver 602. Transceiver 602 may be a single component or multiple components, such as multiple receivers and transmitters, providing units for communicating with various other devices over a transmission medium. Data processed by processor 605 is transmitted over a wireless medium via antenna 603. Further, antenna 603 also receives data and transmits the data to processor 605.

[0083] Processor 605 is responsible for managing bus 601 and general processing, and may also provide various functions including timing, peripheral interface, voltage regulation, power management, and other control functions. Memory 606 may be used to store data used by processor 605 during operation.

[0084] Optionally, processor 605 may be a CPU, ASIC, FPGA, or CPLD.

[0085] An embodiment of the present invention also provides a computer-readable storage medium, on which a computer program is stored. When the computer program is executed by a processor, it implements each process of the embodiment of the above materialized view maintenance method and can achieve the same technical effect. To avoid repetition, it will not be elaborated here. Among them, the computer-readable storage medium is, for example, a Read-Only Memory (ROM), a Random Access Memory (RAM), a magnetic disk, or an optical disc, etc.

[0086] The present invention also provides a computer program product, including computer instructions, which when executed by a processor implement the above Figure 1 corresponding processes of the embodiment of the materialized view maintenance method and can achieve the same technical effect. To avoid repetition, it will not be elaborated here.

[0087] It should be noted that in this text, the terms "include", "comprise" or any other variants thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements not only includes those elements, but also includes other elements not explicitly listed, or further includes elements inherent to such process, method, article or device. Without further limitation, an element defined by the statement "including one..." does not exclude the existence of additional identical elements in the process, method, article or device including that element.

[0088] Through the description of the above embodiments, those skilled in the art can clearly understand that the above-described example methods can be implemented by means of software plus a necessary general hardware platform. Of course, they can also be implemented by hardware, but in many cases the former is a better implementation. Based on such an understanding, the technical solution of the present invention, in essence or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk) and includes several instructions for causing a terminal (which can be a mobile phone, computer, server, air conditioner, or network device, etc.) to execute the methods described in various embodiments of the present invention.

[0089] The embodiments of the present invention have been described above in conjunction with the accompanying drawings. However, the present invention is not limited to the above specific embodiments. The above specific embodiments are merely illustrative and not restrictive. Under the inspiration of the present invention, those of ordinary skill in the art can also make many forms without departing from the purpose of the present invention and the scope protected by the claims, and all of them belong to the protection scope of the present invention.

Claims

1. A method for maintaining a materialized view, characterized in that Including: Obtain historical data and an initial MV set, where the initial MV set includes multiple initial MV nodes, the historical data includes N historical query instructions, and the maximum first benefit parameter corresponding to each historical query instruction, where the first benefit parameter is used to characterize the reduction in execution time after rewriting, and N is a positive integer greater than 1; When the sum of the maximum first benefit parameters corresponding to the N historical query instructions is less than a set benefit threshold, create an empty initial MV subset; Calculate the second benefit parameter corresponding to each initial MV node in the initial MV set; Based on the second benefit parameter, add multiple target MV nodes to the initial MV subset to obtain a target MV subset, where the multiple target MV nodes are nodes in the multiple initial MV nodes, and the second benefit parameter corresponding to the target MV node is greater than the second benefit parameters corresponding to other MV nodes in the initial MV set; Set the target MV subset as the optimized target MV set.

2. The method according to claim 1, wherein The initial MV set further includes multiple query nodes, and calculating the second benefit parameter corresponding to each initial MV node in the initial MV set includes: Calculate the benefit parameter between a first MV node and the multiple query nodes, where the first MV node is one of the multiple initial MV nodes; Set the maximum benefit parameter among the benefit parameters between the first MV node and the multiple query nodes as the second benefit parameter.

3. The method according to claim 1 or 2, characterized in that, After setting the target MV subset as the optimized target MV set, the method further includes: Receive a query instruction; Extract the first feature corresponding to the query instruction, and the second feature corresponding to each MV node included in the target MV set; Based on a preset graph neural network model, calculate the third benefit parameter between the second feature corresponding to each MV node and the first feature; Rewrite the query instruction based on the MV node with the maximum third benefit parameter among the multiple MV nodes to obtain a target instruction.

4. The method according to claim 3, wherein The preset graph neural network model is obtained through the following method: Obtain sample data, where the sample data includes a sample query graph and a sample benefit parameter, the sample query graph includes multiple sample query nodes and multiple sample MV nodes, and the association relationship between different sample query nodes and different sample MV nodes, and the sample benefit parameter is the reduced execution time after rewriting the instruction corresponding to the sample query node according to the sample MV node; Perform feature encoding on the multiple sample query nodes and the multiple sample MV nodes respectively to obtain the first sample features corresponding to the multiple sample query nodes and the second sample features corresponding to the multiple sample MV nodes; Train an initial graph neural network model based on the first sample features corresponding to the multiple sample query nodes, the second sample features corresponding to the multiple sample MV nodes, and the sample benefit parameter to obtain the preset graph neural network model.

5. The method according to claim 4, wherein Performing feature encoding on the multiple sample query nodes and the multiple sample MV nodes respectively to obtain the first sample features corresponding to the multiple sample query nodes and the second sample features corresponding to the multiple sample MV nodes includes: Performing feature encoding on a target node to obtain a first target intermediate feature, where the target node is a node among the multiple sample query nodes or the multiple sample MV nodes; Determining adjacent nodes of the target node based on the association relationship; Obtaining propagation features corresponding to second target intermediate features of the adjacent nodes, where the second target intermediate features are features obtained by performing feature encoding on the adjacent nodes; Aggregating the first target intermediate feature and the propagation features to obtain a target sample feature, where the target sample feature is the first sample feature or the second sample feature.

6. The method according to claim 5, characterized in that The adjacent nodes are parent nodes, index nodes, or other nodes. Obtaining the propagation features corresponding to the second target intermediate features of the adjacent nodes includes: When the adjacent node is the parent node, calculating the second target intermediate feature based on a first preset formula to obtain the propagation features; When the adjacent node is the index node, calculating the second target intermediate feature based on a second preset formula to obtain the propagation features; When the adjacent node is the other node, calculating the second target intermediate feature based on a third preset formula to obtain the propagation features.

7. The method according to claim 4, wherein Training an initial graph neural network model based on the first sample features corresponding to the multiple sample query nodes, the second sample features corresponding to the multiple sample MV nodes, and the sample benefit parameter to obtain the preset graph neural network model includes: Training an initial graph neural network model based on the first sample features corresponding to the multiple sample query nodes, the second sample features corresponding to the multiple sample MV nodes, and the sample benefit parameter to obtain an intermediate neural network model; Calculating a loss value corresponding to the intermediate neural network model based on the sample benefit parameter corresponding to the multiple sample query nodes; When the loss value is less than a set loss threshold, setting the intermediate neural network model as the preset graph neural network model.

8. A materialized view maintenance device, characterized in that Includes: An acquisition module for acquiring historical data and an initial MV set, where the initial MV set includes multiple initial MV nodes, the historical data includes N historical query instructions, and a maximum first benefit parameter corresponding to each historical query instruction, where the first benefit parameter is used to characterize the reduction in execution time after rewriting, and N is a positive integer greater than 1; A creation module for creating an empty initial MV subset when the sum of the maximum first benefit parameters corresponding to the N historical query instructions is less than a set benefit threshold; A first calculation module for calculating a second benefit parameter corresponding to each initial MV node in the initial MV set; An adding module, configured to add a plurality of target MV nodes to the initial MV subset based on the second benefit parameter to obtain a target MV subset, where the plurality of target MV nodes are nodes among the plurality of initial MV nodes, and the second benefit parameter corresponding to the target MV node is greater than the second benefit parameters corresponding to other MV nodes in the initial MV set; A setting module, configured to set the target MV subset as the optimized target MV set.

9. An electronic device, characterized in that, It includes a transceiver and a processor. The transceiver is configured to obtain historical data and an initial MV set. The initial MV set includes a plurality of initial MV nodes. The historical data includes N historical query instructions and the maximum first benefit parameter corresponding to each historical query instruction. The first benefit parameter is used to characterize the reduction in execution time after rewriting, and N is a positive integer greater than 1; The processor is configured to create an empty initial MV subset when the sum of the maximum first benefit parameters corresponding to the N historical query instructions is less than a set benefit threshold; The processor is further configured to calculate the second benefit parameter corresponding to each initial MV node in the initial MV set; The processor is further configured to add a plurality of target MV nodes to the initial MV subset based on the second benefit parameter to obtain a target MV subset, where the plurality of target MV nodes are nodes among the plurality of initial MV nodes, and the second benefit parameter corresponding to the target MV node is greater than the second benefit parameters corresponding to other MV nodes in the initial MV set; The processor is further configured to set the target MV subset as the optimized target MV set.

10. An electronic device, characterized in that, It includes: A processor, a memory, and a program stored on the memory and executable on the processor. When the program is executed by the processor, the steps of the materialized view maintenance method according to any one of claims 1 to 7 are implemented.

11. A computer-readable storage medium, characterized in that, A computer program is stored on the computer-readable storage medium. When the computer program is executed by the processor, the steps of the materialized view maintenance method according to any one of claims 1 to 7 are implemented.

12. A computer program product, characterized in that, It includes computer instructions. When the computer instructions are executed by the processor, the steps of the materialized view maintenance method according to any one of claims 1 to 7 are implemented.

Images