Adaptive query optimization scheduling method and system for lung cancer research database

Abstract

The invention discloses an adaptive query optimization scheduling method and system for a lung cancer research database, and belongs to the technical field of database query optimization, and the method comprises the steps: generating a query semantic feature vector based on a graph attention network through a query semantic graph construction module; generating a cardinality estimation value and an estimation confidence coefficient by adopting an attention-enhanced deep set network through an adaptive cardinality estimation module; a layered reinforcement learning framework is adopted to generate an optimization execution plan through a dynamic execution scheduling module; according to the invention, a deep coupling closed-loop cooperative system is formed among the modules, nonlinear growth of technical effects is realized through mutual promotion and superposition synergy among the modules, and the execution time of complex association query is reduced by more than 85%.

Description

Technical Field

[0001] This invention relates to the field of database query optimization technology, and in particular to an adaptive query optimization scheduling method and system for lung cancer research databases. Background Technology

[0002] With the rapid growth of data volume in the field of lung cancer research, lung cancer research databases store a large amount of heterogeneous data from multiple sources, including patient information, genetic data, pathology reports, imaging data, and follow-up records. These data tables have complex relationships, requiring researchers to frequently execute complex queries involving multiple table joins for data analysis and scientific research. However, traditional query optimization techniques often fail to provide satisfactory query performance when faced with this type of large-scale, high-dimensional, and strongly correlated medical research data.

[0003] In the prior art, Chinese patent application CN117609288A discloses a data query strategy optimization method, apparatus, terminal device, and storage medium. This technology constructs a data management system comprising a parser, agent, optimizer, and executor, and utilizes a recurrent neural network LSTM and a proximal policy optimization algorithm (PPO) to optimize the query strategy. Specifically, the method first parses the SQL statement into an abstract syntax tree and performs vectorization to obtain an initial state. Then, the agent generates an initial policy function and a value function based on the LSTM network. Finally, the reward value is calculated based on the execution result, and the policy parameters are updated to generate the optimized query strategy. This technology improves the optimization efficiency of multi-table join queries to a certain extent.

[0004] However, the aforementioned existing technologies have the following shortcomings: First, although the LSTM network used in this method can process sequential data, it struggles to effectively capture the complex structured semantic relationships and table join topology features in query statements, thus limiting the accuracy of query representation learning. Second, the cost estimation model of this method primarily focuses on hardware resource indicators such as CPU usage and memory consumption, lacking the ability to accurately estimate the cardinality of the query result set, while the accuracy of cardinality estimation directly affects the optimization quality of the query plan. Third, this method uses a single reinforcement learning framework for end-to-end optimization, lacking a deep-coupling collaborative mechanism between optimization stages, failing to achieve mutual promotion and synergistic effects between modules. Furthermore, this method does not consider the data access patterns and query load characteristics of specific application domains, lacking targeted adaptive scheduling strategies. These limitations make it difficult for this technology to achieve ideal query optimization results when processing large-scale scientific research data with domain specificity, such as lung cancer research databases.

[0005] Therefore, there is an urgent need for an adaptive query optimization method that can deeply understand the semantic structure of queries, accurately estimate the query cardinality, dynamically schedule execution strategies, and form a deep-coupled closed-loop collaboration among various optimization modules, in order to meet the urgent need for efficient retrieval and analysis of lung cancer research databases. Summary of the Invention

[0006] The purpose of this invention is to provide an adaptive query optimization scheduling method and system for lung cancer research databases, aiming to solve the technical problems of insufficient semantic understanding, inaccurate cardinality estimation, and insufficient module collaboration in existing query optimization techniques when processing lung cancer research databases.

[0007] To achieve the above objectives, this invention provides an adaptive query optimization scheduling method for a lung cancer research database, comprising the following steps:

[0008] The semantic graph construction module constructs semantic graphs and extracts features from the input SQL query statement based on graph attention network, generating query semantic feature vectors. Specifically, the query relationship graph is constructed by parsing the abstract syntax tree of the query statement, the semantic representation of table nodes and predicate edges is learned by the multi-head attention mechanism of graph attention network, and neighborhood information is aggregated based on message passing mechanism to capture the topological structure features of multi-table connections.

[0009] The adaptive cardinality estimation module estimates the cardinality of the query result set based on the query semantic feature vector and the statistical metadata of the lung cancer research database, using an attention-enhanced deep ensemble network to generate cardinality estimates and estimated confidence levels. The self-attention mechanism is used to weight the importance of each data table involved in the query, and the estimation parameters are dynamically adjusted by incorporating historical query execution feedback information.

[0010] Through the dynamic execution scheduling module, based on the query semantic feature vector, cardinality estimate and estimated confidence, a hierarchical reinforcement learning framework is used to generate query execution plans and optimize scheduling strategies, generating optimized execution plans. The upper-layer policy network learns macro-schedule decisions based on gated recurrent units, and the lower-layer policy network learns specific operator selection based on graph convolutional networks. The global optimization of the execution plan is achieved through the synergistic effect of the two-layer strategies.

[0011] Through the closed-loop feedback adjustment module, based on the actual execution results of the optimized execution plan, the parameters of the query semantic graph construction module, the adaptive cardinality estimation module, and the dynamic execution scheduling module are updated in reverse using an online learning mechanism, forming a closed-loop feedback adjustment. In this process, a multi-objective loss function is constructed by calculating the execution cost deviation and cardinality estimation error, and gradient backpropagation is used to achieve collaborative optimization of the parameters of each module. This allows the output quality of the front-end module and the decision accuracy of the back-end module to promote each other and achieve synergistic effects.

[0012] Preferably, the steps of constructing a semantic graph and extracting features from the input SQL query statement based on the graph attention network include:

[0013] Perform lexical and syntactic analysis on SQL query statements to generate an abstract syntax tree;

[0014] Traverse the abstract syntax tree to extract the set of data tables, the set of join conditions, and the set of filter predicates involved in the query;

[0015] A query relationship graph is constructed using data tables as nodes and join conditions as edges. The node features include table statistics, index distribution, and historical access frequency, while the edge features include the selectivity of the join key and the data type.

[0016] The query relationship graph is subjected to feature learning through a multi-layer graph attention network, wherein each layer of the network calculates the attention weights between nodes through a multi-head attention mechanism and updates the node representation based on attention-weighted neighborhood aggregation.

