A method for combined use of SQL component nodes in multi-engine development

Abstract

The present invention discloses a method for combining and using SQL component nodes in multi-engine development, comprising the following steps: S1, establishing a database connection; S2, defining a standardized SQL component node interface to obtain data; S3, assigning execution tasks to each SQL component node and executing SQL queries on each SQL component node; S4, transmitting the query results to an intermediate processing layer; S5, utilizing an enhanced ETL process to process the query results in the intermediate processing layer and merge them to generate a unified query result set; S6, employing an adaptive result merging algorithm based on a random forest algorithm to adjust the merging strategy; S7, collecting performance indicators of each node; S8, using a deep reinforcement learning model based on a graph neural network and a multi-head attention mechanism to dynamically predict and optimize the SQL component node combination strategy; and S9, applying the optimized SQL component node combination strategy to query tasks. The present invention utilizes a distributed computing framework and deep reinforcement learning to achieve multi-engine SQL optimization.

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 constantly 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 (DBMSs) are no longer able to meet the complex data processing and query requirements. Therefore, the collaborative operation 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, and SQLite) and non-relational databases (such as MongoDB and Cassandra) each leverage their strengths to jointly address data processing challenges.

[0003] In existing technologies, implementing multi-engine databases typically relies on developers manually writing complex scripts to exchange and process data between different database engines. This approach is not only time-consuming and labor-intensive, but also prone to errors and difficult to maintain and scale. Especially when processing large-scale queries, manual scripting fails to fully utilize the performance of each database engine, resulting in low query efficiency and inefficient system resource utilization.

[0004] Furthermore, existing multi-engine database systems often lack intelligent task scheduling and node combination strategies. In a multi-engine environment, intelligently selecting and combining different SQL component nodes based on the specific requirements of query tasks and the current system load is key to improving overall system performance. Existing technologies often employ static task scheduling and node combination strategies, lacking the ability to dynamically adjust, making them difficult to adapt to changing query requirements and system status.

[0005] Furthermore, existing technologies also suffer from numerous shortcomings in data transmission and processing efficiency. While some systems employ compression algorithms and data transmission protocols, data transmission efficiency remains unsatisfactory when faced with complex query tasks and large datasets. Data format conversion, data cleansing, and data synchronization are complex and time-consuming, impacting system response speed and query performance.

[0006] When it comes to data integration, existing technologies mostly focus on processing data between database engines of the same type, but lack the ability to integrate and process data across multiple engines. Different database engines have varying data formats, making efficient data integration and processing while ensuring data consistency and integrity a technical challenge. Existing solutions often suffer from significant latency and poor data consistency during the data integration process, making them difficult to meet the demands of real-time data processing.

[0007] In terms of real-time monitoring and dynamic adjustment, existing multi-engine SQL query optimization technologies are typically static and lack the ability to perceive and adjust system status in real time. This prevents the system from adjusting node combination strategies in a timely manner when load and query demand fluctuate, 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 optimal node combination strategies, query execution efficiency can be significantly improved. However, existing technologies still exploratory in their application to multi-engine SQL query optimization, and a systematic and practical solution has yet to be developed.

