Intelligent routing optimization method and system for multi-table associated query

Abstract

The invention provides an intelligent routing optimization method and system for multi-table association query, and relates to the technical field of routing optimization, and the method comprises the steps: extracting static features and dynamic features in a query request to construct a multi-dimensional feature vector; and calculating association degrees among the tables based on the static characteristics and grouping, and predicting a system resource state by using a time sequence prediction model. And inputting the grouping result and the resource state sequence into a two-channel neural network model, respectively processing by using a graph neural network and a recurrent neural network, and fusing features through an attention mechanism. And finally, the decision-making layer network outputs a query optimization scheme including a distributed execution plan, a data fragmentation strategy and a resource allocation scheme. In the execution process, the performance is monitored in real time, and the optimization scheme is adjusted according to the state information. In addition, an incremental training set is constructed by utilizing execution historical information, the model is updated online, the model adaptability is improved, and closed-loop optimization is formed, so that the query efficiency is remarkably improved, and the system resource consumption is reduced.

Description

Technical Field

[0001] The present invention relates to routing optimization technologies, and in particular to an intelligent routing optimization method and system for multi-table join queries. Background Art

[0002] In a distributed database system, multi-table join queries are common operations, and their performance directly affects the overall efficiency of the database system. With the growth of data scale and the increase of query complexity, how to efficiently execute multi-table join queries has become an important research topic. Traditional query optimizers usually optimize based on rules or cost models, but these methods are difficult to adapt to complex and changing query scenarios and dynamic system environments. For example, rule-based optimizers rely on predefined rules, lack flexibility, and are difficult to handle new query patterns; while cost-model-based optimizers need to accurately estimate the execution cost of queries, which is full of challenges in complex distributed environments.

[0003] First of all, traditional query optimizers usually only consider static features, such as the scale of tables, the selectivity of fields, and the type of indexes, etc., while ignoring dynamic system states, such as system load, memory occupancy rate, and IO queue depth, etc. This results in the generated query execution plan being unable to adapt to changes in system resources, and is prone to causing performance bottlenecks.

[0004] Secondly, most existing optimizers adopt fixed optimization strategies and lack the ability of adaptive adjustment. During the query execution process, the system state and query load may change, and fixed optimization strategies are difficult to handle these changes, resulting in a decline in query performance.

[0005] Finally, traditional optimizers usually lack the ability of learning and improvement. They cannot learn from historical query execution data, and are difficult to automatically optimize query strategies and improve query performance. Summary of the Invention

[0006] Embodiments of the present invention provide an intelligent routing optimization method and system for multi-table join queries, which can solve the problems in the prior art.

[0007] In the first aspect of the embodiments of the present invention,

[0008] An intelligent routing optimization method for multi-table join queries is provided, including:

[0009] Receive a multi-table association query request, extract the static features and dynamic features in the multi-table association query request to construct a multi-dimensional feature vector, where the static features include the number of tables participating in the query, the field selection degree, and the index type, and the dynamic features include the system load status, the memory occupancy rate, and the IO queue depth; calculate the inter-table association degree score based on the static features, generate a query association matrix, and group and partition the data tables participating in the query according to the query association matrix to obtain a query grouping result; at the same time, input the dynamic features into a pre-constructed time series prediction model to generate a system resource status sequence within the predicted time window;

[0010] Input the query grouping result and the system resource status sequence into a dual-channel neural network model. The first channel of the dual-channel neural network model uses a graph neural network to process the query grouping result and extract the inter-table association features. The second channel uses a recurrent neural network to process the system resource status sequence and capture the resource change trend; adaptively fuse the output features of the two channels based on the attention mechanism to generate a comprehensive feature representation; input the comprehensive feature representation into the decision layer network, and output a query optimization plan including a distributed execution plan, a data sharding strategy, and a resource allocation plan. The query optimization plan is used as the scheduling basis for the execution engine;

[0011] Send the query optimization plan to the execution engine, set performance monitoring points during the execution process, and collect the execution status information in real time. When it is detected that the execution efficiency decreases, combine the latest collected status information with the original feature vector and re-input it into the dual-channel neural network model for optimization plan adjustment; record the execution history information during the entire query process. The execution history information includes the execution path, resource usage, performance metrics, and optimization effects. Sort the execution history information according to the performance improvement amplitude, and select the execution history information with a higher ranking to construct an incremental training set; perform online update on the dual-channel neural network model based on the incremental training set, and improve the model's adaptability to different query scenarios by adjusting the network parameters to form a closed-loop optimization.

[0012] Calculate the inter-table association degree score based on the static features, generate a query association matrix, and group and partition the data tables participating in the query according to the query association matrix. The obtained query grouping result includes:

[0013] Extract the data features of the associated fields and the index information of the associated fields from the static features; calculate the field association score based on the data features of the associated fields, and the field association score is calculated by weighted calculation of the selection degree ratio of the associated fields, the data type matching degree, and the data distribution similarity; calculate the index matching score based on the index information of the associated fields, and the index matching score is determined according to the index coverage and the index type matching degree; combine the field association score and the index matching score to calculate the inter-table association degree score;

[0014] Construct a query association degree matrix, the dimension of the query association degree matrix is equal to the number of tables participating in the query, fill the inter-table association degree score into the corresponding position of the query association degree matrix, the query association degree matrix satisfies symmetry, and perform normalization processing on the query association degree matrix to obtain a standardized association degree matrix; construct an inter-table distance matrix based on the standardized association degree matrix, and the element value of the inter-table distance matrix is the inverse function of the association degree score;

[0015] Perform grouping division based on the inter-table distance matrix, form an initial grouping for the table pairs with the smallest distance, calculate the average distance between the ungrouped tables and the existing groups in an iterative manner, add the ungrouped tables to the corresponding groups when the average distance between the groups is less than the first preset threshold, and form new groups when the average distance between the groups is greater than the first preset threshold to obtain the query grouping result.

[0016] Calculate the index matching score based on the index information of the associated fields, and the index matching score is determined according to the index coverage and the index type matching degree; the combination of the field association score and the index matching score to calculate the inter-table association degree score includes:

[0017] Receive the index information and associated fields of two data tables to be associated, the index information includes index type, index column composition, and index key value distribution, and the associated fields include field cardinality, field distribution characteristics, and field null value ratio;

[0018] Determine the index coverage of the associated fields based on the index information, assign different weight values to different coverage degrees according to the position of the associated fields in the index, assign the first weight value when the associated field is an index prefix column, assign the second weight value when the associated field is a non-index prefix column, and assign the third weight value when the associated field is not covered by the index, and perform weighted combination of the first weight value, the second weight value, and the third weight value to generate an index coverage score;

[0019] The calculation formula of the index coverage score is as follows:

[0020] ;

[0021] Among them, IC(f) is the index coverage score of the associated field f, w1 is the first weight value representing the weight of the index prefix, w2 is the second weight value representing the weight of the non-prefix index column, w3 is the third weight value representing the weight of the uncovered index, and I prefix (f) is the first indicator function, which takes the value of 1 when the associated field f is an index prefix column, and 0 otherwise. And I nonPrefix (f) is the second indicator function, which takes the value of 1 when the associated field f is a non-index prefix column, and 0 otherwise. And I nonCovered (f) is the third indicator function, which takes the value of 1 when the associated field f is not covered by the index, and 0 otherwise;

[0022] Calculate the index type matching score based on the connection type between the index type and the associated field, assign different weight values according to the support degree of different index types for the connection operation, assign a matching weight value higher than the preset weight value when the index type and the connection type are completely matched, and assign a non-matching weight value lower than the preset weight value when the index type and the connection type do not match;

[0023] The calculation formula of the index type matching score is as follows:

[0024] ;

[0025] Among them, ITM(f) is the index type matching score of the associated field f, n is the total number of supported index types, and w i is the weight value of the i-th index type, I i is the i-th index type, J f is the connection type of the associated field f, M(I i , J f ) is the matching degree score between the index type I i and the connection type J f ;

[0026] Weightedly combine the index coverage score and the index type matching score to obtain the index matching score; calculate the field association score based on the associated field, obtain the cardinality similarity by calculating the cardinality ratio of the associated field, obtain the distribution similarity by calculating the distribution KL divergence of the associated field, obtain the null value similarity by calculating the difference in null value ratios of the associated field, and weightedly combine the cardinality similarity, the distribution similarity, and the null value similarity to obtain the field association score; weightedly combine the index matching score and the field association score to calculate the inter-table association degree score.

[0027] Input the query grouping result and the system resource status sequence into a dual-channel neural network model. The first channel of the dual-channel neural network model uses a graph neural network to process the query grouping result and extract inter-table association features, and the second channel uses a recurrent neural network to process the system resource status sequence to capture resource change trends, including:

[0028] Construct a dual-channel neural network model. The dual-channel neural network model includes a first channel and a second channel. The first channel uses a graph neural network to process the query grouping result, and the second channel uses a recurrent neural network to process the system resource status sequence; in the first channel, construct the query grouping result into a graph structure. The vertices of the graph structure represent data tables, and the vertices contain feature vectors of table access frequency, table read-write ratio, and table update frequency. The edges of the graph structure represent inter-table association relationships, and the edges contain feature vectors of association field selection ratio, field type matching degree, and connection cost estimation value;