[0017] Graph-level pooling is performed on all node representations in the final layer to generate a global query semantic feature vector.

[0018] Preferably, the step of estimating the cardinality of the query result set using an attention-enhanced deep ensemble network includes:

[0019] Based on the query semantic feature vector, identify the data tables involved in the query and their filtering conditions;

[0020] Extract the number of rows, cardinality of columns, data distribution histogram, and correlation matrix of each table from the statistical metadata of the lung cancer research database;

[0021] For each data table, a local feature vector is constructed, which integrates table statistics, filter selectivity, and index coverage.

[0022] The importance weight of each table in the current query is calculated through a self-attention mechanism, and the local feature vectors are aggregated based on the importance weight.

[0023] The aggregated global features are input into a multilayer perceptron, which outputs the logarithm of the cardinality estimate and the estimated confidence score.

[0024] Preferably, the steps of generating query execution plans and optimizing scheduling strategies using a hierarchical reinforcement learning framework include:

[0025] The upper-layer policy network receives the query semantic feature vector and cardinality estimate, learns the macro-scheduling strategy for query execution through the gated recurrent unit, and outputs a high-level decision vector containing parallelism, resource allocation and execution priority.

[0026] The lower-level policy network receives the higher-level decision vector and query relationship graph, learns the specific physical operator selection and connection order through a graph convolutional network, and outputs a detailed execution plan tree.

[0027] The strategies of the upper and lower layers are coordinated based on the estimated confidence level. When the confidence level is higher than the preset threshold, a deterministic strategy is adopted, and when the confidence level is lower than the threshold, an exploratory strategy is adopted.

[0028] The generated execution plan is submitted to the database execution engine, and the actual execution time and resource consumption are recorded.

[0029] Preferably, the step of constructing a multi-objective loss function by calculating the execution cost bias and the cardinality estimation error includes:

[0030] The relative deviation between the actual execution time and the estimated execution time is calculated as the execution cost loss;

[0031] The q-error between the actual result set size and the cardinality estimate is calculated as the cardinality estimation loss.

[0032] The contrast loss between the current query semantic feature vector and the historical similar query feature vector is calculated as the representation learning loss;

[0033] The three types of losses are weighted and summed based on preset weight coefficients to generate a comprehensive loss function;

[0034] The network parameters of each module are updated using the comprehensive loss function through gradient backpropagation.

[0035] Preferably, the adaptive cardinality estimation module further includes:

[0036] Maintain a historical query execution log, recording the SQL statement, estimated cardinality, actual cardinality, and execution time for each query;

[0037] Based on historical execution logs, a sliding window mechanism is used to identify the data distribution trends in the lung cancer research database.

[0038] When a significant change in data distribution is detected, an incremental update of the statistical metadata is triggered;

[0039] The learning rate and regularization parameters of the deep ensemble network are dynamically adjusted based on the cardinality estimation error of the most recent queries.

[0040] Preferably, the dynamic execution scheduling module further includes:

[0041] Establish a query template library and cluster historical queries according to structural similarity to form several query template categories;

[0042] For each query template category, maintain a cache of preferred execution plans, which caches the historical best execution plans for queries in that category;

[0043] For newly entered queries, template matching is performed first. If the match is successful and the cache is hit, the cached execution plan is reused directly.

[0044] If template matching fails or the cache is not hit, a new execution plan is generated using a hierarchical reinforcement learning framework.

[0045] Update the cache of the preferred execution plan for the corresponding template category based on the actual performance of the new execution plan.

[0046] Preferably, the closed-loop feedback adjustment module further includes:

[0047] Set the priority order for updating parameters of each module, and prioritize updating the parameters of the modules that have the greatest impact on overall performance;

[0048] An asynchronous update mechanism is adopted, which allows each module to adjust parameters at different time scales;

[0049] An experience replay buffer is introduced to store the execution experience of historical queries for offline batch training;

[0050] High-value samples are selected from the experience replay buffer for parameter updates through importance sampling;

[0051] Set stability constraints for parameter updates to prevent system performance fluctuations caused by excessively large single update magnitudes.

[0052] Preferably, the query semantic graph construction module, the adaptive cardinality estimation module, the dynamic execution scheduling module, and the closed-loop feedback adjustment module are deeply coupled:

[0053] The output of the query semantic graph construction module serves as the core input to the adaptive cardinality estimation module, and the accuracy of cardinality estimation directly depends on the quality of semantic feature representation.

[0054] The output of the adaptive cardinality estimation module serves as a key decision-making basis for the dynamic execution scheduling module, and the optimization quality of the execution plan is directly affected by the accuracy of cardinality estimation.

[0055] The execution result of the dynamic execution scheduling module is transmitted back to the first two modules through the closed-loop feedback adjustment module, forming a closed-loop optimization loop;

[0056] Each module achieves collaborative parameter updates by sharing gradient information. Improvements in the preceding module promote performance enhancements in the following module, and feedback from the following module guides the optimization direction of the preceding module, thus achieving a synergistic effect of mutual promotion and cumulative efficiency.

[0057] This invention also provides an adaptive query optimization scheduling system for a lung cancer research database, comprising:

[0058] The query semantic graph construction module is used to construct semantic graphs and extract features from input SQL query statements based on graph attention networks, generating query semantic feature vectors.

[0059] An adaptive cardinality estimation module, connected to the query semantic graph construction module, is used to estimate the cardinality of the query result set based on the query semantic feature vector and statistical metadata of the lung cancer research database, using an attention-enhanced deep ensemble network, and to generate a cardinality estimate and an estimated confidence level.

[0060] The dynamic execution scheduling module, connected to the adaptive cardinality estimation module, is used to generate an optimized execution plan and optimize the scheduling strategy based on the query semantic feature vector, cardinality estimate, and estimated confidence using a hierarchical reinforcement learning framework.

[0061] The closed-loop feedback adjustment module is connected to the query semantic graph construction module, the adaptive cardinality estimation module, and the dynamic execution scheduling module, respectively. It is used to update the parameters of each module in reverse using an online learning mechanism based on the actual execution results of the optimized execution plan, thus forming a closed-loop feedback adjustment.