[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 objective of the present invention is to propose a method for combining and using SQL component nodes in multi-engine development. This method uses a deep reinforcement learning model with a graph neural network and a multi-head attention mechanism, combined with Apache Avro and the Zstandard compression algorithm, 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, enhancing the system's flexibility, stability, and execution efficiency. The present invention also ensures the accuracy and consistency of query results through an adaptive result merging algorithm, providing high efficiency, reliability, and scalability to meet complex and diverse database query and processing requirements.

[0011] A method for combining and using 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 multiple database management systems, including 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, query results from different SQL component nodes are converted into data formats, cleaned, and synchronized in the intermediate processing layer to generate a unified query result set.

[0017] S6. Adopt an adaptive result merging algorithm based on the random forest algorithm to adaptively adjust the merging strategy by analyzing the query result characteristics and user needs;

[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 networks 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 strategies;

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

[0021] Furthermore, the S3 specifically includes:

[0022] S31. Define the 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. Define the 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. Utilize 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 SQL query tasks to appropriate SQL component nodes. The task-node matching algorithm expression is:

[0028]

[0029] Among them, T i represents the execution time of the i-th task, T ik represents the time of the i-th task at the k-th execution, 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 degree of match 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. Assign the node with the highest matching score to the current SQL query task, and update 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 j-th SQL component node, R j Indicates 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 j-th 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 node resource pool.

[0036] Furthermore, the compression algorithm's data transmission protocol specifically includes a data transmission protocol based on Apache Avro and the Zstandard compression algorithm, with Apache Avro used for data serialization and deserialization and the Zstandard compression algorithm used for data compression and decompression. By first serializing query results into a compact binary format and then efficiently compressing them using the Zstandard algorithm, combining the advantages of both, efficient and reliable data transmission is achieved, significantly reducing transmission latency and bandwidth usage, and improving 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: Perform preliminary format conversion on the received query result data, converting the data from heterogeneous data sources into a unified intermediate format, where the intermediate format is a standardized SQL data format;

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

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

[0042] S55. Synchronize the cleaned and standardized data. The data synchronization includes aligning data with different timestamps and merging the data using a time window technique 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 in a structured table format to ensure that the query results can be efficiently processed and analyzed later;

[0045] S58, performing index optimization on the result data set, 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 integrity 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 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;

[0050] S63. Randomly extract a sample subset D″ from the training data set D′, and construct a sample subset of size |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 the 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 k-th result, m represents the size of the result set, and φ represents the feature transformation function used to enhance feature representation;

[0056] S67, query result feature vector X q Input into the trained random forest classifier RF, perform classification prediction, and 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 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] Among them, 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, and δ represents the indicator function;

[0062] S69. Adjust the merging strategy based on performance indicators and user needs to optimize the merging method of query results. The adjustment formula is:

[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 historical execution data of SQL component nodes, where the performance database includes 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 the data dependency relationships 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] Where 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 and use the loss function to measure the difference between the predicted results and the actual performance indicators to optimize the parameters of the graph neural network model. The loss function is defined as:

[0083]

[0084] Among them, 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 a standardized SQL component node interface and a heterogeneous data adaptation layer, this invention enables seamless collaboration among different types of database management systems. Developers can integrate and process multi-engine databases without writing complex scripts, greatly simplifying development and maintenance and improving system flexibility and scalability.

[0088] Secondly, the present invention utilizes a data transmission protocol based on Apache Avro and the Zstandard compression algorithm, significantly improving data transmission efficiency and reducing transmission latency and bandwidth usage. During data processing, an enhanced ETL process is utilized for data format conversion, data cleaning, and data synchronization, effectively ensuring data consistency and integrity and increasing data processing speed. These measures enable the system to efficiently process and transmit large amounts of data, meeting the requirements for efficient querying and real-time processing.

[0089] This paper implements data integration and processing across multiple database engines by building a standardized SQL component node interface. Using an adaptive result merging algorithm, the data merging strategy is dynamically adjusted based on query result characteristics and user needs, ensuring data accuracy and consistency and meeting complex and diverse data processing requirements. This method not only improves data processing efficiency but also enhances system reliability and flexibility.

[0090] This invention also introduces a real-time monitoring mechanism that monitors the execution time, resource consumption, and load of SQL component nodes in real time, and dynamically adjusts them based on this monitoring data. By optimizing the combined strategy of SQL component nodes through load balancing and parallel processing, the system maintains optimal performance under varying loads and query demands, improving system stability and responsiveness. This real-time monitoring and dynamic adjustment mechanism enables the system to rapidly respond to changes in load and query demand, maintaining efficient operation.

[0091] Furthermore, an adaptive result merging algorithm based on the random forest algorithm analyzes query result characteristics and user needs, adaptively adjusting the merging strategy to improve query result accuracy and response time. This method effectively handles large-scale data query tasks and significantly improves system performance. Furthermore, a task migration mechanism is introduced during task scheduling. When a node becomes overloaded or experiences a failure, the task scheduler promptly migrates SQL query tasks to other available SQL component nodes, ensuring system reliability and continuity.

[0092] Finally, this paper applies deep learning and artificial intelligence technologies to multi-engine SQL query optimization. By using a deep reinforcement learning model with a graph neural network and a multi-head attention mechanism, it analyzes and predicts the optimal node combination strategy, improving the efficiency and accuracy of query execution. This method continuously learns and adapts to system changes, providing more intelligent optimization solutions and further enhancing 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 This is a flow chart 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 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, which are simplified schematic diagrams that illustrate the basic structure of the present invention in a schematic manner.

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

[0098] S1. Establishing database connections in multiple database management systems, including 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, query results from different SQL component nodes are converted into data formats, cleaned, and synchronized in the intermediate processing layer to generate a unified query result set.

[0103] S6. Adopt an adaptive result merging algorithm based on the random forest algorithm to adaptively adjust the merging strategy by analyzing the query result characteristics and user needs;

[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 networks 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 strategies;

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

[0107] In this embodiment, S3 specifically includes:

[0108] S31. Define the 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. Define the 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. Utilize 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 SQL query tasks to appropriate SQL component nodes. The task-node matching algorithm expression is:

[0114]

[0115] Among them, T i represents the execution time of the i-th task, T ik represents the time of the i-th task at the k-th execution, 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 degree of match 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. Assign the node with the highest matching score to the current SQL query task, and update 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 j-th SQL component node, R j Indicates 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 j-th 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 node resource pool.

[0122] In this embodiment, the data transmission protocol using the compression algorithm specifically includes a data transmission protocol based on Apache Avro and the Zstandard compression algorithm. Apache Avro is used for data serialization and deserialization, and the Zstandard compression algorithm is used for data compression and decompression. By first serializing query results into a compact binary format and then efficiently compressing them using the Zstandard algorithm, combining the advantages of both, efficient and reliable data transmission is achieved, significantly reducing transmission latency and bandwidth usage, and improving overall system performance.

[0123] In this embodiment, the S5 specifically includes:

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

[0125] S52: Perform preliminary format conversion on the received query result data, converting the data from heterogeneous data sources into a unified intermediate format, where the intermediate format is a standardized SQL data format;

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

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

[0128] S55. Synchronize the cleaned and standardized data. The data synchronization includes aligning data with different timestamps and merging the data using a time window technique 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 in a structured table format to ensure that the query results can be efficiently processed and analyzed later;

[0131] S58, performing index optimization on the result data set, 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 integrity check and data accuracy check.

[0133] In this embodiment, 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 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;

[0136] S63. Randomly extract a sample subset D″ from the training data set D′, and construct a sample subset of size |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 the 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 k-th result, m represents the size of the result set, and φ represents the feature transformation function used to enhance feature representation;

[0142] S67, query result feature vector X q Input into the trained random forest classifier RF, perform classification prediction, and 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 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] Among them, 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, and δ represents the indicator function;

[0148] S69. Adjust the merging strategy based on performance indicators and user needs to optimize the merging method of query results. The adjustment formula is:

[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 embodiment, the S8 specifically includes:

[0153] S81. Build a node performance database based on historical execution data of SQL component nodes, where the performance database includes 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 the data dependency relationships 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] Where 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 and use the loss function to measure the difference between the predicted results and the actual performance indicators to optimize the parameters of the graph neural network model. The loss function is defined as:

[0169]

[0170] Among them, 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] Example 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 this 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 connectivity across multiple database management systems, including relational databases like MySQL and PostgreSQL, as well as non-relational databases like MongoDB and Cassandra. By defining standardized SQL component node interfaces and utilizing a heterogeneous data adaptation layer to convert and standardize data from different databases, they ensured seamless data flow between different database engines.

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