[0029] In the graph neural network of the first channel, process the feature vector of the edge through an edge feature encoding layer to generate an embedded representation of the edge; calculate the multi-head attention coefficient between vertices based on the feature vector of the vertex and the embedded representation of the edge. The multi-head attention coefficient is calculated through a trainable attention vector and a feature transformation matrix; use the multi-head attention coefficient to perform weighted aggregation on the feature vector of the vertex, and through residual connection and layer normalization processing, generate a vertex representation reflecting the local structure; iteratively update the vertex representation through a multi-layer graph attention network to capture global structure information and generate an association feature vector reflecting inter-table association relationships;

[0030] In the recurrent neural network of the second channel, project the system resource status sequence into a hidden layer space through a trainable feature mapping layer; use a long short-term memory network to model the projected status sequence. The long short-term memory network includes three gating units: an input gate, a forget gate, and an output gate. The input gate controls the degree of entry of new state information, the forget gate controls the degree of retention of historical state information, and the output gate controls the degree of output of state information; update the memory cell state and the hidden layer state based on the gating signal, and extract temporal features through a multi-layer recurrent network to generate a state feature vector reflecting resource change trends.

[0031] Based on the attention mechanism, adaptively fuse the output features of the two channels to generate a comprehensive feature representation, including:

[0032] Construct a dual-channel feature extraction network, where the dual-channel feature extraction network includes a multi-head self-attention module, a feature transformation module, and a feature fusion module. The multi-head self-attention module includes a trainable query matrix, key matrix, and value matrix. The feature transformation module includes a trainable mapping matrix. The feature fusion module includes a trainable fusion weight;

[0033] Obtain the first feature vector output by the first feature extraction channel and the second feature vector output by the second feature extraction channel. Input the first feature vector into the feature transformation module to obtain a first mapped feature, and input the second feature vector into the feature transformation module to obtain a second mapped feature; use the query matrix to transform the first mapped feature into a query representation, use the key matrix to transform the second mapped feature into a key representation, and use the value matrix to transform the second mapped feature into a value representation. The query representation, the key representation, and the value representation have the same feature dimension;

[0034] Calculate the dot product of the query representation and the key representation to obtain a similarity matrix, perform normalization processing on the similarity matrix to obtain an attention weight matrix, and perform weighted summation on the value representation based on the attention weight matrix to obtain a context feature; splice the context feature and the first mapped feature, calculate a fusion coefficient through the feature fusion module, and perform adaptive weighting on the features based on the fusion coefficient to obtain a comprehensive feature representation.

[0035] Input the comprehensive feature representation into the decision layer network, and output a query optimization plan including a distributed execution plan, a data sharding strategy, and a resource allocation scheme. The query optimization plan serves as the scheduling basis for the execution engine and includes:

[0036] Construct a hierarchical decision network, where the hierarchical decision network includes an execution plan generation layer, a sharding strategy generation layer, and a resource allocation layer. The execution plan generation layer includes an operator dependency analysis unit and a parallelism prediction unit. The sharding strategy generation layer includes a sharding key selection unit and a sharding boundary calculation unit. The resource allocation layer includes a resource demand prediction unit and a resource scheduling unit;

[0037] Input the comprehensive feature representation into the execution plan generation layer. The operator dependency analysis unit analyzes the data flow dependency relationship between operators to obtain an operator dependency graph. The parallelism prediction unit predicts the parallelism of each operator based on the operator dependency graph to obtain a distributed execution plan;

[0038] Input the comprehensive feature representation and the distributed execution plan into the sharding strategy generation layer. The sharding key selection unit selects the optimal sharding key based on the data distribution characteristics, and the sharding boundary calculation unit determines the sharding boundary based on the data skew degree to obtain a data sharding strategy;

[0039] Input the comprehensive feature representation, the distributed execution plan, and the data sharding strategy into the resource allocation layer. The resource demand prediction unit predicts the computing and storage resource demands, and the resource scheduling unit generates a resource allocation plan based on the resource utilization rate;

[0040] Combine the distributed execution plan, the data sharding strategy, and the resource allocation plan to form a query optimization plan, which is used to guide the distributed execution engine for task scheduling.

[0041] Combining the distributed execution plan, the data sharding strategy, and the resource allocation plan to form a query optimization plan for guiding the distributed execution engine to perform task scheduling includes:

[0042] Construct a multi-level optimization combination system, which includes a plan parsing unit, a data mapping unit, and a resource allocation unit. The outputs of the plan parsing unit, the data mapping unit, and the resource allocation unit are respectively connected to the combination optimization unit;

[0043] The plan parsing unit receives the distributed execution plan, extracts the data flow relationship between operators to construct a directed acyclic graph, calculates the optimal parallelism of each operator node based on the operator computing complexity and the intermediate result scale, and marks the data flow dependency relationship in the directed acyclic graph to obtain the execution skeleton information;

[0044] The data mapping unit receives the data sharding strategy, establishes the mapping relationship from data shards to physical nodes based on the operator distribution in the directed acyclic graph, calculates the data distribution density of each shard, and generates an initial shard routing table;

[0045] The resource allocation unit receives the resource allocation plan, and according to the parallelism information marked in the directed acyclic graph, allocates computing resource quotas and storage resource limits for each operator node, and generates a resource configuration table;

[0046] The combination optimization unit hierarchically combines the execution skeleton information, the initial shard routing table, and the resource configuration table to construct a query optimization plan including an execution dependency layer, a data distribution layer, and a resource configuration layer;

[0047] Construct an adaptive scheduling system, which includes a task parser, a dynamic optimizer, and an execution monitor. The input end of the task parser is connected to the output end of the combination optimization unit; the task parser converts operator nodes into specific execution tasks based on the execution dependency layer in the query optimization plan, determines the data locality of the tasks according to the data distribution layer, and generates task scheduling instructions based on the resource configuration layer.

[0048] In the second aspect of the embodiments of the present invention,

[0049] An intelligent routing optimization system for multi-table association query is provided, including:

[0050] A first unit, configured to receive a multi-table association query request, extract static features and dynamic features in the multi-table association query request to construct a multi-dimensional feature vector, where the static features include the number of tables participating in the query, the field selection degree, and the index type, and the dynamic features include the system load status, the memory occupancy rate, and the IO queue depth; calculate the table association degree score based on the static features, generate a query association degree matrix, and group and partition the data tables participating in the query according to the query association degree matrix to obtain a query grouping result; at the same time, input the dynamic features into a pre-constructed time series prediction model to generate a system resource status sequence within a predicted time window;

[0051] A second unit, configured to input the query grouping result and the system resource status sequence into a dual-channel neural network model. The first channel of the dual-channel neural network model uses a graph neural network to process the query grouping result and extract inter-table association features, and the second channel uses a recurrent neural network to process the system resource status sequence to capture resource change trends; adaptively fuse the output features of the two channels based on the attention mechanism to generate a comprehensive feature representation; input the comprehensive feature representation into a decision layer network to output a query optimization plan including a distributed execution plan, a data sharding strategy, and a resource allocation plan, and the query optimization plan is used as the scheduling basis for the execution engine;

[0052] A third unit, configured to send the query optimization plan to the execution engine, set performance monitoring points during the execution process, collect execution status information in real time, and when it is detected that the execution efficiency decreases, combine the latest collected status information with the original feature vector and re-input them into the dual-channel neural network model for optimization plan adjustment; record the execution history information during the entire query process, where the execution history information includes the execution path, resource usage, performance metrics, and optimization effects, sort the execution history information according to the performance improvement amplitude, select the execution history information with a higher ranking to construct an incremental training set; perform online update on the dual-channel neural network model based on the incremental training set, and improve the adaptability of the model to different query scenarios by adjusting network parameters to form a closed-loop optimization.

[0053] In the third aspect of the embodiments of the present invention,

[0054] An electronic device is provided, including:

[0055] A processor;

[0056] A memory for storing instructions executable by the processor;

[0057] Wherein, the processor is configured to call the instructions stored in the memory to execute the method described above.

[0058] In the fourth aspect of the embodiments of the present invention,

[0059] a computer-readable storage medium is provided, on which computer program instructions are stored, and when the computer program instructions are executed by a processor, the method described above is implemented.

[0060] The beneficial effects of this application are as follows:

[0061] 1. Intelligent multi-table association query optimization: The present invention utilizes a dual-channel neural network model to fuse static table association features and dynamic system resource status, generating a better distributed execution plan, data sharding strategy, and resource allocation scheme to achieve intelligent optimization of multi-table association queries.

[0062] 2. Adaptive adjustment and performance improvement: The present invention can dynamically adjust the optimization scheme according to the performance monitoring information during the execution process, and improve the query efficiency by online updating the model, achieving closed-loop optimization and enhancing the adaptive ability of the system.

