Method for combined use of SQL (Structured Query Language) component nodes in multi-engine development

Abstract

The invention discloses a method for combined use of SQL (Structured Query Language) component nodes in multi-engine development. The method comprises the following steps: S1, establishing database connection; s2, defining a standardized SQL component node interface to obtain data; s3, allocating an execution task of each SQL component node, and executing an SQL query on each SQL component node; s4, transmitting a query result to an intermediate processing layer; s5, utilizing an enhanced ETL (Extract-Transform-Load) process to process query results in an intermediate processing layer, and merging to generate a unified query result set; s6, adopting a self-adaptive result merging algorithm based on a random forest algorithm to adjust a merging strategy; s7, collecting performance indexes of each node; s8, dynamically predicting and optimizing an SQL component node combination strategy by using a deep reinforcement learning model based on a graph neural network and a multi-head attention mechanism; and S9, applying the optimized SQL component node combination strategy to a query task. According to the method, a distributed computing framework and deep reinforcement learning are adopted, and multi-engine SQL optimization use is achieved.

Description

Technical Field

[0001] The present invention relates to the field of distributed computing and deep learning technology, and in particular to a method for combining and using SQL component nodes in multi-engine development. Background Art

[0002] With the advent of the big data era, the diversity and volume of data are increasing, and the types and scale of databases that enterprises and research institutions need to process and analyze are also expanding. Traditional single database management systems (DBMS) can no longer meet the needs of complex data processing and query. Therefore, the collaborative work of multiple database engines has gradually become an important means to solve this problem. In a multi-engine environment, relational databases (such as MySQL, PostgreSQL, SQLite, etc.) and non-relational databases (such as MongoDB, Cassandra, etc.) each play their own strengths to jointly meet the challenges of data processing.

[0003] In the prior art, the implementation of multi-engine databases usually relies on developers to manually write complex scripts to exchange and process data between different database engines. This method is not only time-consuming and labor-intensive, but also prone to errors and difficult to maintain and expand. Especially when dealing with large query tasks, the manual script method is difficult to fully utilize the performance of each database engine, resulting in low query efficiency and low system resource utilization.

[0004] In addition, existing multi-engine database systems usually lack intelligent task scheduling and node combination strategies. In a multi-engine environment, how to intelligently select and combine different SQL component nodes according to the specific requirements of the query task and the current load of the system is the key to improving the overall performance of the system. In the existing technology, task scheduling and node combination strategies are often static and lack the ability to dynamically adjust, making it difficult to cope with the ever-changing query requirements and system status.

[0005] In addition, the data transmission and processing efficiency in the existing technology also have many shortcomings. Although some systems use compression algorithms and data transmission protocols, the efficiency of data transmission is still unsatisfactory when facing complex query tasks and large-scale data sets. The processes of data format conversion, data cleaning and data synchronization are complicated and time-consuming, which affects the system's response speed and query performance.

[0006] In terms of data integration, existing technologies mostly focus on data processing between database engines of the same type, and lack the ability to integrate and process data across engines. Different types of database engines have different data formats. How to achieve efficient data integration and processing while ensuring data consistency and integrity is a technical challenge. Existing solutions often have problems such as large delays and poor data consistency during the data integration process, making it difficult to meet the needs of real-time data processing.

[0007] In terms of real-time monitoring and dynamic adjustment, the multi-engine SQL query optimization in existing technologies is usually static and lacks the ability to perceive and adjust the system status in real time. When the system load changes and query requirements change, it is unable to adjust the node combination strategy in time, resulting in unstable system performance and poor response speed.

[0008] The rapid development of deep learning and artificial intelligence technologies has provided new solutions for multi-engine SQL query optimization. By analyzing historical execution data and current system status and using deep learning models to predict the best node combination strategy, query execution efficiency can be greatly improved. However, in the existing technology, the application of deep learning and artificial intelligence in multi-engine SQL query optimization is still in the initial exploration stage, and a systematic and practical solution has not yet been formed.

[0009] Therefore, how to provide a method for combining and using SQL component nodes in multi-engine development is a problem that those skilled in the art urgently need to solve. Summary of the invention

[0010] One object of the present invention is to propose a method for combining and using SQL component nodes in multi-engine development. The present invention adopts a deep reinforcement learning model of a graph neural network and a multi-head attention mechanism, combined with Apache Avro and Zstandard compression algorithms, to achieve intelligent combination and optimization of multi-engine SQL component nodes. Through real-time monitoring and dynamic adjustment mechanisms, data transmission and processing efficiency are effectively improved, and the flexibility, stability and execution efficiency of the system are enhanced. The present invention also ensures the accuracy and consistency of query results through an adaptive result merging algorithm, has high efficiency, reliability and scalability, and can meet complex and diverse database query and processing requirements.

[0011] A method for using a combination of SQL component nodes in multi-engine development according to an embodiment of the present invention includes the following steps:

[0012] S1. Establishing database connections in a variety of database management systems, wherein the database management systems include relational databases and non-relational databases;

[0013] S2, by defining standardized SQL component node interfaces, data is obtained from different database management systems, and the heterogeneous data adaptation layer is used to convert and standardize data formats;

[0014] S3. Use the distributed computing framework to schedule SQL query tasks, assign execution tasks to each SQL component node, and execute SQL queries on each SQL component node;