[0062] The beneficial effects of this invention are as follows:

[0063] This invention sets up a query semantic graph construction module and uses graph attention networks to perform deep semantic understanding of SQL query statements. Compared with the existing LSTM-based serialization processing method, it can more accurately capture the structured features of query statements and the topological relationships of multi-table joins, significantly improving the accuracy of query representation learning.

[0064] This invention sets up an adaptive cardinality estimation module, uses an attention-enhanced deep ensemble network to accurately estimate the cardinality of the query result set, and integrates historical execution feedback for dynamic parameter adjustment. Compared with existing technologies that only focus on cost models that consume hardware resources, this invention can provide more accurate cardinality information for the query optimizer, thereby generating a better execution plan.

[0065] This invention sets up a dynamic execution scheduling module and adopts a hierarchical reinforcement learning framework to achieve collaborative optimization of macro-scheduling strategies and micro-operator selection. Compared with the existing single-level reinforcement learning methods, it can search for the optimal execution scheme in a larger policy space and improve the optimization effect of complex queries.

[0066] This invention establishes a deep coupling and collaborative mechanism among various optimization modules by setting up a closed-loop feedback adjustment module. This allows the output quality of the front-end module and the decision-making accuracy of the back-end module to mutually promote and enhance each other, forming a closed-loop optimization loop. This inter-module synergy achieves non-linear growth in technical performance, resulting in significantly better overall performance compared to the existing method of optimizing each stage independently.

[0067] This invention targets the specific application scenario of lung cancer research databases, and adaptively optimizes the data based on domain access patterns and query load characteristics. It can reduce the average execution time of complex relational queries by more than 85%, providing strong technical support for the efficient retrieval and analysis of large-scale lung cancer research data. Attached Figure Description

[0068] Figure 1 This is a schematic diagram of the adaptive query optimization scheduling system for the lung cancer research database of this invention;

[0069] Figure 2 This is a flowchart illustrating the adaptive query optimization scheduling method for the lung cancer research database of this invention.

[0070] Figure 3 This is a schematic diagram of the processing flow of the query semantic graph construction module of the present invention;

[0071] Figure 4 This is a schematic diagram of the processing flow of the adaptive cardinality estimation module of the present invention;

[0072] Figure 5 This is a schematic diagram of the hierarchical reinforcement learning framework of the dynamic execution scheduling module of this invention. Detailed Implementation

[0073] Please refer to the attached document. Figures 1-5 The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only a part of the embodiments of the present invention, and not all of them. All other embodiments obtained by those skilled in the art based on the embodiments of the present invention without creative effort are within the scope of protection of the present invention.

[0074] Reference Figure 1 This invention provides an adaptive query optimization scheduling system for a lung cancer research database, comprising a query semantic graph construction module 1, an adaptive cardinality estimation module 2, a dynamic execution scheduling module 3, and a closed-loop feedback adjustment module 4. These four modules form a deeply coupled closed-loop collaborative system, achieving a synergistic effect of mutual promotion and cumulative efficiency through parameter-level and state-level coupling between modules.

[0075] The query semantic graph construction module 1 is used to construct a semantic graph and extract features from the input SQL query statement based on a graph attention network, generating a query semantic feature vector. This module is the input end of the entire system, and its output quality directly affects the processing effect of all subsequent modules. The query semantic graph construction module 1 includes an SQL parser, a query relationship graph construction unit, and a graph attention network unit. The SQL parser is responsible for performing lexical and syntactic analysis on the input SQL query statement to generate an abstract syntax tree. The query relationship graph construction unit is responsible for traversing the abstract syntax tree, extracting data table nodes and connecting edges, and constructing the query relationship graph. The graph attention network unit is responsible for performing multi-layer feature learning on the query relationship graph to generate a global query semantic feature vector.

[0076] The adaptive cardinality estimation module 2 is connected to the query semantic graph construction module 1. Based on the query semantic feature vector and statistical metadata from the lung cancer research database, it uses an attention-enhanced deep ensemble network to estimate the cardinality of the query result set, generating a cardinality estimate and an estimated confidence level. The estimation accuracy of this module directly depends on the quality of the semantic features provided by the query semantic graph construction module 1, and its output serves as the core decision-making basis for the dynamic execution scheduling module 3. The adaptive cardinality estimation module 2 includes a statistical metadata acquisition unit, a table-level feature encoding unit, a self-attention aggregation unit, and a cardinality prediction unit. The statistical metadata acquisition unit obtains statistical information such as the number of rows, column cardinality, data distribution histograms, and correlation matrices for each table from the lung cancer research database. The table-level feature encoding unit constructs a local feature vector for each data table that integrates statistical information and filtering conditions. The self-attention aggregation unit calculates the importance weights of each table through a self-attention mechanism and performs weighted aggregation. The cardinality prediction unit inputs the aggregated features into a multilayer perceptron and outputs a cardinality estimate and a confidence level.

[0077] The dynamic execution scheduling module 3 is connected to the adaptive cardinality estimation module 2. It uses a hierarchical reinforcement learning framework to generate query execution plans and optimize scheduling strategies based on query semantic feature vectors, cardinality estimates, and estimated confidence levels, resulting in an optimized execution plan. The decision quality of this module is directly affected by the output accuracy of the first two modules, and its execution results, in turn, influence the parameter updates of the preceding modules through the closed-loop feedback adjustment module 4. The dynamic execution scheduling module 3 includes an upper-layer policy network unit, a lower-layer policy network unit, a policy coordination unit, and an execution plan generation unit. The upper-layer policy network unit learns macro-level scheduling decisions based on gated recurrent units. The lower-layer policy network unit learns specific operator selection and connection order based on graph convolutional networks. The policy coordination unit coordinates the execution modes of the upper and lower layer policies according to the estimated confidence levels. The execution plan generation unit converts the policy decisions into specific execution plan trees.