[0063] 3. Continuous learning and optimization: The present invention uses the execution history information to construct an incremental training set and online updates the model, enabling the model to continuously learn new query scenarios, improving the adaptability to different query scenarios, and achieving continuous learning and optimization. BRIEF DESCRIPTION OF THE DRAWINGS

[0064] Figure 1 is a schematic flowchart of the intelligent routing optimization method for multi-table association query in the embodiments of the present invention;

[0065] Figure 2 is a schematic structural diagram of the intelligent routing optimization system for multi-table association query in the embodiments of the present invention. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0066] To make the objectives, technical solutions, and advantages of the embodiments of the present invention clearer, the technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings in the embodiments of the present invention. Obviously, the described embodiments are only a part of the embodiments of the present invention, rather than all the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those of ordinary skill in the art without creative efforts shall fall within the protection scope of the present invention.

[0067] The technical solutions of the present invention will be described in detail below with specific embodiments. These specific embodiments can be combined with each other, and the same or similar concepts or processes may not be repeated in some embodiments.

[0068] Figure 1 This is a schematic flowchart of the intelligent routing optimization method for multi-table association query in an embodiment of the present invention. As Figure 1 shown, the method includes:

[0069] S11. Receive a multi-table association query request, extract static features and dynamic features in the multi-table association query request to construct a multi-dimensional feature vector, where the static features include the number of tables participating in the query, field selection degree, and index type, and the dynamic features include system load status, memory occupancy rate, and IO queue depth; calculate the inter-table association degree score based on the static features, generate a query association matrix, and group and partition the data tables participating in the query according to the query association matrix to obtain a query grouping result; at the same time, input the dynamic features into a pre-constructed time series prediction model to generate a system resource status sequence within the predicted time window;

[0070] S12. Input the query grouping result and the system resource status sequence into a dual-channel neural network model. The first channel of the dual-channel neural network model uses a graph neural network to process the query grouping result and extract inter-table association features, and the second channel uses a recurrent neural network to process the system resource status sequence to capture the resource change trend; adaptively fuse the output features of the two channels based on the attention mechanism to generate a comprehensive feature representation; input the comprehensive feature representation into a decision layer network to output a query optimization plan including a distributed execution plan, a data sharding strategy, and a resource allocation plan, and the query optimization plan is used as the scheduling basis for the execution engine;

[0071] S13. Send the query optimization plan to the execution engine, set performance monitoring points during the execution process, and collect execution status information in real time. When it is detected that the execution efficiency decreases, combine the latest collected status information with the original feature vector and re-enter it into the dual-channel neural network model for optimization plan adjustment; record the execution history information during the entire query process, where the execution history information includes the execution path, resource usage, performance metrics, and optimization effects, sort the execution history information according to the performance improvement amplitude, select the execution history information with a higher ranking to construct an incremental training set; perform online update on the dual-channel neural network model based on the incremental training set, and improve the adaptability of the model to different query scenarios by adjusting network parameters to form a closed-loop optimization.

[0072] In an alternative embodiment, calculating the inter-table association degree score based on the static features, generating a query association matrix, and grouping and partitioning the data tables participating in the query according to the query association matrix to obtain a query grouping result includes:

[0073] Extract the data features of the associated fields and the index information of the associated fields from the static features; calculate the field association score based on the data features of the associated fields, where the field association score is calculated by weighted calculation of the selection degree ratio of the associated fields, the data type matching degree, and the data distribution similarity; calculate the index matching score based on the index information of the associated fields, where the index matching score is determined according to the index coverage and the index type matching degree; combine and calculate the field association score and the index matching score to obtain the inter-table association degree score.

[0074] Construct a query association degree matrix, where the dimension of the query association degree matrix is equal to the number of tables participating in the query, fill the inter-table association degree score into the corresponding position of the query association degree matrix, the query association degree matrix satisfies symmetry, and perform normalization processing on the query association degree matrix to obtain a standardized association degree matrix; construct an inter-table distance matrix based on the standardized association degree matrix, where the element value of the inter-table distance matrix is the inverse function of the association degree score.

[0075] Perform grouping division based on the inter-table distance matrix, form an initial group for the table pair with the smallest distance, calculate the average distance between the ungrouped tables and the existing groups in an iterative manner, add the ungrouped tables to the corresponding groups when the average distance between the groups is less than the first preset threshold, and form a new group when the average distance between the groups is greater than the first preset threshold to obtain the query grouping result.

[0076] A database query grouping method based on static features is used to optimize the execution efficiency of multi-table join queries. This method analyzes the static features of the data tables participating in the query, calculates the inter-table association degree score, and groups the data tables based on the association degree, thereby decomposing complex join queries into multiple sub-queries and improving the query efficiency.

[0077] First, extract the data features and index information of the associated fields. For each pair of data tables participating in the query, identify the fields that appear together as the associated fields. Extract the data features of the associated fields, including the selection degree ratio of the fields (the ratio of the number of different values to the total number of rows), the data type (such as integer, string, date, etc.), and the data distribution (such as the value range, value frequency distribution, etc.). At the same time, extract the index information of the associated fields, including whether the index exists, the index type (such as B-tree index, hash index, etc.), and the index coverage (whether the index contains all the fields required for the query).

[0078] Next, calculate the field association score. The field association score is used to measure the degree of association between two associated fields. According to the selectivity ratio of the associated fields, the data type matching degree, and the data distribution similarity, calculate three component scores respectively. For example, the closer the selectivity ratio is, the higher the score; the same data type, the higher the score; the more similar the data distribution, the higher the score. The final field association score is obtained by weighted summation of these three component scores. For example, the weight of the selectivity ratio is 0.5, the weight of the data type matching degree is 0.3, and the weight of the data distribution similarity is 0.2.

[0079] Then, calculate the index matching score. The index matching score is used to measure the index utilization efficiency of two associated fields. According to the index coverage and the index type matching degree, calculate two component scores respectively. For example, the index covers all fields required for the query, the higher the score; the index types are the same and applicable to the join operation, the higher the score. The final index matching score is obtained by weighted summation of these two component scores. For example, the weight of the index coverage is 0.7, and the weight of the index type matching degree is 0.3.

[0080] Combine the field association score and the index matching score to calculate the inter-table association degree score. For example, perform weighted summation on the field association score and the index matching score to obtain the final inter-table association degree score. For example, the weight of the field association score is 0.6, and the weight of the index matching score is 0.4.

[0081] Construct a query association degree matrix. The query association degree matrix is a symmetric matrix, and its dimension is equal to the number of tables participating in the query. Fill the calculated inter-table association degree scores into the corresponding positions of the matrix. For example, if there are three tables participating in the query, construct a 3x3 matrix, where the element in the i-th row and j-th column represents the association degree score between the i-th table and the j-th table.

[0082] Normalize the query association degree matrix to obtain a standardized association degree matrix. For example, divide each element in the matrix by the sum of all elements in the matrix.

[0083] Construct an inter-table distance matrix based on the standardized association degree matrix. The element value of the inter-table distance matrix is the inverse function of the association degree score. For example, you can use 1 minus the element value in the standardized association degree matrix as the distance value.

[0084] Perform grouping division based on the inter-table distance matrix. First, find the pair of tables with the smallest distance and form an initial group. Then, iteratively calculate the average distance between the ungrouped tables and the existing groups. If the average distance between groups is less than the first preset threshold (e.g., 0.2), add the ungrouped table to the corresponding group. If the average distance between groups is greater than the first preset threshold, form a new group. Finally, obtain the query grouping result.

[0085] The solution of this application can:

[0086] Improve query efficiency: By decomposing complex join queries into multiple subqueries, the data transmission volume and computational volume can be reduced, thereby improving query efficiency. Optimize the query plan: The query grouping results can be used to guide the generation of the query plan and select a more optimal join order and execution method. Reduce the system load: By improving query efficiency, the load on the database system can be reduced, and the stability and availability of the system can be improved.

[0087] In an alternative embodiment, an index matching score is calculated based on the index information of the associated fields, and the index matching score is determined according to the index coverage and the degree of match of the index type; calculating the inter-table association degree score by combining the field association score and the index matching score includes:

[0088] Receiving the index information and associated fields of two data tables to be associated, where the index information includes the index type, index column composition, and index key value distribution, and the associated fields include field cardinality, field distribution characteristics, and field null value ratio;

[0089] Determining the index coverage of the associated fields based on the index information, and assigning different weight values to different coverage degrees according to the position of the associated fields in the index. When the associated field is an index prefix column, the first weight value is assigned; when the associated field is a non-index prefix column, the second weight value is assigned; when the associated field is not covered by the index, the third weight value is assigned. The first weight value, the second weight value, and the third weight value are weighted and combined to generate an index coverage score;

[0090] The formula for calculating the index coverage score is as follows:

[0091] ;

[0092] where IC(f) is the index coverage score of the associated field f, w1 is the first weight value representing the weight of the index prefix, w2 is the second weight value representing the weight of the non-prefix index column, w3 is the third weight value representing the weight of the non-index-covered, I prefix (f) is the first indicator function, which takes the value of 1 when the associated field f is an index prefix column, otherwise 0, I nonPrefix (f) is the second indicator function, which takes the value of 1 when the associated field f is a non-index prefix column, otherwise 0, I nonCovered (f) is the third indicator function, which takes the value of 1 when the associated field f is not covered by the index, otherwise 0;