[0015] S4, using a data transmission protocol with a compression algorithm to transmit the query results to the intermediate processing layer;

[0016] S5. Using the enhanced ETL process, the query results from different SQL component nodes are converted into data formats, cleaned, and synchronized in the intermediate processing layer, and then merged to generate a unified query result set.

[0017] S6, using an adaptive result merging algorithm based on the random forest algorithm, by analyzing the query result characteristics and user needs, and adaptively adjusting the merging strategy;

[0018] S7, monitor the execution time, resource consumption and load of SQL component nodes in real time, and collect performance indicators of each node;

[0019] S8. Use a deep reinforcement learning model based on graph neural network and multi-head attention mechanism, combined with historical execution data and current performance indicators of each node, to dynamically predict and optimize SQL component node combination strategy;

[0020] S9. Apply the optimized SQL component node combination strategy to the query task.

[0021] Furthermore, the S3 specifically includes:

[0022] S31, defining task metadata of the SQL query task, including task ID, task priority, required resources, and expected execution time;

[0023] S32, establish a task queue, sort all SQL query tasks to be executed according to the priority in the task metadata, and enter the task queue;

[0024] S33, defining node metadata of the SQL component node, including node ID, node type, available resources, current load, and network latency;

[0025] S34, build a node resource pool, and manage all available SQL component nodes according to node metadata;

[0026] S35, using the task scheduler in the distributed computing framework to extract the highest priority SQL query task from the task queue;

[0027] S36. The task scheduler uses a task-node matching algorithm based on the node metadata and task metadata to assign the SQL query task to the appropriate SQL component node. The task-node matching algorithm expression is:

[0028]

[0029] Among them, T i represents the execution time of the ith task, T ik represents the time when the i-th task is executed for the kth time, N represents the number of executions, α, β and γ represent the weight parameters in the task scheduler, η and θ represent the weights of the expected value and variance of the execution time in the comprehensive matching score, respectively, E(T i ) represents the expected value of execution time, Var(T i ) represents the variance of execution time, L i Indicates the load of the i-th SQL component node, D i represents the network delay from the task scheduler to the i-th SQL component node, R i It indicates the matching degree between the resources of the i-th SQL component section and the resources required by the task. By analyzing the expected value and variance of the task execution time, the task execution time can be predicted more accurately and the task scheduling strategy can be optimized.

[0030] S37, assigning the node with the highest matching score to the current SQL query task, and updating the node metadata in the node resource pool;

[0031] S38. Under the control of the distributed computing framework, start the SQL query task and monitor the execution status of the task and the resource usage of the node;

[0032] S39. When a node is overloaded or fails, the task scheduler triggers the task migration mechanism to migrate SQL query tasks to other available SQL component nodes:

[0033] Migration score = δ·(L i -L j )+ε·(R i -R j )+ζ·S j ;

[0034] Among them, δ, ε and ζ represent the weight parameters in the task migration mechanism, L j Indicates the load of the jth SQL component node, R j represents the matching degree between the resources of the j-th SQL component section and the resources required by the task, S j represents the network stability of the jth SQL component node;

[0035] S310: After the task is executed, the task scheduler returns the task result and updates the information in the task queue and the node resource pool.

[0036] Furthermore, the data transmission protocol of the compression algorithm specifically includes a data transmission protocol based on Apache Avro and Zstandard compression algorithms, wherein Apache Avro is used for data serialization and deserialization, and the Zstandard compression algorithm is used for data compression and decompression. By first serializing the query results into a compact binary format and then using the Zstandard algorithm for efficient compression, the advantages of both are combined to achieve high efficiency and reliability of data transmission, significantly reduce transmission delays and bandwidth occupancy, and improve overall system performance.

[0037] Furthermore, the S5 specifically includes:

[0038] S51, receiving query result data from different SQL component nodes at the intermediate processing layer;

[0039] S52, performing preliminary format conversion on the received query result data, converting the data of the heterogeneous data sources into a unified intermediate format, wherein the intermediate format is a standardized SQL data format;

[0040] S53, using the enhanced ETL process to clean the data in the intermediate format, wherein the data cleaning includes removing duplicate data, correcting erroneous data, filling missing data and standardizing data format;

[0041] S54. During the data cleaning process, data normalization and standardization techniques are applied to ensure that data from different sources have consistent measurement units and formats, and use standardized formulas to convert data;

[0042] S55, synchronizing the cleaned and standardized data, wherein the data synchronization includes aligning data with different timestamps and merging the data using a time window technology to ensure data consistency and integrity;

[0043] S56. During the data merging process, a hash matching algorithm and a key field-based matching algorithm are applied to match and merge data from different data sources;

[0044] S57, storing the merged data into a unified result data set, wherein the result data set is stored in a structured table form to ensure that the query results can be efficiently processed and analyzed later;

[0045] S58, performing index optimization on the result data set, and establishing an index structure to improve the speed of data retrieval and query, wherein the index structure includes a B-tree index, a hash index, and a full-text index;

[0046] S59: Perform final verification and validation on the result data set to ensure the accuracy and completeness of the data. The verification and validation include data consistency check, data completeness check and data accuracy check.

[0047] Furthermore, the S6 specifically includes:

[0048] S61, construct training data set D = {(X i ,Y i )}, where X i represents the feature vector of the i-th query result, Y i Indicates the corresponding user demand label;

[0049] S62, preprocessing the training data set, including data cleaning, missing value filling and feature standardization, to obtain a preprocessed training data set D′={(X′ i ,Y i )}, where X′ i represents the feature vector of the i-th query result after preprocessing;

[0050] S63, randomly extracting a sample subset D″ from the training data set D′, and constructing a sample subset whose size is |D″|=α·|D′|, where α represents the sampling ratio;

[0051] S64. Randomly extract a feature subset on D″, the size of the feature subset is |F″|=β·|F|, where β represents the feature sampling ratio, and construct a decision tree T;

[0052] S65, repeat steps S63-S64 to generate N decision trees, and obtain a random forest classifier RF = {T1, T2, ..., T N};

[0053] S66. Extract features from the result set of the current query task to obtain a query result feature vector X q , where the elements of the eigenvector are defined as:

[0054]

[0055] Among them, r kj represents the jth eigenvalue in the kth result, w kj represents the weight of the kth result, m represents the size of the result set, and φ represents the feature transformation function, which is used to enhance feature representation;

[0056] S67, query result feature vector X q Input into the trained random forest classifier RF for classification prediction to obtain the preliminary merge strategy label Y q :

[0057]

[0058] Among them, argmax represents the index function of taking the maximum value, T i (X q ,y) represents the decision tree of the i-th decision tree for X q The predicted probability, ω i represents the weight of the i-th decision tree, and N represents the number of decision trees;

[0059] S68. Calculate preliminary merging strategy Y q Performance indicators include query result accuracy A and response time TIME:

[0060]

[0061] Where n represents the number of test samples, Y i represents the true label set of the i-th sample, y ij represents the true label, represents the predicted label, δ represents the indicator function;

[0062] S69. Adjust the merging strategy according to performance indicators and user needs, optimize the merging method of query results, and adjust the formula to:

[0063]

[0064] Among them, S represents the candidate strategy set, λ A and λ T represents the weight parameter;

[0065] S610: Execute the optimized merging strategy to generate a final unified query result set.

[0066] Furthermore, the S8 specifically includes:

[0067] S81. Build a node performance database based on the historical execution data of the SQL component nodes, where the performance database includes the execution time, resource consumption, and load of each node;

[0068] S82. Design a graph neural network model, where the nodes in the graph structure represent SQL component nodes, and the edges represent data dependencies between nodes;

[0069] S83. Define the input of the graph neural network model as the historical performance indicators of the SQL component nodes and the current performance indicators of each node;

[0070] S84. Apply the multi-head attention mechanism in the graph neural network model to the graph structure and calculate the attention weight e between each node and its neighboring nodes. ij :

[0071] e ij =LeakyReLU(a T [Wh i ||Wh j ]);

[0072] Among them, a represents the attention vector, W represents the weight matrix, and h i and h j Represent the feature vectors of node i and node j respectively, || represents the vector connection operation;

[0073] S85, based on attention weight e ij Calculate the weighted sum of neighboring nodes to obtain the new feature expression h′ of node i i :

[0074]

[0075] Among them, σ represents the activation function, N(i) represents the set of neighbor nodes of node i, and α ij represents the normalized attention weight between node i and node j:

[0076]

[0077] S86. In order to improve the accuracy of the model, a node adaptive weight adjustment mechanism is introduced:

[0078]

[0079] Among them, h′ j represents the new feature expression of node j, λ represents the learning rate, β ij Represents the adaptive weight between node i and node j:

[0080]

[0081] Where u represents the adaptive weight vector;

[0082] S87. Train the graph neural network model, use the loss function to measure the difference between the predicted results and the actual performance indicators, and optimize the parameters of the graph neural network model. The loss function is defined as:

[0083]

[0084] Where N represents the total number of samples, y i Indicates the actual performance index, represents the prediction performance index, γ represents the regularization parameter, and E represents the edge set in the graph;

[0085] S88. After the training is completed, the graph neural network model is applied to the real-time system, the historical performance indicators of the SQL component nodes and the current performance indicators of each node are input, and the optimized SQL component node combination strategy is output.

[0086] The beneficial effects of the present invention are:

[0087] First, by defining standardized SQL component node interfaces and heterogeneous data adaptation layers, the present invention enables different types of database management systems to work seamlessly together. Developers can integrate and process multi-engine databases without writing complex scripts, which greatly simplifies development and maintenance work and improves the flexibility and scalability of the system.

[0088] Secondly, the present invention adopts a data transmission protocol based on ApacheAvro and Zstandard compression algorithm, which greatly improves the efficiency of data transmission and reduces transmission delay and bandwidth occupancy. In the data processing process, the enhanced ETL process is used for data format conversion, data cleaning and data synchronization, which effectively ensures the consistency and integrity of the data and improves the speed of data processing. These measures enable the system to efficiently process and transmit large-scale data, meeting the needs of efficient query and real-time processing.

[0089] The present invention realizes data integration and processing across multiple database engines by constructing a standardized SQL component node interface. The adaptive result merging algorithm is used to dynamically adjust the data merging strategy according to the query result characteristics and user needs to ensure the accuracy and consistency of the data and meet the complex and diverse data processing requirements. The method not only improves the efficiency of data processing, but also enhances the reliability and flexibility of the system.