[0078] The closed-loop feedback adjustment module 4 is connected to the query semantic graph construction module 1, the adaptive cardinality estimation module 2, and the dynamic execution scheduling module 3, respectively. It is used to update the parameters of each module in reverse using an online learning mechanism based on the actual execution results of the optimized execution plan, forming a closed-loop feedback adjustment. This module is key to achieving deep coupling and collaboration among the modules. Through backpropagation of gradients from the multi-objective loss function, the parameters of each module can be optimized collaboratively, achieving a mutually beneficial synergistic effect. The closed-loop feedback adjustment module 4 includes an execution result acquisition unit, a loss function calculation unit, a gradient distribution unit, and a parameter update unit. The execution result acquisition unit records the actual execution time of the query, the size of the result set, and resource consumption. The loss function calculation unit calculates the comprehensive loss function based on the execution cost bias, cardinality estimation error, and representation learning loss. The gradient distribution unit backpropagates the loss gradient to each module. The parameter update unit updates the network weights of each module based on the gradient information.

[0079] The core innovation of this invention lies in establishing a deeply coupled closed-loop collaborative mechanism among four modules. The semantic feature vector of the query semantic graph construction module 1 serves as a key input to the adaptive cardinality estimation module 2, and the accuracy of cardinality estimation highly depends on the quality of semantic feature representation. The cardinality estimate and confidence level of the adaptive cardinality estimation module 2 serve as important decision-making criteria for the dynamic execution scheduling module 3, and the optimization quality of the execution plan is directly affected by the accuracy of the cardinality estimation. The actual execution results of the dynamic execution scheduling module 3 are transmitted back to the first three modules through the closed-loop feedback adjustment module 4, forming a complete closed-loop optimization loop. Each module achieves collaborative parameter updates by sharing gradient information. Improvements in the preceding modules can promote performance improvements in the following modules, and feedback from the following modules can guide the optimization direction of the preceding modules, thereby achieving a synergistic effect of mutual promotion and synergistic enhancement, resulting in an overall technical effect exhibiting a non-linear growth characteristic of 1+1>2.

[0080] Reference Figure 2 This invention provides an adaptive query optimization scheduling method for a lung cancer research database, comprising the following steps:

[0081] Step S1: Using the query semantic graph construction module 1, semantic graph construction and feature extraction are performed on the input SQL query statement based on the graph attention network to generate a query semantic feature vector.

[0082] Step S2: Using the adaptive cardinality estimation module 2, based on the query semantic feature vector and statistical metadata of the lung cancer research database, an attention-enhanced deep ensemble network is used to estimate the cardinality of the query result set, generating a cardinality estimate and an estimated confidence level.

[0083] Step S3: Through the dynamic execution scheduling module 3, based on the query semantic feature vector, cardinality estimate and estimated confidence, a hierarchical reinforcement learning framework is used to generate the query execution plan and optimize the scheduling strategy, thereby generating an optimized execution plan.

[0084] Step S4: Through the closed-loop feedback adjustment module 4, based on the actual execution results of the optimized execution plan, the parameters of the query semantic graph construction module 1, the adaptive cardinality estimation module 2, and the dynamic execution scheduling module 3 are updated in reverse using an online learning mechanism to form a closed-loop feedback adjustment.

[0085] Reference Figure 3 In one embodiment of the present invention, the specific implementation process of semantic graph construction and feature extraction of the input SQL query statement based on graph attention network in step S1 is as follows:

[0086] First, the SQL parser performs lexical and syntactic analysis on the input SQL query statement, generating an abstract syntax tree. The lexical analysis phase decomposes the SQL statement into lexical units such as keywords, identifiers, operators, and delimiters. The syntactic analysis phase organizes these lexical units into a tree structure according to SQL grammar rules. In a preferred embodiment of the invention, for typical query scenarios in lung cancer research databases, the abstract syntax tree generation process pays particular attention to table references in the FROM clause, join conditions and filtering predicates in the WHERE clause, and projected columns in the SELECT clause.

[0087] Then, the query graph construction unit traverses the abstract syntax tree to extract the set of data tables, join conditions, and filter predicates involved in the query. For lung cancer research databases, common data tables include patient basic information tables, gene testing result tables, pathology report tables, imaging diagnosis tables, and follow-up record tables. The query graph is constructed using data tables as nodes and join conditions as edges. Node features include table statistics (such as number of rows, average row length, and data distribution characteristics), index distribution (such as the type and selectivity of primary key indexes, foreign key indexes, and composite indexes), and historical access frequency (such as the number of times the table has been accessed and the access pattern within recent time windows). Edge features include the selectivity of the join key (indicating the filtering effect of the join conditions) and data type (such as integer joins, string joins, or date joins).

[0088] Next, the graph attention network unit performs multi-layer feature learning on the query relationship graph. In a preferred embodiment of the invention, a 3-layer graph attention network is used, with the hidden dimension of each layer set to 128. Each layer calculates the attention weights between nodes through a multi-head attention mechanism. Specifically, for nodes in the query relationship graph... , its first The layer's feature update uses a multi-head attention mechanism:

[0089] ,

[0090] in, For nodes In the The feature vector of the layer, For nodes In the The feature vector of the layer, For nodes The set of neighboring nodes, To consider the number of attention heads, in the preferred embodiment Set to 8, For the first Each attention node For nodes Attention weights For the first The learnable weight matrix for each attention head. This is the LeakyReLU activation function. Attention weights. The calculation uses the following formula:

[0091] ,

[0092] in, For the first The attention parameter vector of each attention head. For the edge eigenvectors, This represents a vector concatenation operation. This represents the transpose of a vector. The innovation of this formula lies in incorporating edge features into the attention calculation, enabling the model to perceive the semantic differences between different connection conditions.

[0093] Finally, graph-level pooling is performed on all node representations in the final layer to generate a global query semantic feature vector. The pooling operation employs attention-weighted summation aggregation:

[0094] ,

[0095] in, This is a global query semantic feature vector. To query the total number of nodes in the relationship graph. For nodes In the final layer eigenvectors, For nodes The graph-level attention weights are calculated using the following formula:

[0096] ,

[0097] in, This is the attention parameter vector for graph-level pooling. The projection matrix is ​​learnable. This is a hyperbolic tangent activation function. In this way, different importance weights can be adaptively assigned to different table nodes, highlighting the key data tables in the query.

[0098] Reference Figure 4 In one embodiment of the present invention, the specific implementation process of using an attention-enhanced deep ensemble network to estimate the cardinality of the query result set in step S2 is as follows:

[0099] First, based on the query semantic feature vector, the various data tables involved in the query and their filtering conditions are identified. For a lung cancer research database, a typical complex query may involve the join of four tables: a patient basic information table, a gene testing results table, a pathology report table, and a follow-up record table, and include filtering conditions based on patient age, gene mutation type, pathological stage, and follow-up time.

[0100] Then, the statistical metadata acquisition unit retrieves statistical information for each table from the lung cancer research database. For the patient basic information table, the statistical information includes the number of rows (e.g., 1 million rows), the data distribution histogram of the age column (the distribution of the number of patients divided by age group), the cardinality of the gender column (2), and the inter-column correlation matrix (e.g., the correlation between age and disease stage). For the gene testing results table, the statistical information includes the number of gene mutation types, the frequency distribution of each mutation type, and the time distribution of the testing date. Similarly, the corresponding statistical metadata is also retrieved for the pathology report table and the follow-up record table.

[0101] Next, the table-level feature encoding unit constructs a local feature vector for each data table. The local feature vector integrates three types of information: table statistics (such as the logarithm of the number of rows, average row length, and number of data pages), filter selectivity (the proportion remaining after predicate filtering based on histogram estimation), and index coverage (the proportion of columns involved in the query that are covered by the index). In a preferred embodiment, the dimension of the local feature vector for each table is set to 64.

[0102] Then, the self-attention aggregation unit calculates the importance weight of each table in the current query using a self-attention mechanism. For tables containing... A query of several data tables, assuming the local feature vectors of each table are... The self-attention weights are calculated using the following mechanism:

[0103] ,

[0104] in, For the first Importance weights of each data table To query semantic feature vectors, This is the similarity calculation function. The specific form of the similarity calculation function is:

[0105] ,

[0106] in, For learnable weight vectors, and These are the feature projection matrices, This is the bias vector. Local feature vectors are weighted and aggregated based on importance weights:

[0107] ,

[0108] in, This is the aggregated global feature vector.

[0109] Finally, the cardinality prediction unit inputs the aggregated global features into the multilayer perceptron, outputting the logarithmic value of the cardinality estimate and the estimated confidence score. The multilayer perceptron contains three hidden layers with 256, 128, and 64 neurons in each layer, respectively, using ReLU activation. The output layer employs a dual-head structure, with one head outputting the logarithmic value of the cardinality estimate. The other head outputs the estimated confidence level. The confidence level is calculated by mapping the output to the 0-1 interval using the Sigmoid function. Cardinality estimate. We obtain this by performing exponential operations on the numerical values:

[0110] ,

[0111] Estimate confidence level This reflects the model's confidence in the current estimation results. High confidence indicates that the current query pattern is supported by sufficient training samples in historical data, while low confidence indicates that the current query pattern is relatively rare or that the data distribution may have changed.

[0112] Furthermore, the adaptive cardinality estimation module 2 dynamically adjusts the estimation parameters by incorporating historical query execution feedback information. The module maintains a historical query execution log, recording the SQL statement, estimated cardinality, actual cardinality, and execution time for each query. Based on the cardinality estimation errors of the most recent queries (set to the most recent 100 in the preferred embodiment), the learning rate of the deep ensemble network is dynamically adjusted. When the average relative error exceeds a preset threshold (e.g., 50%), the learning rate is appropriately increased to accelerate model adaptation; when the error is small, the learning rate is decreased to maintain model stability. This adaptive mechanism enables the cardinality estimation module to track the dynamic changes in the data distribution in the lung cancer research database and continuously maintain high estimation accuracy.

[0113] Reference Figure 5 In one embodiment of the present invention, the specific implementation process of using a hierarchical reinforcement learning framework to generate the query execution plan and optimize the scheduling strategy in step S3 is as follows:

[0114] The dynamic execution scheduling module 3 employs a hierarchical reinforcement learning framework, decomposing the query optimization problem into two levels: the upper-level strategy is responsible for macro-level scheduling decisions, while the lower-level strategy is responsible for specific operator selection. This hierarchical design enables the search for optimal execution solutions within a larger policy space while maintaining computational tractability.

[0115] The upper-layer policy network unit receives the query semantic feature vector. and base estimate The system learns a macro-level scheduling strategy for query execution through Gated Recurrent Units (GRUs). The hidden state dimension of the GRU network is set to 256. The input to the upper-level policy network consists of a concatenation of the query semantic feature vector and the logarithmic value of the cardinality estimate. The GRU update process includes the computation of reset and update gates, which can selectively retain or forget historical information. The output of the upper-level policy network contains three types of high-level decisions: parallelism decisions (e.g., the number of threads to execute in parallel, such as 1, 2, 4, 8, etc.), resource allocation decisions (e.g., the proportion of memory allocated and the number of CPU cores), and execution priority decisions (e.g., the urgency of the query and the priority during resource contention). The dimension of these high-level decision vectors is set to 32.

[0116] The lower-level policy network unit receives the high-level decision vector and query relationship graph, and learns the specific physical operator selection and connection order through a graph convolutional network. The graph convolutional network has 4 layers, with each layer having a feature dimension of 64. For physical operator selection, the lower-level policy network needs to select an appropriate connection algorithm (such as nested loop connections, hash connections, or sort-merge connections) for each connection operation. For connection order optimization, the lower-level policy network needs to determine the optimal execution order of multi-table joins to minimize the size of the intermediate result set. The output of the lower-level policy network is a complete execution plan tree, containing the operator type, input-output relationship, and estimated execution cost for each operation node.

[0117] The strategy coordination unit estimates the confidence level. Coordinating strategies between upper and lower layers. In a preferred embodiment of the invention, the confidence threshold is set to 0.7. When When the probability of a certain value is high, it indicates that the cardinality estimation result has high reliability. In this case, a deterministic strategy is adopted, meaning that the upper and lower policy networks directly output the action with the highest probability. When the uncertainty is high, it indicates significant uncertainty in the cardinality estimation. In this case, an exploratory strategy is adopted, which increases the diversity of decisions by adding noise to the strategy output, so as to explore more possible execution schemes under uncertain conditions. The exploratory strategy uses an ε-greedy mechanism to explore probabilities. Inversely proportional to confidence level:

[0118] ,

[0119] Among them, when confidence level The lower the probability of exploration The larger the value, the greater the balance should be, but not exceeding 0.3, in order to maintain a balance between exploration and utilization.