[0093] Calculate the index type matching score based on the connection type between the index type and the associated field. Different weight values are assigned according to the support degree of different index types for connection operations. When the index type and the connection type are completely matched, a matching weight value higher than the preset weight value is assigned. When the index type and the connection type do not match, a non-matching weight value lower than the preset weight value is assigned;

[0094] The calculation formula for the index type matching score is as follows:

[0095] ;

[0096] where ITM(f) is the index type matching score of the associated field f, n is the total number of supported index types, w i is the weight value of the i-th index type, I i is the i-th index type, J f is the connection type of the associated field f, M(I i , J f ) is the matching degree score between the index type I i and the connection type J f ;

[0097] Weightedly combine the index coverage score and the index type matching score to obtain the index matching score; calculate the field association score based on the associated field. Calculate the cardinality similarity by calculating the cardinality ratio of the associated field, calculate the distribution similarity by calculating the KL divergence of the distribution of the associated field, calculate the null value similarity by calculating the difference in the null value ratio of the associated field, and weightedly combine the cardinality similarity, the distribution similarity, and the null value similarity to obtain the field association score; calculate the inter-table association degree score by weightedly combining the index matching score and the field association score.

[0098] A method for calculating the inter-table association degree based on index and field information is used to evaluate the association strength between two data tables for database optimization or data analysis. This method comprehensively considers the index coverage, the index type matching degree, and the characteristics of the associated field itself, so as to more accurately reflect the inter-table association relationship.

[0099] First, obtain the index information and associated field information of the two data tables to be associated. The index information includes the index type (such as B-tree index, hash index, etc.), the index column composition (such as single-column index, composite index, etc.), and the distribution of index key values (such as the number of unique values, the uniformity of value distribution, etc.). The associated field information includes the field cardinality (the number of different values), the field distribution characteristics (such as the skewness, kurtosis, etc. of the value distribution), and the field null value ratio.

[0100] Next, determine the index coverage of the associated fields based on the obtained index information. Different weight values are assigned according to the position of the associated fields in the index. If the associated field is a prefix column of the index (for example, in the composite index (col1, col2), col1 is the prefix column), the highest weight value is assigned because in this case the index can most effectively support the join operation. If the associated field is a non-prefix index column (for example, in the composite index (col1, col2), col2 is the non-prefix column), a medium weight value is assigned because the index can still provide a certain acceleration effect at this time, but it is not as efficient as the prefix column. If the associated field is not covered by any index, the lowest weight value is assigned because in this case a full table scan is required for the join operation, resulting in the lowest efficiency. The weight values in these three cases are combined with weights to generate an index coverage score.

[0101] For example, assume there are two tables, Table A and Table B, and the associated field is col1. There is a composite index (col1, col2) on Table A, and col1 on Table B is not covered by an index. Then the index coverage score of col1 in Table A is higher, while the index coverage score of col1 in Table B is the lowest.

[0102] Then, calculate the index type matching score based on the index type and the join type of the associated fields. Different types of indexes support different types of join operations to different degrees. For example, B-tree indexes support both equality joins and range joins well, while hash indexes only support equality joins well. If the index type exactly matches the join type, a higher matching weight value is assigned. If the index type does not match the join type, a lower non-matching weight value is assigned.

[0103] For example, if the join type of two tables is an equality join and a B-tree index is established on the associated field of one of the tables, the index type matching score is higher. If a hash index is established on the associated field of the other table, the index type matching score is also higher. However, if the join type is a range join and a hash index is established on the associated field, the index type matching score is lower.

[0104] The index coverage score and the index type matching score are combined with weights to obtain the index matching score.

[0105] Next, calculate the field association score. The cardinality similarity is obtained by calculating the ratio of the cardinalities of the associated fields. The closer the cardinality ratio is to 1, the higher the cardinality similarity. The distribution similarity is obtained by calculating the KL divergence of the distributions of the associated fields. The smaller the KL divergence, the higher the distribution similarity. The null value similarity is obtained by calculating the difference in the null value ratios of the associated fields. The smaller the difference in the null value ratios, the higher the null value similarity. The cardinality similarity, the distribution similarity, and the null value similarity are combined with weights to obtain the field association score.

[0106] For example, if the cardinalities of the associated fields of two tables are 1000 and 1000 respectively, the cardinality similarity is very high. If the value distributions of both fields are close to the normal distribution, the distribution similarity is relatively high. If the null value ratios of both fields are close to 0, the null value similarity is relatively high.

[0107] Finally, the index matching score and the field association score are combined with weights to calculate the inter-table association degree score. The higher this score is, the stronger the association relationship between the two tables is.

[0108] For example, if both the index matching score and the field association score of two tables are high, the inter-table association degree score is high. If one of the scores is low, the inter-table association degree score will decrease accordingly.

[0109] The solution of this application can:

[0110] Improve the efficiency of association queries: By comprehensively considering index and field information, the inter-table association relationship can be evaluated more accurately, so as to select a more efficient association query strategy, such as selecting an appropriate index or adjusting the join order, and finally improve the efficiency of association queries. Optimize database design: The inter-table association degree score can be used as a reference index for database design to help identify potential association relationships in the database and perform corresponding optimizations, such as adding indexes or adjusting the table structure. Assist data analysis: The inter-table association degree score can help data analysts better understand the association relationship between data, so as to perform more in-depth data analysis and mining.

[0111] In an optional implementation manner, the query grouping result and the system resource status sequence are input into a dual-channel neural network model. The first channel of the dual-channel neural network model uses a graph neural network to process the query grouping result and extract inter-table association features, and the second channel uses a recurrent neural network to process the system resource status sequence to capture resource change trends, including:

[0112] Construct a dual-channel neural network model, the dual-channel neural network model includes a first channel and a second channel. The first channel uses a graph neural network to process the query grouping result, and the second channel uses a recurrent neural network to process the system resource status sequence; in the first channel, the query grouping result is constructed into a graph structure. The vertices of the graph structure represent data tables, and the vertices contain feature vectors of table access frequency, table read-write ratio, and table update frequency. The edges of the graph structure represent inter-table association relationships, and the edges contain feature vectors of associated field selection degree ratio, field type matching degree, and connection cost estimation value;

[0113] In the graph neural network of the first channel, the feature vector of the edge is processed by an edge feature encoding layer to generate an embedded representation of the edge; based on the feature vector of the vertex and the embedded representation of the edge, the multi-head attention coefficients between vertices are calculated, and the multi-head attention coefficients are calculated by a trainable attention vector and a feature transformation matrix; the feature vector of the vertex is weighted and aggregated by using the multi-head attention coefficients, and through residual connection and layer normalization processing, a vertex representation reflecting the local structure is generated; the vertex representation is iteratively updated through a multi-layer graph attention network to capture global structure information, and an association feature vector reflecting the inter-table association relationship is generated.

[0114] In the recurrent neural network of the second channel, the system resource status sequence is projected into a hidden layer space through a trainable feature mapping layer; a long short-term memory network is used to model the projected status sequence, and the long short-term memory network includes three gating units: an input gate, a forget gate, and an output gate. The input gate controls the degree of entry of new state information, the forget gate controls the degree of retention of historical state information, and the output gate controls the degree of output of state information; based on the gating signals, the memory cell state and the hidden layer state are updated, and temporal features are extracted through a multi-layer recurrent network to generate a state feature vector reflecting the resource change trend.

[0115] A database query optimization method based on a dual-channel neural network is used to predict the query execution time and select the optimal query execution plan according to the prediction result. The method inputs the query grouping result and the system resource status sequence into two channels of a dual-channel neural network model respectively, extracts the inter-table association features and the resource change trend, and finally predicts the query execution time.

[0116] First, group the query history in the database, and group queries with similar structures and accessing the same tables into one group. The query grouping result includes the set of data tables involved in each query group and the inter-table association relationship. For each data table, features such as table access frequency, table read-write ratio, and table update frequency are extracted to form the feature vector of the vertex. For the inter-table association relationship, features such as the selection degree ratio of the associated field, the field type matching degree, and the connection cost estimation value are extracted to form the feature vector of the edge. For example, query group 1 includes data tables A and B. The access frequency of table A is 0.8, the read-write ratio is 0.9, and the update frequency is 0.1; the access frequency of table B is 0.7, the read-write ratio is 0.5, and the update frequency is 0.2. Tables A and B are associated through field id, the selection degree ratio is 0.6, the field type matching degree is 1, and the connection cost estimation value is 10.

[0117] Next, construct the query grouping results into a graph structure, where vertices represent data tables and edges represent the inter-table association relationships. The vertices contain the feature vectors of table access frequency, table read-write ratio, and table update frequency, and the edges contain the feature vectors of association field selection ratio, field type matching degree, and connection cost estimation value. Taking query group 1 as an example, the constructed graph structure contains two vertices A and B, and an edge connecting A and B.