[0090] The present invention also introduces a real-time monitoring mechanism that can monitor the execution time, resource consumption and load of SQL component nodes in real time, and make dynamic adjustments based on the monitoring data. Through load balancing and parallel processing to optimize the combined strategy of SQL component nodes, it is ensured that the system maintains optimal performance under different loads and query requirements, and improves the stability and response speed of the system. This real-time monitoring and dynamic adjustment mechanism enables the system to quickly respond to changes in load and query requirements and maintain efficient operation.

[0091] In addition, an adaptive result merging algorithm based on the random forest algorithm is used to analyze the query result characteristics and user needs, and the merging strategy is adaptively adjusted to improve the accuracy and response time of the query results. This method can effectively handle large-scale data query tasks and significantly improve the performance of the system. In addition, the task migration mechanism introduced in the task scheduling process, when a node is overloaded or fails, the task scheduler can promptly migrate the SQL query task to other available SQL component nodes to ensure the reliability and continuity of the system.

[0092] Finally, the present invention applies deep learning and artificial intelligence technologies to multi-engine SQL query optimization. By using a deep reinforcement learning model with graph neural networks and multi-head attention mechanism, the optimal node combination strategy is analyzed and predicted, which improves the efficiency and accuracy of query execution. This method can continuously learn and adapt to system changes, provide more intelligent optimization solutions, and further improve system performance and user experience. BRIEF DESCRIPTION OF THE DRAWINGS

[0093] The accompanying drawings are used to provide a further understanding of the present invention and constitute a part of the specification. Together with the embodiments of the present invention, they are used to explain the present invention and do not constitute a limitation of the present invention. In the accompanying drawings:

[0094] Figure 1 A flowchart of a method for combining and using SQL component nodes in multi-engine development proposed by the present invention;

[0095] Figure 2 This is a structural schematic diagram of a method for combining and using SQL component nodes in multi-engine development proposed by the present invention. DETAILED DESCRIPTION

[0096] The present invention will now be described in further detail with reference to the accompanying drawings. These drawings are simplified schematic diagrams, which only illustrate the basic structure of the present invention in a schematic manner, and therefore only show the components related to the present invention.

[0097] refer to Figure 1 and Figure 2 , a method for combining and using SQL component nodes in multi-engine development, comprising the following steps:

[0098] S1. Establishing database connections in a variety of database management systems, wherein the database management systems include relational databases and non-relational databases;

[0099] S2, by defining standardized SQL component node interfaces, data is obtained from different database management systems, and the heterogeneous data adaptation layer is used to convert and standardize data formats;

[0100] S3. Use the distributed computing framework to schedule SQL query tasks, assign execution tasks to each SQL component node, and execute SQL queries on each SQL component node;

[0101] S4, using a data transmission protocol with a compression algorithm to transmit the query results to the intermediate processing layer;

[0102] S5. Using the enhanced ETL process, the query results from different SQL component nodes are converted into data formats, cleaned, and synchronized in the intermediate processing layer, and then merged to generate a unified query result set.

[0103] S6, using an adaptive result merging algorithm based on the random forest algorithm, by analyzing the query result characteristics and user needs, and adaptively adjusting the merging strategy;

[0104] S7, monitor the execution time, resource consumption and load of SQL component nodes in real time, and collect performance indicators of each node;

[0105] S8. Use a deep reinforcement learning model based on graph neural network and multi-head attention mechanism, combined with historical execution data and current performance indicators of each node, to dynamically predict and optimize SQL component node combination strategy;

[0106] S9. Apply the optimized SQL component node combination strategy to the query task.

[0107] In this implementation, S3 specifically includes:

[0108] S31, defining task metadata of the SQL query task, including task ID, task priority, required resources, and expected execution time;

[0109] S32, establish a task queue, sort all SQL query tasks to be executed according to the priority in the task metadata, and enter the task queue;

[0110] S33, defining node metadata of the SQL component node, including node ID, node type, available resources, current load, and network latency;

[0111] S34, build a node resource pool, and manage all available SQL component nodes according to node metadata;

[0112] S35, using the task scheduler in the distributed computing framework to extract the highest priority SQL query task from the task queue;

[0113] S36. The task scheduler uses a task-node matching algorithm based on the node metadata and task metadata to assign the SQL query task to the appropriate SQL component node. The task-node matching algorithm expression is:

[0114]

[0115] Among them, T i represents the execution time of the ith task, T ik represents the time when the i-th task is executed for the kth time, N represents the number of executions, α, β and γ represent the weight parameters in the task scheduler, η and θ represent the weights of the expected value and variance of the execution time in the comprehensive matching score, respectively, E(T i ) represents the expected value of execution time, Var(T i ) represents the variance of execution time, L i Indicates the load of the i-th SQL component node, D i represents the network delay from the task scheduler to the i-th SQL component node, R i It indicates the matching degree between the resources of the i-th SQL component section and the resources required by the task. By analyzing the expected value and variance of the task execution time, the task execution time can be predicted more accurately and the task scheduling strategy can be optimized.

[0116] S37, assigning the node with the highest matching score to the current SQL query task, and updating the node metadata in the node resource pool;