[0177] The intermediate processing layer utilizes an enhanced ETL process to convert, clean, and synchronize query results from different SQL component nodes, ultimately merging them to generate a unified query result set. An adaptive result merging algorithm based on the random forest algorithm analyzes query result characteristics and user needs, adaptively adjusting the merging strategy to ensure the accuracy and consistency of 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, collecting performance metrics for each node. By incorporating a deep reinforcement learning model using graph neural networks and a multi-head attention mechanism, combined with historical execution data and current node performance metrics, the system can dynamically predict and optimize SQL component node combination strategies and apply these 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% increase Order inquiry time 2.8 seconds 1.3 seconds 54% increase Product information query time 2.2 seconds 1.0 seconds 55% increase Data transmission efficiency 200MB / s 450MB / s 125% increase Data cleaning and format conversion time 6 hours 3 hours 50% shorter Data synchronization and result merging time 4 hours 2 hours 50% shorter User satisfaction 70％ 90％ Increased by 20 percentage points

[0181] Analysis of the data in Table 1 shows that before optimization, the system's average response time for each complex query was 2.5 seconds, resulting in frequent system delays during peak usage. By introducing multi-engine SQL optimization technology, system response time was reduced to 1.2 seconds, a 52% reduction. User information query time decreased from 3.0 seconds before optimization to 1.4 seconds, a 53% improvement; order query time decreased from 2.8 seconds to 1.3 seconds, a 54% improvement; and product information query time decreased from 2.2 seconds to 1.0 second, a 55% improvement. This significant improvement has greatly improved the user experience, especially in high-concurrency environments.