[0120] The execution plan generation unit converts strategy decisions into specific execution plan trees and submits them to the database execution engine for execution. During execution, performance metrics such as actual execution time, CPU utilization, memory consumption, and disk I / O counts are recorded. These execution results serve as input to the closed-loop feedback adjustment module 4.

[0121] Furthermore, the dynamic execution scheduling module 3 establishes a query template library to accelerate the processing of duplicate queries. Historical queries are clustered based on structural similarity, forming several query template categories. A cache of optimal execution plans is maintained for each query template category, caching the historical best execution plans for queries within that category. For a new input query, template matching is first performed. If a match is successful and the cache is hit, the cached execution plan is directly reused, avoiding redundant optimization calculations. If the template match fails or the cache is not hit, a new execution plan is generated using a hierarchical reinforcement learning framework. Based on the actual performance of the new execution plan, the cache of optimal execution plans for the corresponding template category is updated. In a preferred embodiment, the capacity of the query template library is set to 1000, and the cache replacement strategy adopts a comprehensive scoring mechanism based on execution frequency and performance gains.

[0122] In one embodiment of the present invention, the specific implementation process of updating the parameters of each module in reverse through the closed-loop feedback adjustment module 4 in step S4 is as follows:

[0123] The closed-loop feedback adjustment module 4 is the core component that enables deep coupling and collaboration among the modules. Through gradient backpropagation of the multi-objective loss function, the parameters of each module can be optimized collaboratively, forming a mutually reinforcing closed-loop optimization mechanism.

[0124] First, the execution result collection unit records the actual execution results of the query. Key collection metrics include: actual execution time. (Unit: milliseconds), actual result set size (Number of rows returned), actual CPU utilization, and actual peak memory consumption. Simultaneously, the estimated execution time is obtained from the dynamic execution scheduling module 3. And obtain cardinality estimates from adaptive cardinality estimation module 2 .

[0125] Then, the loss function calculation unit calculates the comprehensive loss function based on the execution cost bias, cardinality estimation error, and representation learning loss. (Comprehensive Loss Function) It consists of three parts:

[0126] The first part is the cost of execution. Calculate the relative deviation between the actual execution time and the estimated execution time:

[0127] ,

[0128] The loss function penalizes the inaccuracy of execution time prediction, prompting the dynamic execution scheduling module 3 to improve the accuracy of execution cost estimation.

[0129] The second part is the base estimate loss. The q-error metric is used to measure the accuracy of the cardinality estimate.

[0130] ,

[0131] Here, q-error is a widely used cardinality estimation error metric in the database field, which can symmetrically penalize overestimation and underestimation. This loss function enables the adaptive cardinality estimation module 2 to improve the accuracy of cardinality prediction.

[0132] The third part represents the learning loss. A contrastive learning mechanism is employed to enhance the quality of query semantic feature representation. For the semantic feature vector of the current query... Retrieve a set of structurally similar positive query samples from the historical query database. Negative sample query set with dissimilar structure The comparative loss is calculated as follows:

[0133] ,

[0134] in, and These are the semantic feature vectors of the positive and negative samples, respectively. The cosine similarity function is used. The temperature parameter is set to 0.1 in the preferred embodiment. This loss function prompts the query semantic graph construction module 1 to learn more discriminative query representations, so that queries with similar structures have similar feature vectors, and queries with different structures have more distant feature vectors.

[0135] The overall loss function is obtained by weighted summation:

[0136] ,

[0137] in, , and This is a weighting coefficient. In a preferred embodiment, Set to 1.0, Set to 0.5. Set to 0.3. This weight configuration prioritizes performance optimization while balancing cardinality estimation accuracy and query representation quality.

[0138] Next, the gradient distribution unit backpropagates the loss gradient to each module. Since the three loss components are associated with different modules, gradient backpropagation can update the parameters of all modules simultaneously.

[0139] The gradient mainly affects the parameters of the dynamic execution scheduling module 3, and optimizes the scheduling strategy by reducing the execution time prediction error;

[0140] The gradient mainly affects the parameters of the adaptive cardinality estimation module 2, thereby improving the estimation accuracy by reducing the cardinality estimation error;

[0141] The gradient mainly affects the parameters of the query semantic graph construction module 1, and improves semantic representation by enhancing feature discriminability;

[0142] More importantly, due to the cascading dependencies between modules, gradients flow across modules during backpropagation: the input to the adaptive cardinality estimation module 2 includes the output of the query semantic graph construction module 1, therefore... The gradient will also propagate to the query semantic graph construction module 1; similarly, the input to the dynamic execution scheduling module 3 includes the outputs of the first two modules, therefore The gradient propagates to all preceding modules. This cross-module gradient flow is a key mechanism for achieving deep coupling and collaboration between modules, enabling preceding modules to perceive the impact of their output on the decision-making quality of subsequent modules, and thus make targeted parameter adjustments.

[0143] Finally, the parameter update unit updates the network weights of each module based on the gradient information. The Adam optimizer is used for parameter updates, with an initial learning rate set to 0.001. To ensure the stability of the update, a gradient pruning mechanism is introduced to limit the gradient norm to within 1.0. Parameter updates follow these constraints:

[0144] The priority order for updating parameters of each module is set, prioritizing the modules that have the greatest impact on overall performance. In a preferred embodiment, the priority order is: dynamic execution scheduling module 3 > adaptive cardinality estimation module 2 > query semantic graph construction module 1;

[0145] An asynchronous update mechanism is adopted, allowing each module to adjust parameters at different time scales. The dynamic execution scheduling module 3 updates parameters after each query, the adaptive cardinality estimation module 2 updates parameters in batches every 10 queries, and the query semantic graph construction module 1 updates parameters in batches every 100 queries.

[0146] An experience replay buffer is introduced to store the execution experience of historical queries for offline batch training. The buffer capacity is set to 10,000, and a first-in, first-out replacement strategy is adopted.

[0147] High-value samples are selected from the experience replay buffer for parameter updates through importance sampling. The importance weight of a sample is proportional to its temporal difference error; samples with high error receive a higher probability of being sampled.

[0148] Set stability constraints for parameter updates, limiting the magnitude of a single update to no more than 10% of the current parameter value, to prevent drastic parameter changes from causing system performance fluctuations.