[0117] S38. Under the control of the distributed computing framework, start the SQL query task and monitor the execution status of the task and the resource usage of the node;

[0118] S39. When a node is overloaded or fails, the task scheduler triggers the task migration mechanism to migrate SQL query tasks to other available SQL component nodes:

[0119] Migration score = δ·(L i -L j )+ε·(R i -R j )+ζ·S j ;

[0120] Among them, δ, ε and ζ represent the weight parameters in the task migration mechanism, L j Indicates the load of the jth SQL component node, R j represents the matching degree between the resources of the j-th SQL component section and the resources required by the task, S j represents the network stability of the jth SQL component node;

[0121] S310: After the task is executed, the task scheduler returns the task result and updates the information in the task queue and the node resource pool.

[0122] In this embodiment, the data transmission protocol of the compression algorithm specifically includes a data transmission protocol based on Apache Avro and Zstandard compression algorithms, wherein Apache Avro is used for data serialization and deserialization, and the Zstandard compression algorithm is used for data compression and decompression. By first serializing the query results into a compact binary format and then using the Zstandard algorithm for efficient compression, the advantages of both are combined to achieve high efficiency and reliability of data transmission, significantly reduce transmission delays and bandwidth occupancy, and improve overall system performance.

[0123] In this implementation manner, S5 specifically includes:

[0124] S51, receiving query result data from different SQL component nodes at the intermediate processing layer;

[0125] S52, performing preliminary format conversion on the received query result data, converting the data of the heterogeneous data sources into a unified intermediate format, wherein the intermediate format is a standardized SQL data format;

[0126] S53, using the enhanced ETL process to clean the data in the intermediate format, wherein the data cleaning includes removing duplicate data, correcting erroneous data, filling missing data and standardizing data format;

[0127] S54. During the data cleaning process, data normalization and standardization techniques are applied to ensure that data from different sources have consistent measurement units and formats, and use standardized formulas to convert data;

[0128] S55, synchronizing the cleaned and standardized data, wherein the data synchronization includes aligning data with different timestamps and merging the data using a time window technology to ensure data consistency and integrity;

[0129] S56. During the data merging process, a hash matching algorithm and a key field-based matching algorithm are applied to match and merge data from different data sources;

[0130] S57, storing the merged data into a unified result data set, wherein the result data set is stored in a structured table form to ensure that the query results can be efficiently processed and analyzed later;

[0131] S58, performing index optimization on the result data set, and establishing an index structure to improve the speed of data retrieval and query, wherein the index structure includes a B-tree index, a hash index, and a full-text index;

[0132] S59: Perform final verification and validation on the result data set to ensure the accuracy and completeness of the data. The verification and validation include data consistency check, data completeness check and data accuracy check.

[0133] In this implementation manner, S6 specifically includes:

[0134] S61, construct training data set D = {(X i ,Y i )}, where X i represents the feature vector of the i-th query result, Y i Indicates the corresponding user demand label;

[0135] S62, preprocessing the training data set, including data cleaning, missing value filling and feature standardization, to obtain a preprocessed training data set D′={(X′ i ,Y i )}, where X′ i represents the feature vector of the i-th query result after preprocessing;

[0136] S63, randomly extracting a sample subset D″ from the training data set D′, and constructing a sample subset whose size is |D″|=α·|D′|, where α represents the sampling ratio;

[0137] S64. Randomly extract a feature subset on D″, the size of the feature subset is |F″|=β·|F|, where β represents the feature sampling ratio, and construct a decision tree T;

[0138] S65, repeat steps S63-S64 to generate N decision trees, and obtain a random forest classifier RF = {T1, T2, ..., T N};

[0139] S66. Extract features from the result set of the current query task to obtain a query result feature vector X q , where the elements of the eigenvector are defined as:

[0140]

[0141] Among them, r kj represents the jth eigenvalue in the kth result, w kj represents the weight of the kth result, m represents the size of the result set, and φ represents the feature transformation function, which is used to enhance feature representation;

[0142] S67, query result feature vector X q Input into the trained random forest classifier RF for classification prediction to obtain the preliminary merge strategy label Y q :

[0143]

[0144] Among them, argmax represents the index function of taking the maximum value, T i (X q ,y) represents the decision tree of the i-th decision tree for X q The predicted probability, ω i represents the weight of the i-th decision tree, and N represents the number of decision trees;

[0145] S68. Calculate preliminary merging strategy Y q Performance indicators include query result accuracy A and response time TIME:

[0146]

[0147] Where n represents the number of test samples, Y i represents the true label set of the i-th sample, y ij represents the true label, represents the predicted label, δ represents the indicator function;

[0148] S69. Adjust the merging strategy according to performance indicators and user needs, optimize the merging method of query results, and adjust the formula to:

[0149]

[0150] Among them, S represents the candidate strategy set, λ A and λ T represents the weight parameter;

[0151] S610: Execute the optimized merging strategy to generate a final unified query result set.

[0152] In this implementation manner, S8 specifically includes:

[0153] S81. Build a node performance database based on the historical execution data of the SQL component nodes, where the performance database includes the execution time, resource consumption, and load of each node;

[0154] S82. Design a graph neural network model, where the nodes in the graph structure represent SQL component nodes, and the edges represent data dependencies between nodes;