[0118] Then, input the constructed graph structure into the graph neural network of the first channel for processing. First, process the feature vector of the edge through the edge feature encoding layer to generate the embedded representation of the edge. For example, pass the feature vector (0.6, 1, 10) of the edge connecting A and B through a fully connected layer to obtain the embedded representation (0.2, 0.5, 0.8) of the edge. Then, calculate the multi-head attention coefficients between vertices based on the feature vectors of vertices and the embedded representations of edges. The multi-head attention mechanism performs linear transformations on vertex features and edge embedded representations through multiple attention vectors respectively to obtain multiple attention scores, and then weights and sums these scores to obtain the final multi-head attention coefficients. Use the multi-head attention coefficients to weight and aggregate the feature vectors of vertices, and through residual connection and layer normalization processing, generate vertex representations reflecting the local structure. Finally, iteratively update the vertex representations through a multi-layer graph attention network to capture global structure information and generate association feature vectors reflecting the inter-table association relationships. For example, after passing through the multi-layer graph attention network, the feature vector of vertex A becomes (0.3, 0.7, 0.9), and the feature vector of vertex B becomes (0.2, 0.6, 0.8).

[0119] Meanwhile, input the system resource status sequence into the recurrent neural network of the second channel for processing. The system resource status sequence contains information such as CPU usage rate, memory usage rate, and disk I / O. First, project the system resource status sequence into the hidden layer space through the feature mapping layer. For example, pass the CPU usage rate sequence (0.5, 0.6, 0.7) through a fully connected layer to project it to (0.1, 0.2, 0.3). Then, use the long short-term memory network to model the projected status sequence. The long short-term memory network controls the flow of information through three gating units: input gate, forget gate, and output gate, and updates the memory cell state and the hidden layer state. Finally, extract the temporal features through a multi-layer recurrent network to generate status feature vectors reflecting the resource change trend. For example, after passing through the long short-term memory network, the obtained status feature vector is (0.4, 0.5, 0.6).

[0120] Finally, concatenate the correlation feature vectors generated by the first channel and the state feature vectors generated by the second channel, and input them into a fully connected layer to predict the query execution time. For example, after concatenating the correlation feature vector (0.3, 0.7, 0.9, 0.2, 0.6, 0.8) and the state feature vector (0.4, 0.5, 0.6), input them into the fully connected layer, and the predicted query execution time is 10 seconds.

[0121] The solution of this application can:

[0122] Improve query execution efficiency: By accurately predicting the query execution time and selecting the optimal query execution plan, the query execution time can be reduced, thereby improving the query execution efficiency. Improve resource utilization: By considering the changing trend of the system resource status, the system resources can be allocated more effectively, avoiding resource waste and improving resource utilization. Enhance system stability: By predicting the query execution time, potential performance bottlenecks can be identified in advance and corresponding optimization measures can be taken to enhance the system stability.

[0123] In an alternative embodiment, the output features of the two channels are adaptively fused based on the attention mechanism, and the generation of the comprehensive feature representation includes:

[0124] Construct a dual-channel feature extraction network, the dual-channel feature extraction network includes a multi-head self-attention module, a feature transformation module and a feature fusion module, the multi-head self-attention module includes a trainable query matrix, key matrix and value matrix, the feature transformation module includes a trainable mapping matrix, and the feature fusion module includes a trainable fusion weight;

[0125] Obtain the first feature vector output by the first feature extraction channel and the second feature vector output by the second feature extraction channel, input the first feature vector into the feature transformation module to obtain the first mapped feature, and input the second feature vector into the feature transformation module to obtain the second mapped feature; use the query matrix to transform the first mapped feature into a query representation, use the key matrix to transform the second mapped feature into a key representation, and use the value matrix to transform the second mapped feature into a value representation, and the query representation, the key representation and the value representation have the same feature dimension;

[0126] Calculate the dot product of the query representation and the key representation to obtain a similarity matrix, normalize the similarity matrix to obtain an attention weight matrix, and perform weighted summation on the value representation based on the attention weight matrix to obtain a context feature; splice the context feature and the first mapped feature, calculate a fusion coefficient through the feature fusion module, and perform adaptive weighting on the features based on the fusion coefficient to obtain a comprehensive feature representation.

[0127] A dual-channel feature fusion method based on the attention mechanism is used to generate a comprehensive feature representation, and its specific implementation is as follows:

[0128] First, construct a dual-channel feature extraction network. This network contains three key modules: a multi-head self-attention module, a feature transformation module, and a feature fusion module. The multi-head self-attention module contains three learnable matrices: a query matrix, a key matrix, and a value matrix. The feature transformation module contains a learnable mapping matrix. The feature fusion module contains learnable fusion weights.

[0129] Next, obtain feature vectors. Assume that through two different feature extraction channels, a first feature vector and a second feature vector are obtained respectively. For example, the first feature vector can be a vector [0.1, 0.2,..., 0.256] with a shape of 1x256, and the second feature vector can be a vector [0.3, 0.4,..., 0.812] with a shape of 1x512.

[0130] Then, perform feature transformation. Input the first feature vector into the feature transformation module and perform a linear transformation using the mapping matrix to obtain a first mapped feature. Assume that the dimension of the mapping matrix is 256x256, then the dimension of the obtained first mapped feature is also 1x256. Similarly, input the second feature vector into the feature transformation module and perform a linear transformation using another mapping matrix with a dimension of 512x256 to obtain a second mapped feature with a dimension of 1x256.

[0131] After that, calculate the attention weights. Use the query matrix, key matrix, and value matrix in the multi-head self-attention module to transform the first mapped feature into a query representation, and transform the second mapped feature into a key representation and a value representation. The dimensions of these three representations are the same, for example, all 1x256. Calculate the dot product of the query representation and the key representation to obtain a similarity matrix. Perform a normalization process on the similarity matrix, for example, using the Softmax function, to obtain an attention weight matrix. Assume that the obtained attention weight matrix is 256x256.

[0132] Subsequently, generate context features. Use the attention weight matrix to perform a weighted sum on the value representation to obtain context features. The dimension of the context features is the same as the dimension of the value representation, for example, all 1x256.

[0133] Next, feature concatenation and fusion are performed. The context feature and the first mapped feature are concatenated to obtain a concatenated feature with a dimension of 1x512. The concatenated feature is input into the feature fusion module to calculate the fusion coefficient. For example, a fully connected layer can be used to learn the fusion coefficient. Assume the fusion coefficient is [0.6, 0.4]. Based on the fusion coefficient, the two parts of the concatenated feature (context feature and first mapped feature) are weighted to obtain the final comprehensive feature representation with a dimension of 1x256.

[0134] Finally, the comprehensive feature representation is output. This comprehensive feature representation fuses the information of two channels and adaptively highlights the part in the second channel that is relevant to the first channel through the attention mechanism.

[0135] The solution of this application can:

[0136] Improve the feature representation ability: By fusing the feature information of two channels, a more comprehensive and rich feature representation can be obtained, thereby improving the performance of the model. Enhance feature correlation: The introduction of the attention mechanism enables the model to focus on the part in the second channel that is more relevant to the first channel, thereby enhancing the correlation between the features of the two channels. Achieve adaptive fusion: The fusion coefficient in the feature fusion module is learnable, enabling the model to adaptively adjust the fusion method of the features of the two channels according to different input data, thereby improving the generalization ability of the model.

[0137] In an alternative embodiment, the comprehensive feature representation is input into the decision layer network, and a query optimization solution including a distributed execution plan, a data sharding strategy, and a resource allocation scheme is output. The query optimization solution serves as the scheduling basis for the execution engine and includes:

[0138] Construct a hierarchical decision network. The hierarchical decision network includes an execution plan generation layer, a sharding strategy generation layer, and a resource allocation layer. The execution plan generation layer includes an operator dependency analysis unit and a parallelism prediction unit. The sharding strategy generation layer includes a sharding key selection unit and a sharding boundary calculation unit. The resource allocation layer includes a resource demand prediction unit and a resource scheduling unit;

[0139] Input the comprehensive feature representation into the execution plan generation layer. The operator dependency analysis unit analyzes the data flow dependency relationship between operators to obtain an operator dependency graph. The parallelism prediction unit predicts the parallelism of each operator based on the operator dependency graph to obtain a distributed execution plan;

[0140] Input the comprehensive feature representation and the distributed execution plan into the sharding strategy generation layer. The sharding key selection unit selects the optimal sharding key based on the data distribution characteristics. The sharding boundary calculation unit determines the sharding boundary based on the data skew degree to obtain a data sharding strategy;

[0141] Input the comprehensive feature representation, the distributed execution plan, and the data sharding strategy into the resource allocation layer. The resource demand prediction unit predicts the computing and storage resource demands, and the resource scheduling unit generates a resource allocation plan based on the resource utilization rate.

[0142] Combine the distributed execution plan, the data sharding strategy, and the resource allocation plan to form a query optimization plan, which is used to guide the distributed execution engine for task scheduling.

[0143] A distributed database query optimization method based on a hierarchical decision network aims to improve query execution efficiency and reduce resource consumption. This method generates a query optimization plan containing a distributed execution plan, a data sharding strategy, and a resource allocation plan by comprehensively analyzing query features, data distribution, and resource status, and guides the execution engine for task scheduling.