[0149] Through the aforementioned closed-loop feedback adjustment mechanism, a close collaborative optimization relationship is formed among the modules. The semantic feature quality of the query semantic graph construction module 1 directly affects the estimation accuracy of the adaptive cardinality estimation module 2, while the accuracy of cardinality estimation determines the decision quality of the dynamic execution scheduling module 3. Simultaneously, the actual execution results of the dynamic execution scheduling module 3 guide the parameter optimization of the preceding modules through the closed-loop feedback mechanism. This deeply coupled collaborative mechanism enables the modules to mutually promote and synergize, resulting in an overall technical effect exhibiting a non-linear growth characteristic of 1+1>2.

[0150] In one specific embodiment of the present invention, performance testing was conducted on a lung cancer research database containing 5 million patient records, 20 million genetic testing records, 8 million pathology report records, and 15 million follow-up records. The test queries included join queries involving 2 to 5 tables, with query complexity ranging from simple two-table equi-joins to complex five-table mixed joins. The test results show that:

[0151] For two-table join queries, the method of this invention reduces the average query execution time from 12 seconds to 1.8 seconds compared to traditional rule-based optimizers, achieving a performance improvement of 85%.

[0152] For three-table join queries, the average query execution time was reduced from 28 seconds to 3.5 seconds, representing a performance improvement of 87.5%.

[0153] For four-table join queries, the average query execution time was reduced from 65 seconds to 7.2 seconds, representing a performance improvement of 88.9%.

[0154] For five-table join queries, the average query execution time was reduced from 120 seconds to 12.5 seconds, representing a performance improvement of 89.6%.

[0155] The average q-error of cardinality estimation is reduced from 15.3 in the traditional histogram method to 2.1 in the method of this invention, and the estimation accuracy is improved by 86.3%.

[0156] The query plan cache hit rate reached 78.5%, significantly reducing the computational overhead of optimizing duplicate queries.

[0157] The aforementioned performance improvements fully validate the effectiveness of the deeply coupled closed-loop collaborative system of this invention. The query semantic graph construction module 1 accurately captures the structured semantic features of the query through a graph attention network, providing a high-quality input representation for subsequent modules. The adaptive cardinality estimation module 2 significantly improves cardinality estimation accuracy through an attention-enhanced deep ensemble network, providing a reliable decision-making basis for execution plan optimization. The dynamic execution scheduling module 3 finds a better execution scheme in the large policy space through a hierarchical reinforcement learning framework. The closed-loop feedback adjustment module 4 achieves collaborative optimization of the parameters of each module through a multi-objective loss function, enabling continuous improvement and adaptive adjustment of the entire system. The deep coupling and collaborative mechanism among the four modules achieves mutually reinforcing and synergistic effects, reducing the response time of complex relational queries by more than 85%, providing strong technical support for efficient data analysis of the lung cancer research database.

[0158] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of the present invention should be included within the protection scope of the present invention.

Claims

1. An adaptive query optimization scheduling method for a lung cancer research database, characterized in that, Includes the following steps: The semantic graph construction module constructs semantic graphs and extracts features from the input SQL query statement based on graph attention network, generating query semantic feature vectors. Specifically, the query relationship graph is constructed by parsing the abstract syntax tree of the query statement, the semantic representation of table nodes and predicate edges is learned by the multi-head attention mechanism of graph attention network, and neighborhood information is aggregated based on message passing mechanism to capture the topological structure features of multi-table connections. The adaptive cardinality estimation module estimates the cardinality of the query result set based on the query semantic feature vector and the statistical metadata of the lung cancer research database, using an attention-enhanced deep ensemble network to generate cardinality estimates and estimated confidence levels. The self-attention mechanism is used to weight the importance of each data table involved in the query, and the estimation parameters are dynamically adjusted by incorporating historical query execution feedback information. Through the dynamic execution scheduling module, based on the query semantic feature vector, cardinality estimate and estimated confidence, a hierarchical reinforcement learning framework is used to generate query execution plans and optimize scheduling strategies, generating optimized execution plans. The upper-layer policy network learns macro-schedule decisions based on gated recurrent units, and the lower-layer policy network learns specific operator selection based on graph convolutional networks. The global optimization of the execution plan is achieved through the synergistic effect of the two-layer strategies. Through the closed-loop feedback adjustment module, based on the actual execution results of the optimized execution plan, the parameters of the query semantic graph construction module, the adaptive cardinality estimation module, and the dynamic execution scheduling module are updated in reverse using an online learning mechanism to form a closed-loop feedback adjustment. In this process, a multi-objective loss function is constructed by calculating the execution cost deviation and cardinality estimation error, and gradient backpropagation is used to achieve collaborative optimization of the parameters of each module.

2. The adaptive query optimization scheduling method for the lung cancer research database according to claim 1, characterized in that, The steps for constructing a semantic graph and extracting features from the input SQL query statement based on the graph attention network include: Perform lexical and syntactic analysis on SQL query statements to generate an abstract syntax tree; Traverse the abstract syntax tree to extract the set of data tables, the set of join conditions, and the set of filter predicates involved in the query; A query relationship graph is constructed using data tables as nodes and join conditions as edges. The node features include table statistics, index distribution, and historical access frequency, while the edge features include the selectivity of the join key and the data type. The query relationship graph is subjected to feature learning through a multi-layer graph attention network, wherein each layer of the network calculates the attention weights between nodes through a multi-head attention mechanism and updates the node representation based on attention-weighted neighborhood aggregation. Graph-level pooling is performed on all node representations in the final layer to generate a global query semantic feature vector.

3. The adaptive query optimization scheduling method for the lung cancer research database according to claim 1, characterized in that, The step of estimating the cardinality of the query result set using an attention-enhanced deep ensemble network includes: Based on the query semantic feature vector, identify the data tables involved in the query and their filtering conditions; Extract the number of rows, cardinality of columns, data distribution histogram, and correlation matrix of each table from the statistical metadata of the lung cancer research database; For each data table, a local feature vector is constructed, which integrates table statistics, filter selectivity, and index coverage. The importance weight of each table in the current query is calculated through a self-attention mechanism, and the local feature vectors are aggregated based on the importance weight. The aggregated global features are input into a multilayer perceptron, which outputs the logarithm of the cardinality estimate and the estimated confidence score.