[0155] S83. Define the input of the graph neural network model as the historical performance indicators of the SQL component nodes and the current performance indicators of each node;

[0156] S84. Apply the multi-head attention mechanism in the graph neural network model to the graph structure and calculate the attention weight e between each node and its neighboring nodes. ij :

[0157] e ij =LeakyReLU(a T [Wh i ||Wh j ]);

[0158] Among them, a represents the attention vector, W represents the weight matrix, and h i and h j Represent the feature vectors of node i and node j respectively, || represents the vector connection operation;

[0159] S85, based on attention weight e ij Calculate the weighted sum of neighboring nodes to obtain the new feature expression h′ of node i i :

[0160]

[0161] Among them, σ represents the activation function, N(i) represents the set of neighbor nodes of node i, and α ij represents the normalized attention weight between node i and node j:

[0162]

[0163] S86. In order to improve the accuracy of the model, a node adaptive weight adjustment mechanism is introduced:

[0164]

[0165] Among them, h′ j represents the new feature expression of node j, λ represents the learning rate, β ij Represents the adaptive weight between node i and node j:

[0166]

[0167] Where u represents the adaptive weight vector;

[0168] S87. Train the graph neural network model, use the loss function to measure the difference between the predicted results and the actual performance indicators, and optimize the parameters of the graph neural network model. The loss function is defined as:

[0169]

[0170] Where N represents the total number of samples, y i Indicates the actual performance index, represents the prediction performance index, γ represents the regularization parameter, and E represents the edge set in the graph;

[0171] S88. After the training is completed, the graph neural network model is applied to the real-time system, the historical performance indicators of the SQL component nodes and the current performance indicators of each node are input, and the optimized SQL component node combination strategy is output.

[0172] Embodiment 1:

[0173] In order to verify the feasibility of the present invention in implementation, the present invention is applied to the database architecture of a large e-commerce platform. The database architecture of the large e-commerce platform includes multiple relational databases and non-relational database systems. Relational databases are mainly used to store structured data such as orders, user information and product information, while non-relational databases are used to store unstructured data such as user behavior logs, product reviews and pictures. Due to the huge business volume and frequent and complex query requests, the existing single database engine can no longer meet the query requirements of high concurrency and large data volumes, resulting in slow system response, poor user experience, and low data processing efficiency.

[0174] In order to enhance system performance and improve data processing efficiency, the platform decided to adopt a multi-engine database solution and introduced a method of combining SQL component nodes in multi-engine development to optimize the scheduling and execution of query tasks.

[0175] First, the platform's technical team established database connections in a variety of database management systems, including relational databases such as MySQL and PostgreSQL, as well as non-relational databases such as MongoDB and Cassandra. By defining standardized SQL component node interfaces and using heterogeneous data adaptation layers to convert and standardize data from different databases, we ensured that data could flow seamlessly between different database engines.

[0176] After receiving the query request, the distributed computing framework is used to schedule the SQL query task and assign the execution tasks to each SQL component node. The task scheduler assigns tasks based on the metadata of the task (including task ID, task priority, required resources, and expected execution time) and the metadata of the node (including node ID, node type, available resources, current load, and network latency). When executing SQL queries, the system uses a data transmission protocol based on Apache Avro and Zstandard compression algorithm to efficiently transmit the query results to the intermediate processing layer.

[0177] The middle processing layer uses the enhanced ETL process to convert the data format, clean the data, and synchronize the query results from different SQL component nodes, and finally merge them to generate a unified query result set. The adaptive result merging algorithm based on the random forest algorithm is used to analyze the query result characteristics and user needs, and adaptively adjust the merging strategy to ensure the accuracy and consistency of the query results.

[0178] Throughout the entire process, the real-time monitoring system continuously monitors the execution time, resource consumption, and load of each SQL component node, and collects the performance indicators of each node. By introducing a deep reinforcement learning model with graph neural networks and multi-head attention mechanisms, combined with historical execution data and current performance indicators of each node, the system can dynamically predict and optimize SQL component node combination strategies, and apply the optimized strategies to subsequent query tasks.

[0179] Table 1 Multi-engine SQL optimization technology performance improvement data table

[0180] Test indicators Before optimization After optimization Improvement effect System response time Average 2.5 seconds Average 1.2 seconds 52% shorter User information query time 3.0 seconds 1.4 seconds 53% improvement Order inquiry time 2.8 seconds 1.3 seconds 54% improvement Product information query time 2.2 seconds 1.0 sec 55% improvement Data transmission efficiency 200MB / s 450MB / s 125% improvement Data cleaning and format conversion time 6 hours 3 hours 50% shorter Data synchronization and result merging time 4 hours 2 hours 50% shorter Customer satisfaction 70％ 90％ Increase by 20 percentage points

[0181] Analysis of the data in Table 1 shows that before optimization, the average response time for the system to process each complex query was 2.5 seconds, which led to frequent system delays during peak hours. By introducing multi-engine SQL optimization technology, the system response time was shortened to 1.2 seconds, a reduction of 52%. The user information query time was reduced from 3.0 seconds before optimization to 1.4 seconds, an increase of 53%; the order query time was shortened from 2.8 seconds to 1.3 seconds, an increase of 54%; the product information query time was reduced from 2.2 seconds to 1.0 seconds, an increase of 55%. This significant improvement greatly improves the user experience, especially in a high-concurrency environment.