[0144] First, collect the comprehensive feature representation of the query to be optimized. These features include but are not limited to: the syntax tree structure of the query statement, the table and column information involved, filtering conditions and aggregation operations, data scale and data distribution characteristics, the resource configuration and current load of the cluster, etc. For example, a query involves two tables, namely the order table and the product table. The query condition is that the order amount is greater than 1000 yuan and the product category is "electronic products", and it is necessary to count the number of orders that meet the conditions. The collected feature information includes: the join operation of the two tables, the filtering condition, the aggregation operation (COUNT), the data scale (assuming 100 million records in the order table and 1 million records in the product table), the data distribution (assuming that both the order amount and the product category follow a uniform distribution), and the cluster resources (assuming that the cluster has 10 nodes, each node has 16GB of memory and 8 cores of CPU).

[0145] Next, construct a hierarchical decision network. This network includes three core layers: the execution plan generation layer, the sharding strategy generation layer, and the resource allocation layer. The execution plan generation layer includes an operator dependency analysis unit and a parallelism prediction unit. The sharding strategy generation layer includes a sharding key selection unit and a sharding boundary calculation unit. The resource allocation layer includes a resource demand prediction unit and a resource scheduling unit.

[0146] Input the comprehensive feature representation into the execution plan generation layer. The operator dependency analysis unit analyzes the data flow dependency relationships among the operators (such as scan, join, filter, aggregation, etc.) in the query statement and constructs an operator dependency graph. For example, the operator dependency graph of the above query includes operators such as scanning the order table, scanning the product table, joining the two tables, filtering the order amount, filtering the product category, and counting the order quantity, and reflects the dependency relationships among them. The parallelism prediction unit predicts the optimal parallelism of each operator based on the operator dependency graph and the data scale, and generates a distributed execution plan. For example, the predicted parallelism of scanning the order table is 8, and the predicted parallelism of scanning the product table is 2.

[0147] Input the comprehensive feature representation and the distributed execution plan into the sharding strategy generation layer. The sharding key selection unit selects the optimal sharding key based on the data distribution characteristics. For example, according to the uniform distribution of the order amount and the product category, the order ID is selected as the sharding key. The sharding boundary calculation unit determines the sharding boundary based on the data skew degree to ensure that the size of each data shard is as balanced as possible, and generates a data sharding strategy. For example, the order table is evenly divided into 8 shards, and the product table is evenly divided into 2 shards.

[0148] Input the comprehensive feature representation, the distributed execution plan, and the data sharding strategy into the resource allocation layer. The resource requirement prediction unit predicts the requirements for computing resources (number of CPU cores) and storage resources (memory) according to the parallelism of the operators, the data scale, and the sharding strategy. For example, it is predicted that scanning the order table requires 8 CPU cores and 8GB of memory. The resource scheduling unit allocates specific computing and storage resources to each operator based on the resource utilization rate of the cluster and the predicted resource requirements, and generates a resource allocation plan. For example, the tasks of scanning the order table 8 times are respectively assigned to 8 different nodes for execution.

[0149] Finally, combine the distributed execution plan, the data sharding strategy, and the resource allocation plan to form a complete query optimization plan. This plan will guide the distributed execution engine to perform task scheduling. For example, the task of scanning the order table is assigned to the specified node, and the corresponding computing process is started.

[0150] The solution of this application can:

[0151] Improve query execution efficiency: By executing in parallel and sharding data, the response time of the query can be significantly reduced, and the query throughput can be improved. Reduce resource consumption: Through reasonable resource allocation and scheduling, resource waste can be avoided, and the execution cost of the query can be reduced. Enhance system scalability: Through flexible parallelism adjustment and data sharding strategies, it can adapt to different scales of data and clusters, and improve the system scalability.

[0152] In an alternative embodiment, the distributed execution plan, the data sharding strategy, and the resource allocation scheme are combined to form a query optimization scheme, and the query optimization scheme is used to guide the distributed execution engine for task scheduling, including:

[0153] Construct a multi-level optimization combination system, which includes a plan parsing unit, a data mapping unit, and a resource allocation unit. The outputs of the plan parsing unit, the data mapping unit, and the resource allocation unit are respectively connected to the combination optimization unit;

[0154] The plan parsing unit receives the distributed execution plan, extracts the data flow relationship between operators to construct a directed acyclic graph, calculates the optimal parallelism of each operator node based on the operator calculation complexity and the intermediate result scale, and marks the data flow dependency relationship in the directed acyclic graph to obtain the execution skeleton information;

[0155] The data mapping unit receives the data sharding strategy, establishes the mapping relationship from data shards to physical nodes based on the operator distribution in the directed acyclic graph, calculates the data distribution density of each shard, and generates an initial shard routing table;

[0156] The resource allocation unit receives the resource allocation scheme, and allocates computing resource quotas and storage resource limits for each operator node according to the parallelism information marked in the directed acyclic graph, and generates a resource configuration table;

[0157] The combination optimization unit hierarchically combines the execution skeleton information, the initial shard routing table, and the resource configuration table to construct a query optimization scheme including an execution dependency layer, a data distribution layer, and a resource configuration layer;

[0158] Construct an adaptive scheduling system, which includes a task parser, a dynamic optimizer, and an execution monitor. The input end of the task parser is connected to the output end of the combination optimization unit; the task parser converts operator nodes into specific execution tasks based on the execution dependency layer in the query optimization scheme, determines the data locality of the tasks according to the data distribution layer, and generates task scheduling instructions based on the resource configuration layer.

[0159] A distributed query optimization method for guiding a distributed execution engine for task scheduling, and its specific implementation is as follows:

[0160] First, construct a multi-level optimization combination system. This system includes a plan parsing unit, a data mapping unit, and a resource allocation unit, and the outputs of the three units are respectively connected to the combination optimization unit.

[0161] The plan parsing unit receives the distributed execution plan as input. It first extracts the data flow relationships between operators and constructs a directed acyclic graph (DAG). Then, based on the computational complexity of each operator and the scale of intermediate results, it calculates the optimal parallelism for each operator node. For example, for an operator that needs to perform sorting, a higher parallelism is required if the data volume is large. Finally, it marks the data flow dependency relationships in the DAG. For example, if the output of operator A is the input of operator B, then the edge from A to B is marked in the DAG. The final output is the execution skeleton information, including the DAG, the optimal parallelism for each operator, and the data flow dependency relationships.

[0162] The data mapping unit receives the data sharding strategy as input. Based on the operator distribution in the execution skeleton information, it establishes the mapping relationship between data shards and physical nodes. For example, if a table is divided into 10 shards and there are 5 computing nodes, then these shards need to be mapped to these nodes. At the same time, it calculates the data distribution density of each shard. For example, if a certain shard has a large data volume, then its data distribution density is high. The final output is the initial shard routing table, including the mapping relationship between shards and physical nodes and the data distribution density of each shard. A specific example is that table A is divided into 3 shards A1, A2, A3, which are stored on nodes N1, N2, N3 respectively, and the data volumes of A1, A2, A3 are 1GB, 2GB, 1GB respectively, then the initial shard routing table records this information.

[0163] The resource allocation unit receives the resource allocation plan as input. According to the parallelism information marked in the execution skeleton information, it allocates the computing resource quota and storage resource limit for each operator node. For example, if the parallelism of an operator is 4, then 4 CPU cores and the corresponding memory need to be allocated for it. The final output is the resource configuration table, including the computing resource quota and storage resource limit for each operator node. A specific example is that the parallelism of operator B is 2, then 2 CPU cores and 4GB of memory are allocated for it.

[0164] The combination optimization unit receives the execution skeleton information, the initial shard routing table, and the resource configuration table as input. It hierarchically combines these three pieces of information to construct a query optimization plan that includes an execution dependency layer, a data distribution layer, and a resource configuration layer. The execution dependency layer describes the dependency relationships between operators, the data distribution layer describes the data distribution situation, and the resource configuration layer describes the resource allocation situation. The final output is the query optimization plan.

[0165] Next, an adaptive scheduling system is constructed. The system includes a task parser, a dynamic optimizer, and an execution monitor. The input end of the task parser is connected to the output end of the combination optimization unit.

[0166] The task parser converts operator nodes into specific execution tasks based on the execution dependency layer in the query optimization scheme. For example, it converts a sorting operator into a sorting task. It determines the data locality of the task according to the data distribution layer. For example, if the data to be processed by a task is located on node N1, then the task is scheduled to be executed on N1. It generates task scheduling instructions based on the resource configuration layer. For example, it allocates 2 CPU cores and 2GB of memory for a task.

[0167] The dynamic optimizer and the execution monitor work together to dynamically adjust the query optimization scheme according to the actual execution situation. The execution monitor monitors the execution situation of tasks. For example, it monitors the execution time and resource consumption of tasks. If it is found that a certain task is executing slowly, the dynamic optimizer will adjust the query optimization scheme. For example, it increases the parallelism of the task or allocates more resources to it.

[0168] The solution of this application can:

[0169] Improve query execution efficiency: Through the multi-level optimization combination system and the adaptive scheduling system, the resources of the distributed cluster can be fully utilized, and the query plan can be dynamically adjusted according to the actual execution situation, thereby improving the query execution efficiency. Reduce resource consumption: Through refined resource allocation and data locality optimization, unnecessary network transmission and computing overhead can be reduced, thereby reducing resource consumption. Enhance system stability: Through the dynamic optimization and monitoring mechanism, potential performance bottlenecks can be discovered and solved in a timely manner, thereby enhancing the system stability.

[0170] Figure 2 It is a schematic structural diagram of the intelligent routing optimization system for multi-table association query in the embodiment of the present invention. As Figure 2 shown, the system includes:

[0171] The first unit is used to receive a multi-table association query request, extract static features and dynamic features in the multi-table association query request to construct a multi-dimensional feature vector, where the static features include the number of tables participating in the query, the field selection degree, and the index type, and the dynamic features include the system load status, the memory occupancy rate, and the IO queue depth; calculate the table association degree score based on the static features, generate a query association degree matrix, and group and divide the data tables participating in the query according to the query association degree matrix to obtain a query grouping result; at the same time, input the dynamic features into a pre-constructed time series prediction model to generate a system resource status sequence within the predicted time window;

[0172] A second unit, configured to input the query grouping result and the system resource status sequence into a dual-channel neural network model. The first channel of the dual-channel neural network model processes the query grouping result using a graph neural network to extract inter-table association features, and the second channel processes the system resource status sequence using a recurrent neural network to capture resource change trends. Based on an attention mechanism, the output features of the two channels are adaptively fused to generate a comprehensive feature representation. The comprehensive feature representation is input into a decision layer network to output a query optimization solution including a distributed execution plan, a data sharding strategy, and a resource allocation scheme, and the query optimization solution is used as the scheduling basis for the execution engine.

[0173] A third unit, configured to send the query optimization solution to the execution engine, set performance monitoring points during the execution process, and collect execution status information in real time. When it is detected that the execution efficiency decreases, the latest collected status information is combined with the original feature vector and re-input into the dual-channel neural network model for optimization solution adjustment. Record the execution history information during the entire query process, where the execution history information includes the execution path, resource usage, performance metrics, and optimization effects. Sort the execution history information according to the performance improvement amplitude, and select the execution history information with a higher ranking to construct an incremental training set. Based on the incremental training set, perform online update on the dual-channel neural network model to improve the adaptability of the model to different query scenarios through adjusting network parameters, forming a closed-loop optimization.

[0174] In the third aspect of the embodiments of the present invention,

[0175] A kind of electronic device is provided, including:

[0176] A processor;

[0177] A memory for storing instructions executable by the processor;

[0178] Wherein, the processor is configured to call the instructions stored in the memory to execute the method described above.

[0179] In the fourth aspect of the embodiments of the present invention,

[0180] A computer-readable storage medium is provided, on which computer program instructions are stored, and when the computer program instructions are executed by a processor, the method described above is implemented.

[0181] The present invention can be a method, a device, a system, and / or a computer program product. The computer program product may include a computer-readable storage medium, on which computer-readable program instructions for executing various aspects of the present invention are uploaded.

[0182] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit them; although the present invention has been described in detail with reference to the foregoing embodiments, those of ordinary skill in the art should understand that they can still modify the technical solutions described in the foregoing embodiments, or perform equivalent replacements on some or all of the technical features; and these modifications or replacements do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present invention.

Claims

1. An intelligent routing optimization method for multi-table associated query, characterized in that Including: Receiving a multi-table association query request, extracting static features and dynamic features in the multi-table association query request to construct a multi-dimensional feature vector, where the static features include the number of tables participating in the query, field selection degree, and index type, and the dynamic features include system load status, memory occupancy rate, and IO queue depth; calculating the inter-table association degree score based on the static features, generating a query association matrix, and grouping and partitioning the data tables participating in the query according to the query association matrix to obtain a query grouping result; at the same time, inputting the dynamic features into a pre-constructed time series prediction model to generate a system resource status sequence within a predicted time window; Inputting the query grouping result and the system resource status sequence into a dual-channel neural network model. The first channel of the dual-channel neural network model uses a graph neural network to process the query grouping result and extract inter-table association features, and the second channel uses a recurrent neural network to process the system resource status sequence to capture resource change trends; adaptively fusing the output features of the two channels based on the attention mechanism to generate a comprehensive feature representation; Inputting the comprehensive feature representation into a decision layer network, and outputting a query optimization plan including a distributed execution plan, a data sharding strategy, and a resource allocation plan. The query optimization plan is used as the scheduling basis for the execution engine; Sending the query optimization plan to the execution engine, setting performance monitoring points during the execution process, and collecting execution status information in real time. When it is detected that the execution efficiency decreases, combining the latest collected status information with the original feature vector and re-inputting them into the dual-channel neural network model for optimization plan adjustment; recording the execution history information during the entire query process, where the execution history information includes the execution path, resource usage, performance metrics, and optimization effects, sorting the execution history information according to the performance improvement amplitude, and selecting the execution history information with a higher ranking to construct an incremental training set; Based on the incremental training set, online updating the dual-channel neural network model, and improving the adaptability of the model to different query scenarios by adjusting network parameters to form a closed-loop optimization.

2. The method according to claim 1, wherein Calculating the inter-table association degree score based on the static features, generating a query association matrix, and grouping and partitioning the data tables participating in the query according to the query association matrix. The obtained query grouping result includes: Extracting the data features of the association fields and the index information of the association fields from the static features; calculating the field association score based on the data features of the association fields, where the field association score is weighted and calculated through the selection degree ratio of the association fields, the data type matching degree, and the data distribution similarity; calculating the index matching score based on the index information of the association fields, where the index matching score is determined according to the index coverage and the index type matching degree; combining the field association score and the index matching score to calculate the inter-table association degree score; Construct a query correlation matrix, where the dimension of the query correlation matrix is equal to the number of tables participating in the query. Fill the inter-table correlation scores into the corresponding positions of the query correlation matrix. The query correlation matrix satisfies symmetry. Normalize the query correlation matrix to obtain a standardized correlation matrix; construct an inter-table distance matrix based on the standardized correlation matrix, where the element value of the inter-table distance matrix is the inverse function of the correlation score; Perform grouping based on the inter-table distance matrix. Form an initial group with the table pair having the smallest distance. Use an iterative method to calculate the average inter-group distance between the ungrouped tables and the existing groups. When the average inter-group distance is less than the first preset threshold, add the ungrouped tables to the corresponding group. When the average inter-group distance is greater than the first preset threshold, form a new group to obtain the query grouping result.

3. The method according to claim 2, wherein Calculate the index matching score based on the index information of the associated fields. The index matching score is determined according to the index coverage and the degree of index type matching; the combination calculation of the field association score and the index matching score to obtain the inter-table association score includes: Receive the index information and associated fields of two data tables to be associated. The index information includes index type, index column composition, and index key value distribution. The associated fields include field cardinality, field distribution characteristics, and field null value ratio; Determine the index coverage of the associated fields based on the index information. Assign different weight values to different coverage degrees according to the position of the associated fields in the index. Assign the first weight value when the associated field is an index prefix column, assign the second weight value when the associated field is a non-index prefix column, and assign the third weight value when the associated field is not covered by the index. Perform a weighted combination of the first weight value, the second weight value, and the third weight value to generate an index coverage score; The formula for calculating the index coverage score is as follows: ； Among them, IC(f) is the index coverage score for the associated field f, w1 is the first weight value representing the weight of the index prefix, w2 is the second weight value representing the weight of the non-prefix index column, w3 is the third weight value representing the weight of the non-index-covered part, and I prefix (f) is the first indicator function, which takes the value of 1 when the associated field f is an index prefix column and 0 otherwise, and I nonPrefix (f) is the second indicator function, which takes the value of 1 when the associated field f is a non-index prefix column and 0 otherwise, and I nonCovered (f) is the third indicator function, which takes the value of 1 when the associated field f is not covered by the index and 0 otherwise; Calculate the index type matching score based on the index type and the connection type of the associated fields. Assign different weight values according to the support degree of different index types for connection operations. Assign a matching weight value higher than the preset weight value when the index type and the connection type are completely matched, and assign a non-matching weight value lower than the preset weight value when the index type and the connection type do not match; The formula for calculating the index type matching score is as follows: ； Among them, ITM(f) is the index type matching score for the associated field f, n is the total number of supported index types, w i is the weight value of the i-th index type, I i is the i-th index type, J f is the connection type of the associated field f, M(I i , J f ) is the matching degree score between the index type I i and the connection type J f ; Perform a weighted combination of the index coverage score and the index type matching score to obtain the index matching score; calculate the field association score based on the associated fields. Obtain the cardinality similarity by calculating the cardinality ratio of the associated fields, obtain the distribution similarity by calculating the distribution KL divergence of the associated fields, obtain the null value similarity by calculating the difference in the null value ratio of the associated fields. Perform a weighted combination of the cardinality similarity, the distribution similarity, and the null value similarity to obtain the field association score; perform a weighted combination calculation of the index matching score and the field association score to obtain the inter-table association score.