[0182] In terms of data transmission efficiency, the system could transmit 200MB of data per second before optimization, but this figure increased to 450MB per second after optimization, a 125% increase in transmission efficiency. This is due to the use of Apache Avro and the Zstandard compression algorithm, 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 six hours, but after optimization, this was reduced to three hours, a 50% reduction. Data synchronization and result merging took two hours, down from four hours, also a 50% reduction. This improvement ensures the system can quickly and accurately process and integrate massive amounts of data from various databases.

[0184] User satisfaction has significantly increased after system optimization. Before optimization, user satisfaction with system response speed and query result accuracy was 70%. After optimization, this rose to 90%, a 20 percentage point increase. This demonstrates a high level of user satisfaction with the system improvements and their recognition of the significant improvements in 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 increased significantly. These data and analysis fully demonstrate the effectiveness and practicality of this invention in solving existing technical problems.

[0186] The above description is only a preferred specific embodiment of the present invention, but the scope of protection of the present invention is not limited thereto. Any technician familiar with the technical field, within the technical scope disclosed by the present invention, who makes equivalent replacements or changes based on the technical solution and inventive concept of the present invention, should be covered by the scope of protection 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 multiple database management systems, including 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, query results from different SQL component nodes are converted into data formats, cleaned, and synchronized in the intermediate processing layer to generate a unified query result set. S6. Adopt an adaptive result merging algorithm based on the random forest algorithm to adaptively adjust the merging strategy by analyzing the query result characteristics and user needs; 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 networks 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 strategies; S9. Apply the optimized SQL component node combination strategy to the query task.

2. The method for combining and using SQL component nodes in multi-engine development according to claim 1, characterized in that: The S3 specifically includes: S31. Define the 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. Define the 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. Utilize 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 SQL query tasks to appropriate SQL component nodes. The task-node matching algorithm expression is: Among them, T i represents the execution time of the i-th task, T ik represents the time of the i-th task at the k-th execution, 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 matching between the resources of the i-th SQL component section and the resources required by the task; S37. Assign the node with the highest matching score to the current SQL query task, and update 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 j-th SQL component node, R j Indicates 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 j-th 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 node resource pool.

3. The method for combining and using 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 algorithm, 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: Perform preliminary format conversion on the received query result data, converting the data from heterogeneous data sources into a unified intermediate format, where the intermediate format is a standardized SQL data format; S53. Using an enhanced ETL process to clean the intermediate format data, the data cleaning includes removing duplicate data, correcting erroneous data, filling missing data, and standardizing the data format; S54. During the data cleaning process, data normalization and standardization techniques are applied, and standardized formulas are used to transform the data; S55. Synchronize the cleaned and standardized data. The data synchronization includes aligning data with different timestamps and merging the data using a time window technique. 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 format; S58. Optimize the index of the result data set and establish 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, including data consistency check, data integrity check and data accuracy check.

5. The method for combining and using SQL component nodes 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 the 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 k-th 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, perform classification prediction, and 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 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: Among them, 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, and δ represents the indicator function; S69. Adjust the merging strategy based on performance indicators and user needs to optimize the merging method of query results. The adjustment formula is: 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 combining and using 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 historical execution data of SQL component nodes, where the performance database includes 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 the data dependency relationships 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: Where 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 and use the loss function to measure the difference between the predicted results and the actual performance indicators to optimize the parameters of the graph neural network model. The loss function is defined as: Among them, 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