[0182] In terms of data transmission efficiency, the system could transmit 200MB of data per second before optimization, but this value increased to 450MB after optimization, and the transmission efficiency increased by 125%. This is due to the use of Apache Avro and Zstandard compression algorithms, which significantly reduced data transmission time and bandwidth usage.

[0183] Data processing efficiency has also been significantly improved. Before optimization, data cleaning and format conversion took 6 hours, which was reduced to 3 hours after optimization, a 50% reduction; the time for data synchronization and result merging was reduced from 4 hours to 2 hours, also a 50% reduction. This improvement ensures that the system can quickly and accurately process and integrate huge amounts of data from different databases.

[0184] User satisfaction has significantly increased after system optimization. Before optimization, user satisfaction with system response speed and query result accuracy was 70%, and after optimization it increased to 90%, an increase of 20 percentage points. This shows that users are highly satisfied with the system improvements and recognize the significant improvements in system response speed and data accuracy.

[0185] In summary, by introducing multi-engine SQL optimization technology, the system has significantly improved key performance indicators such as response speed, query processing efficiency, data transmission and processing capacity, and user satisfaction has also been significantly improved. These data and analysis fully prove the effectiveness and practicality of the present invention in solving the problems of the prior art.

[0186] The above description is only a preferred specific implementation manner of the present invention, but the protection scope of the present invention is not limited thereto. Any technician familiar with the technical field can make equivalent replacements or changes according to the technical scheme and inventive concept of the present invention within the technical scope disclosed by the present invention, which should be covered by the protection scope of the present invention.

Claims

1. A method for combining and using SQL component nodes in multi-engine development, characterized in that: The steps include: S1. Establishing database connections in a variety of database management systems, wherein the database management systems include relational databases and non-relational databases; S2, by defining standardized SQL component node interfaces, data is obtained from different database management systems, and the heterogeneous data adaptation layer is used to convert and standardize data formats; S3. Use the distributed computing framework to schedule SQL query tasks, assign execution tasks to each SQL component node, and execute SQL queries on each SQL component node; S4, using a data transmission protocol with a compression algorithm to transmit the query results to the intermediate processing layer; S5. Using the enhanced ETL process, the query results from different SQL component nodes are converted into data formats, cleaned, and synchronized in the intermediate processing layer, and then merged to generate a unified query result set. S6, using an adaptive result merging algorithm based on the random forest algorithm, by analyzing the query result characteristics and user needs, and adaptively adjusting the merging strategy; S7, monitor the execution time, resource consumption and load of SQL component nodes in real time, and collect performance indicators of each node; S8. Use a deep reinforcement learning model based on graph neural network and multi-head attention mechanism, combined with historical execution data and current performance indicators of each node, to dynamically predict and optimize SQL component node combination strategy; S9. Apply the optimized SQL component node combination strategy to the query task.

2. The method for using a combination of SQL component nodes in multi-engine development according to claim 1, characterized in that: The S3 specifically includes: S31, defining task metadata of the SQL query task, including task ID, task priority, required resources, and expected execution time; S32, establish a task queue, sort all SQL query tasks to be executed according to the priority in the task metadata, and enter the task queue; S33, defining node metadata of the SQL component node, including node ID, node type, available resources, current load, and network latency; S34, build a node resource pool, and manage all available SQL component nodes according to node metadata; S35, using the task scheduler in the distributed computing framework to extract the highest priority SQL query task from the task queue; S36. The task scheduler uses a task-node matching algorithm based on the node metadata and task metadata to assign the SQL query task to the appropriate SQL component node. The task-node matching algorithm expression is: Among them, T i represents the execution time of the ith task, T ik represents the time when the i-th task is executed for the kth time, N represents the number of executions, α, β and γ represent the weight parameters in the task scheduler, η and θ represent the weights of the expected value and variance of the execution time in the comprehensive matching score, respectively, E(T i ) represents the expected value of execution time, Var(T i ) represents the variance of execution time, L i Indicates the load of the i-th SQL component node, D i represents the network delay from the task scheduler to the i-th SQL component node, R i Indicates the degree of match between the resources of the i-th SQL component section and the resources required by the task; S37, assigning the node with the highest matching score to the current SQL query task, and updating the node metadata in the node resource pool; S38. Under the control of the distributed computing framework, start the SQL query task and monitor the execution status of the task and the resource usage of the node; S39. When a node is overloaded or fails, the task scheduler triggers the task migration mechanism to migrate SQL query tasks to other available SQL component nodes: Migration score = δ·(L i -L j )+ε·(R i -R j )+ζ·S j ; Among them, δ, ε and ζ represent the weight parameters in the task migration mechanism, L j Indicates the load of the jth SQL component node, R j represents the matching degree between the resources of the j-th SQL component section and the resources required by the task, S j represents the network stability of the jth SQL component node; S310: After the task is executed, the task scheduler returns the task result and updates the information in the task queue and the node resource pool.

3. The method for using a combination of SQL component nodes in multi-engine development according to claim 1, characterized in that: The data transmission protocol of the compression algorithm specifically includes a data transmission protocol based on Apache Avro and Zstandard compression algorithms, wherein the Apache Avro is used for data serialization and deserialization, and the Zstandard compression algorithm is used for data compression and decompression.