4. The adaptive query optimization scheduling method for the lung cancer research database according to claim 1, characterized in that, The steps for generating query execution plans and optimizing scheduling strategies using a hierarchical reinforcement learning framework include: The upper-layer policy network receives the query semantic feature vector and cardinality estimate, learns the macro-scheduling strategy for query execution through the gated recurrent unit, and outputs a high-level decision vector containing parallelism, resource allocation and execution priority. The lower-level policy network receives the higher-level decision vector and query relationship graph, learns the specific physical operator selection and connection order through a graph convolutional network, and outputs a detailed execution plan tree. The strategies of the upper and lower layers are coordinated based on the estimated confidence level. When the confidence level is higher than the preset threshold, a deterministic strategy is adopted, and when the confidence level is lower than the threshold, an exploratory strategy is adopted. The generated execution plan is submitted to the database execution engine, and the actual execution time and resource consumption are recorded.

5. The adaptive query optimization scheduling method for the lung cancer research database according to claim 1, characterized in that, The step of constructing a multi-objective loss function by calculating the execution cost bias and the cardinality estimation error includes: The relative deviation between the actual execution time and the estimated execution time is calculated as the execution cost loss; The q-error between the actual result set size and the cardinality estimate is calculated as the cardinality estimation loss. The contrast loss between the current query semantic feature vector and the historical similar query feature vector is calculated as the representation learning loss; The three types of losses are weighted and summed based on preset weight coefficients to generate a comprehensive loss function; The network parameters of each module are updated using the comprehensive loss function through gradient backpropagation.

6. The adaptive query optimization scheduling method for the lung cancer research database according to claim 1, characterized in that, The adaptive cardinality estimation module further includes: Maintain a historical query execution log, recording the SQL statement, estimated cardinality, actual cardinality, and execution time for each query; Based on historical execution logs, a sliding window mechanism is used to identify the data distribution trends in the lung cancer research database. When a significant change in data distribution is detected, an incremental update of the statistical metadata is triggered; The learning rate and regularization parameters of the deep ensemble network are dynamically adjusted based on the cardinality estimation error of the most recent queries.

7. The adaptive query optimization scheduling method for lung cancer research database according to claim 1, characterized in that, The dynamic execution scheduling module also includes: Establish a query template library and cluster historical queries according to structural similarity to form several query template categories; For each query template category, maintain a cache of preferred execution plans, which caches the historical best execution plans for queries in that category; For newly entered queries, template matching is performed first. If the match is successful and the cache is hit, the cached execution plan is reused directly. If template matching fails or the cache is not hit, a new execution plan is generated using a hierarchical reinforcement learning framework. Update the cache of the preferred execution plan for the corresponding template category based on the actual performance of the new execution plan.

8. The adaptive query optimization scheduling method for the lung cancer research database according to claim 1, characterized in that, The closed-loop feedback adjustment module also includes: Set the priority order for updating parameters of each module, and prioritize updating the parameters of the modules that have the greatest impact on overall performance; An asynchronous update mechanism is adopted, which allows each module to adjust parameters at different time scales; An experience replay buffer is introduced to store the execution experience of historical queries for offline batch training; High-value samples are selected from the experience replay buffer for parameter updates through importance sampling; Set stability constraints for parameter updates to prevent system performance fluctuations caused by excessively large single update magnitudes.

9. The adaptive query optimization scheduling method for the lung cancer research database according to claim 1, characterized in that, The query semantic graph construction module, adaptive cardinality estimation module, dynamic execution scheduling module, and closed-loop feedback adjustment module are deeply coupled: The output of the query semantic graph construction module serves as the core input to the adaptive cardinality estimation module, and the accuracy of cardinality estimation directly depends on the quality of semantic feature representation. The output of the adaptive cardinality estimation module serves as a key decision-making basis for the dynamic execution scheduling module, and the optimization quality of the execution plan is directly affected by the accuracy of cardinality estimation. The execution result of the dynamic execution scheduling module is transmitted back to the first two modules through the closed-loop feedback adjustment module, forming a closed-loop optimization loop; Each module achieves collaborative parameter updates by sharing gradient information. Improvements in the front-end module promote performance improvements in the back-end module, and feedback from the back-end module guides the optimization direction of the front-end module.

10. An adaptive query optimization scheduling system for a lung cancer research database, characterized in that, include: The query semantic graph construction module is used to construct semantic graphs and extract features from input SQL query statements based on graph attention networks, generating query semantic feature vectors. Specifically, it constructs query relationship graphs by parsing the abstract syntax tree of the query statement, learns the semantic representations of table nodes and predicate edges using the multi-head attention mechanism of graph attention networks, and aggregates neighborhood information based on message passing mechanisms to capture the topological structure features of multi-table connections. An adaptive cardinality estimation module, connected to the query semantic graph construction module, is used to estimate the cardinality of the query result set based on the query semantic feature vector and statistical metadata of the lung cancer research database, using an attention-enhanced deep ensemble network to generate a cardinality estimate and an estimated confidence level. The module uses a self-attention mechanism to weight the importance of each data table involved in the query and dynamically adjusts the estimation parameters by incorporating historical query execution feedback information. The dynamic execution scheduling module, connected to the adaptive cardinality estimation module, is used to generate a query execution plan and optimize the scheduling strategy based on the query semantic feature vector, cardinality estimate, and estimated confidence, using a hierarchical reinforcement learning framework. The optimized execution plan is generated by the upper-layer policy network learning macro-scheduling decisions based on gated recurrent units, and the lower-layer policy network learning specific operator selection based on graph convolutional networks. The global optimization of the execution plan is achieved through the synergistic effect of the two-layer strategy. The closed-loop feedback adjustment module is connected to the query semantic graph construction module, the adaptive cardinality estimation module, and the dynamic execution scheduling module, respectively. It is used to update the parameters of each module in reverse using an online learning mechanism based on the actual execution results of the optimized execution plan, forming a closed-loop feedback adjustment. In this module, a multi-objective loss function is constructed by calculating the execution cost deviation and cardinality estimation error, and gradient backpropagation is used to achieve collaborative optimization of the parameters of each module.

Images