4. The method according to claim 1, wherein Input the query grouping result and the system resource status sequence into a dual-channel neural network model. The first channel of the dual-channel neural network model uses a graph neural network to process the query grouping result and extract inter-table association features. The second channel uses a recurrent neural network to process the system resource status sequence and capture the resource change trend, including: Construct a dual-channel neural network model. The dual-channel neural network model includes a first channel and a second channel. The first channel uses a graph neural network to process the query grouping result, and the second channel uses a recurrent neural network to process the system resource status sequence. In the first channel, construct the query grouping result into a graph structure. The vertices of the graph structure represent data tables, and the vertices contain feature vectors of table access frequency, table read-write ratio, and table update frequency. The edges of the graph structure represent inter-table association relationships, and the edges contain feature vectors of association field selection ratio, field type matching degree, and connection cost estimation value. In the graph neural network of the first channel, process the feature vector of the edge through an edge feature encoding layer to generate an embedded representation of the edge. Calculate the multi-head attention coefficients between vertices based on the feature vector of the vertex and the embedded representation of the edge. The multi-head attention coefficients are calculated through a trainable attention vector and a feature transformation matrix. Use the multi-head attention coefficients to weight-aggregate the feature vectors of the vertices, and through residual connection and layer normalization processing, generate vertex representations reflecting the local structure. Iteratively update the vertex representations through a multi-layer graph attention network to capture global structure information and generate association feature vectors reflecting inter-table association relationships. In the recurrent neural network of the second channel, project the system resource status sequence into the hidden layer space through a trainable feature mapping layer. Use a long short-term memory network to model the projected status sequence. The long short-term memory network includes three gated units: an input gate, a forget gate, and an output gate. The input gate controls the degree of entry of new state information, the forget gate controls the degree of retention of historical state information, and the output gate controls the degree of output of state information. Update the memory cell state and the hidden layer state based on the gating signal, and extract temporal features through a multi-layer recurrent network to generate state feature vectors reflecting the resource change trend.

5. The method according to claim 1, wherein Based on the attention mechanism, adaptively fuse the output features of the two channels to generate a comprehensive feature representation, including: Construct a dual-channel feature extraction network. The dual-channel feature extraction network includes a multi-head self-attention module, a feature transformation module, and a feature fusion module. The multi-head self-attention module includes a trainable query matrix, key matrix, and value matrix. The feature transformation module includes a trainable mapping matrix. The feature fusion module includes a trainable fusion weight. Obtain the first feature vector output by the first feature extraction channel and the second feature vector output by the second feature extraction channel. Input the first feature vector into the feature transformation module to obtain a first mapped feature, and input the second feature vector into the feature transformation module to obtain a second mapped feature; Use the query matrix to transform the first mapped feature into a query representation, use the key matrix to transform the second mapped feature into a key representation, and use the value matrix to transform the second mapped feature into a value representation. The query representation, the key representation, and the value representation have the same feature dimension; Calculate the dot product of the query representation and the key representation to obtain a similarity matrix, perform normalization processing on the similarity matrix to obtain an attention weight matrix, and perform weighted summation on the value representation based on the attention weight matrix to obtain a context feature; Concatenate the context feature and the first mapped feature, calculate a fusion coefficient through the feature fusion module, and perform adaptive weighting on the features based on the fusion coefficient to obtain a comprehensive feature representation.

6. The method according to claim 1, characterized in that Input the comprehensive feature representation into the decision layer network, and output a query optimization plan including a distributed execution plan, a data sharding strategy, and a resource allocation plan. The query optimization plan, as the scheduling basis for the execution engine, includes: Construct a hierarchical decision network. The hierarchical decision network includes an execution plan generation layer, a sharding strategy generation layer, and a resource allocation layer. The execution plan generation layer includes an operator dependency analysis unit and a parallelism prediction unit. The sharding strategy generation layer includes a sharding key selection unit and a sharding boundary calculation unit. The resource allocation layer includes a resource demand prediction unit and a resource scheduling unit; Input the comprehensive feature representation into the execution plan generation layer. The operator dependency analysis unit analyzes the data flow dependency relationship between operators to obtain an operator dependency graph, and the parallelism prediction unit predicts the parallelism of each operator based on the operator dependency graph to obtain a distributed execution plan; Input the comprehensive feature representation and the distributed execution plan into the sharding strategy generation layer. The sharding key selection unit selects the optimal sharding key based on the data distribution characteristics, and the sharding boundary calculation unit determines the sharding boundary based on the data skew degree to obtain a data sharding strategy; Input the comprehensive feature representation, the distributed execution plan, and the data sharding strategy into the resource allocation layer. The resource demand prediction unit predicts the computing and storage resource requirements, and the resource scheduling unit generates a resource allocation plan based on the resource utilization rate; Combine the distributed execution plan, the data sharding strategy, and the resource allocation plan to form a query optimization plan. The query optimization plan is used to guide the distributed execution engine to perform task scheduling.

7. The method according to claim 6, wherein Combine the distributed execution plan, the data sharding strategy, and the resource allocation plan to form a query optimization plan. The query optimization plan, which is used to guide the distributed execution engine to perform task scheduling, includes: Build a multi-level optimization combination system, where the multi-level optimization combination system includes a plan parsing unit, a data mapping unit, and a resource allocation unit, and the outputs of the plan parsing unit, the data mapping unit, and the resource allocation unit are respectively connected to a combination optimization unit; The plan parsing unit receives the distributed execution plan, extracts the data flow relationship between operators to construct a directed acyclic graph, calculates the optimal parallelism of each operator node based on the operator calculation complexity and the intermediate result scale, and marks the data flow dependency relationship in the directed acyclic graph to obtain execution skeleton information; The data mapping unit receives the data sharding strategy, establishes a mapping relationship from data shards to physical nodes based on the operator distribution in the directed acyclic graph, calculates the data distribution density of each shard, and generates an initial shard routing table; The resource allocation unit receives the resource allocation plan, and according to the parallelism information marked in the directed acyclic graph, allocates computing resource quotas and storage resource limits for each operator node, and generates a resource configuration table; The combination optimization unit hierarchically combines the execution skeleton information, the initial shard routing table, and the resource configuration table to construct a query optimization plan including an execution dependency layer, a data distribution layer, and a resource configuration layer; Build an adaptive scheduling system, where the adaptive scheduling system includes a task parser, a dynamic optimizer, and an execution monitor, and the input end of the task parser is connected to the output end of the combination optimization unit; the task parser converts operator nodes into specific execution tasks based on the execution dependency layer in the query optimization plan, determines the data locality of the tasks according to the data distribution layer, and generates task scheduling instructions based on the resource configuration layer.

8. An intelligent routing optimization system for multi-table association queries, which is used to implement the method described in any one of the foregoing claims 1-7, characterized in that Including: The first unit is used to receive a multi-table association query request, extract static features and dynamic features in the multi-table association query request to construct a multi-dimensional feature vector, where the static features include the number of tables participating in the query, the field selection degree, and the index type, and the dynamic features include the system load status, the memory occupancy rate, and the IO queue depth; calculate the table association degree score based on the static features, generate a query association degree matrix, and group and divide the data tables participating in the query according to the query association degree matrix to obtain a query grouping result; at the same time, input the dynamic features into a pre-constructed time series prediction model to generate a system resource status sequence within the predicted time window; The second unit is used to input the query grouping result and the system resource status sequence into a dual-channel neural network model. The first channel of the dual-channel neural network model uses a graph neural network to process the query grouping result and extract inter-table association features, and the second channel uses a recurrent neural network to process the system resource status sequence and capture the resource change trend; adaptively fuse the output features of the two channels based on the attention mechanism to generate a comprehensive feature representation; Input the comprehensive feature representation into a decision layer network, and output a query optimization plan including a distributed execution plan, a data sharding strategy, and a resource allocation plan. The query optimization plan is used as the scheduling basis for the execution engine; A third unit is used to send the query optimization scheme to an execution engine, set performance monitoring points during execution, collect execution status information in real time, and when a decrease in execution efficiency is detected, combine the latest collected status information with the original feature vector and re-enter the combined information into the dual-channel neural network model for optimization scheme adjustment; record the execution history information during the entire query process, where the execution history information includes the execution path, resource usage, performance metrics, and optimization effects, sort the execution history information according to the magnitude of performance improvement, and select the execution history information with a higher ranking to construct an incremental training set; Based on the incremental training set, perform online update on the dual-channel neural network model, and improve the adaptability of the model to different query scenarios by adjusting network parameters, thus forming a closed-loop optimization.

9. An electronic device, characterized in that, It includes: A processor; A memory for storing instructions executable by the processor; Wherein, the processor is configured to call the instructions stored in the memory to execute the method according to any one of claims 1 to 7.

10. A computer-readable storage medium having computer program instructions stored thereon, characterized in that, When the computer program instructions are executed by the processor, the method according to any one of claims 1 to 7 is implemented.

Images