4. The method for combining and using SQL component nodes in multi-engine development according to claim 1, characterized in that: The S5 specifically includes: S51, receiving query result data from different SQL component nodes at the intermediate processing layer; S52, performing preliminary format conversion on the received query result data, converting the data of the heterogeneous data sources into a unified intermediate format, wherein the intermediate format is a standardized SQL data format; S53, using the enhanced ETL process to clean the data in the intermediate format, wherein the data cleaning includes removing duplicate data, correcting erroneous data, filling missing data and standardizing data format; S54. In the process of data cleaning, data normalization and standardization techniques are applied, and standardized formulas are used to transform data; S55, synchronizing the cleaned and standardized data, wherein the data synchronization includes aligning data with different timestamps and merging the data using a time window technology; S56. During the data merging process, a hash matching algorithm and a key field-based matching algorithm are applied to match and merge data from different data sources; S57, storing the merged data into a unified result data set, wherein the result data set is stored in a structured table form; S58, performing index optimization on the result data set and establishing an index structure, wherein the index structure includes a B-tree index, a hash index, and a full-text index; S59: Perform final verification and validation on the result data set, wherein the verification and validation include data consistency check, data integrity check and data accuracy check.

5. The method for using SQL component nodes in combination in multi-engine development according to claim 1, characterized in that: The S6 specifically includes: S61, construct training data set D = {(X i ,Y i )}, where X i represents the feature vector of the i-th query result, Y i Indicates the corresponding user demand label; S62, preprocess the training data set, including data cleaning, missing value filling and feature standardization, to obtain the preprocessed training data set D ′ ={(X ′ i ,Y i )}, where X ′ i represents the feature vector of the i-th query result after preprocessing; S63. From the training data set D ′ Randomly select a sample subset D from ″ , the size of the constructed sample subset is |D ″ |=α·|D ′ |, where α represents the sampling ratio; S64, in D ″ Randomly extract a feature subset from the above, the size of the feature subset is |F ″ |=β·|F|, where β represents the feature sampling ratio, and a decision tree T is constructed; S65, repeat steps S63-S64 to generate N decision trees, and obtain a random forest classifier RF = {T1, T2, ..., T N }; S66. Extract features from the result set of the current query task to obtain a query result feature vector X q , where the elements of the eigenvector are defined as: Among them, r kj represents the jth eigenvalue in the kth result, w kj represents the weight of the kth result, m represents the size of the result set, and φ represents the feature transformation function; S67, query result feature vector X q Input into the trained random forest classifier RF for classification prediction to obtain the preliminary merge strategy label Y q : Among them, argmax represents the index function of taking the maximum value, T i (X q ,y) represents the decision tree of the i-th decision tree for X q The predicted probability, ω i represents the weight of the i-th decision tree, and N represents the number of decision trees; S68. Calculate preliminary merging strategy Y q Performance indicators include query result accuracy A and response time TIME: Where n represents the number of test samples, Y i represents the true label set of the i-th sample, y ij represents the true label, represents the predicted label, δ represents the indicator function; S69. Adjust the merging strategy according to performance indicators and user needs, optimize the merging method of query results, and adjust the formula to: Among them, S represents the candidate strategy set, λ A and λ T represents the weight parameter; S610: Execute the optimized merging strategy to generate a final unified query result set.

6. The method for using a combination of SQL component nodes in multi-engine development according to claim 1, characterized in that: The S8 specifically includes: S81. Build a node performance database based on the historical execution data of the SQL component nodes, where the performance database includes the execution time, resource consumption, and load of each node; S82. Design a graph neural network model, where the nodes in the graph structure represent SQL component nodes, and the edges represent data dependencies between nodes; S83. Define the input of the graph neural network model as the historical performance indicators of the SQL component nodes and the current performance indicators of each node; S84. Apply the multi-head attention mechanism in the graph neural network model to the graph structure and calculate the attention weight e between each node and its neighboring nodes. ij : e ij =LeakyReLU(a T [Wh i ||Wh j ]); Among them, a represents the attention vector, W represents the weight matrix, and h i and h j Represent the feature vectors of node i and node j respectively, || represents the vector connection operation; S85, based on attention weight e ij Calculate the weighted sum of neighboring nodes to obtain the new feature expression h′ of node i i : Among them, σ represents the activation function, N(i) represents the set of neighbor nodes of node i, and α ij represents the normalized attention weight between node i and node j: S86, introduce node adaptive weight adjustment mechanism: Among them, h′ j represents the new feature expression of node j, λ represents the learning rate, β ij Represents the adaptive weight between node i and node j: Where u represents the adaptive weight vector; S87. Train the graph neural network model, use the loss function to measure the difference between the predicted results and the actual performance indicators, and optimize the parameters of the graph neural network model. The loss function is defined as: Where N represents the total number of samples, y i Indicates the actual performance index, represents the prediction performance index, γ represents the regularization parameter, and E represents the edge set in the graph; S88. After the training is completed, the graph neural network model is applied to the real-time system, the historical performance indicators of the SQL component nodes and the current performance indicators of each node are input, and the optimized SQL component node combination strategy is output.

Images