A SQL statement forwarding method and device, an electronic device, and a storage medium

Abstract

This disclosure proposes an SQL statement forwarding method, apparatus, electronic device, and storage medium. The method includes: receiving an SQL statement forwarding request, the SQL statement forwarding request containing a first SQL statement to be forwarded; obtaining hardware status information of each node in a node cluster; the node cluster includes at least one node, and each of the at least one node runs at least one database instance, the database instance being used to forward the first SQL statement; calculating a node score for each node based on the hardware status information of each node; calculating an instance score for each database instance in each node based on the association between the first SQL statement and a second SQL statement; the second SQL statement being the SQL statement being executed by the database instance; and determining the database instance used to forward the first SQL statement based on the node scores of each node in the node cluster and the instance scores of each database instance in each node.

Description

Technical Field

[0001] This disclosure relates to the field of database technology, and in particular to a method, apparatus, electronic device and storage medium for forwarding SQL statements. Background Technology

[0002] Database proxy devices in related technologies only determine the liveness of database instances in the cluster, without performance evaluation. Furthermore, the forwarding strategy for database instances is fixed and cannot be dynamically adjusted according to actual conditions. Summary of the Invention

[0003] This disclosure provides a method, apparatus, electronic device, and storage medium for forwarding SQL statements to solve problems in related technologies.

[0004] A first aspect of this disclosure provides a method for forwarding SQL statements, the method comprising:

[0005] Receive an SQL statement forwarding request, which contains the first SQL statement to be forwarded.

[0006] Obtain the hardware status information of each node in the node cluster; the node cluster contains at least one node, and each of the at least one node runs at least one database instance, which is used to forward the first SQL statement;

[0007] Calculate the node score for each node based on the hardware status information of each node;

[0008] Based on the relationship between the first SQL statement and the second SQL statement, calculate the instance score for each database instance in each node; the second SQL statement is the SQL statement that the database instance is currently executing.

[0009] Based on the node score of each node in the node cluster and the instance score of each database instance in each node, the database instance used to forward the first SQL statement is determined.

[0010] In some embodiments of this disclosure, the method provided further includes:

[0011] Determine if the first SQL statement is an inefficient SQL statement;

[0012] If the judgment result is yes, rewrite the first SQL statement according to the preset optimization rules.

[0013] In some embodiments of this disclosure, a node score is calculated for each node based on its hardware status information, including:

[0014] Based on the hardware status information of each node and the preset hardware status information weights, calculate the first node score for each node;

[0015] In response to a backup action being triggered by at least one first database instance, the cost of the first database instance performing the backup action is obtained; the cost indicates the hardware overhead of the node corresponding to the first database instance when the backup action is triggered.

[0016] The second node score for each node is calculated based on the cost of triggering backup behavior on the first database instance, the first node score for each node, and the time period during which the first database instance performs backup behavior.

[0017] In some embodiments of this disclosure, a second node score is calculated for each node based on the cost incurred when the first database instance triggers a backup action, the first node score for each node, and the time period during which the first database instance performs the backup action, including:

[0018] If the time of receiving the SQL statement forwarding request is not within the time period when the first database instance is performing backup behavior, then the first node score for each node is determined to be the second node score;

[0019] If the SQL statement forwarding request is received during the period when the first database instance is performing backup operations, then

[0020] Determine the node corresponding to the first database instance that performs the backup operation in each node;

[0021] Based on the cost of triggering backup behavior when the first database instance is used and the first node score of each node, calculate the second node score of the node corresponding to the first database instance that performs backup behavior in each node.

[0022] For each node, the first node score is assigned to the node corresponding to the database instance that does not perform backup actions, and the second node score is assigned to the node corresponding to that instance.

[0023] In some embodiments of this disclosure, based on the association between the first SQL statement and the second SQL statement, an instance score is calculated for each database instance in each node, including:

[0024] Obtain the first execution cost when the database instance forwards an SQL statement;

[0025] Determine if any of the nodes contain a database instance currently executing the second SQL statement;

[0026] If the result is yes, then determine whether the first SQL statement is associated with the second SQL statement;

[0027] If the judgment result is yes, then obtain the second execution cost of the database instance that is currently executing the second SQL statement;

[0028] The first execution cost is determined as the instance score of the database instance that does not execute the second SQL statement in each database instance; and the sum of the first execution cost and the second execution cost is determined as the instance score of the database instance that is executing the second SQL statement in each database instance.

[0029] In some embodiments of this disclosure, obtaining the second execution cost of the database instance executing the second SQL statement includes:

[0030] Retrieve the mapping table; the mapping table is used to indicate the mapping relationship between the forwarding subprocess that executes the SQL statement and the lifecycle of the corresponding SQL statement;

[0031] Determine whether a forwarding subprocess exists in the mapping table that corresponds to the lifecycle of the second SQL statement;

[0032] If the result is negative, then determine whether the forwarding subprocess corresponding to the lifecycle of the second SQL statement can be deleted.

[0033] If the judgment result is yes, then the second execution cost of the database instance that is executing the second SQL statement is the first preset value;

[0034] If the judgment result is negative, and the second SQL statement is a read-only statement, then the second execution cost of the database instance executing the second SQL statement is the same as the first execution cost.

[0035] If the judgment result is yes, and the second SQL statement contains a write operation, then the second execution cost of the database instance executing the second SQL statement is the second preset value.

[0036] In some embodiments of this disclosure, after determining whether the first SQL statement is associated with the second SQL statement, the method provided in this disclosure further includes:

[0037] If the result is negative, parse the first SQL statement and the second SQL statement, and determine whether there is any overlap between the first SQL statement and the second SQL statement based on the parsing result.

[0038] If the judgment result is negative, the second execution cost of the database instance executing the second SQL statement is the first preset value;

[0039] If the judgment result is yes, and the overlapping part is a read-only statement, then the second execution cost of the database instance executing the second SQL statement is the same as the first execution cost;

[0040] If the judgment result is yes, and the overlapping part contains a write operation, then the second execution cost of the database instance executing the second SQL statement is the second preset value.

[0041] In some embodiments of this disclosure, the instance used to forward the first SQL statement is determined based on the node score of each node in the node cluster and the instance score of each instance in each node, including:

[0042] Based on preset node scores, all nodes in the node cluster are divided into high-performance nodes and low-performance nodes.

[0043] Based on the instance score of each instance in the high-performance node, the instance with the highest instance score is determined as the instance used to forward the first SQL statement.

[0044] In some embodiments of this disclosure, determining whether the first SQL statement is an inefficient SQL statement includes:

[0045] Parse the first SQL statement and generate a query plan tree;

[0046] Prune and decompose the query plan tree to obtain the decomposed query statements;

[0047] If the decomposed query statement contains at least one of the following conditions, the first SQL statement is determined to be an inefficient SQL statement;

[0048] The query statement contains a SELECT statement, and the SELECT statement contains operations that require repeated scanning;

[0049] The query statement contains an insert statement, and the insert statement contains batches of non-transactional dependencies that can be merged;

[0050] The query statement contains INSERT or UPDATE statements, and the query scope indicated by the INSERT or UPDATE statements does not overlap with the target database; the target database refers to the database corresponding to the first SQL statement.

[0051] A second aspect of this disclosure provides an SQL statement forwarding apparatus, the apparatus comprising:

[0052] The receiving unit is used to receive SQL statement forwarding requests, which contain the first SQL statement to be forwarded.

[0053] The acquisition unit is used to acquire the hardware status information of each node in the node cluster; the node cluster contains at least one node, and each of the at least one node runs at least one database instance, which is used to forward the first SQL statement;

[0054] The first computing unit is used to calculate the node score of each node based on the hardware status information of each node.

[0055] The second calculation unit is used to calculate the instance score of each database instance in each node based on the association between the first SQL statement and the second SQL statement; the second SQL statement is the SQL statement that the database instance is currently executing.

[0056] The determination unit is used to determine the database instance to forward the first SQL statement based on the node score of each node in the node cluster and the instance score of each database instance in each node.

[0057] A third aspect of this disclosure provides an electronic device comprising:

[0058] At least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor to enable the at least one processor to perform the methods described in the first aspect of this disclosure.

[0059] A fourth aspect of this disclosure provides a non-transitory computer-readable storage medium storing computer instructions, wherein the computer instructions are used to cause a computer to perform the methods described in the first aspect of this disclosure.

[0060] In summary, this disclosure proposes an SQL statement forwarding method, which includes: receiving an SQL statement forwarding request, the SQL statement forwarding request containing a first SQL statement to be forwarded; obtaining hardware status information of each node in a node cluster; the node cluster contains at least one node, and each of the at least one node runs at least one database instance, the database instance being used to forward the first SQL statement; calculating a node score for each node based on the hardware status information of each node; calculating an instance score for each database instance in each node based on the association between the first SQL statement and the second SQL statement; the second SQL statement being the SQL statement being executed by the database instance; and determining the database instance used to forward the first SQL statement based on the node score of each node in the node cluster and the instance score of each database instance in each node.

[0061] The solution provided in this disclosure calculates a score for each cluster node based on the hardware status information of each node, and also scores the database instances in each node. By scoring the database instances in each node, the database instance used for forwarding SQL statements is determined, thereby improving the flexibility of statement forwarding when users initiate forwarding request statements, improving the execution efficiency of statements when users initiate forwarding request statements, and meeting the user's business needs.

[0062] It should be understood that the above general description and the following detailed description are exemplary and explanatory only, and are not intended to limit this disclosure. Attached Figure Description

[0063] The accompanying drawings, which are incorporated in and form part of this specification, illustrate embodiments consistent with this disclosure and, together with the description, serve to explain the principles of this disclosure, and are not intended to unduly limit this disclosure.

[0064] Figure 1 A flowchart illustrating an SQL statement forwarding method provided in this embodiment of the disclosure;

[0065] Figure 2 A flowchart illustrating the second SQL statement forwarding method provided in this disclosure embodiment;

[0066] Figure 3 A flowchart illustrating the third SQL statement forwarding method provided in this disclosure embodiment;

[0067] Figure 4 A flowchart illustrating the fourth SQL statement forwarding method provided in this disclosure embodiment;

[0068] Figure 5 A flowchart illustrating the fifth SQL statement forwarding method provided in this disclosure embodiment;

[0069] Figure 6 A flowchart illustrating the sixth SQL statement forwarding method provided in this disclosure embodiment;

[0070] Figure 7 A flowchart illustrating the seventh SQL statement forwarding method provided in this disclosure embodiment;

[0071] Figure 8 A flowchart illustrating the eighth SQL statement forwarding method provided in this disclosure embodiment;

[0072] Figure 9 A flowchart of the ninth SQL statement forwarding method provided in this disclosure embodiment;

[0073] Figure 10 This is a schematic diagram of the structure of the SQL statement forwarding device provided in the embodiments of this disclosure;

[0074] Figure 11 This is a schematic diagram of the hardware composition structure of an electronic device provided in an embodiment of this disclosure. Detailed Implementation

[0075] The present application will now be described in further detail with reference to the accompanying drawings and embodiments. It is to be understood that the specific embodiments described herein are merely illustrative of the invention and not intended to limit it. The described embodiments are only a part of the embodiments of this application, and not all of them. All other embodiments obtained by those skilled in the art based on the embodiments of this application without inventive effort are within the scope of protection of this application.

[0076] It should be noted that, for ease of description, only the parts relevant to the invention are shown in the accompanying drawings. Unless otherwise specified, the embodiments and features described in this application can be combined with each other.

[0077] It should be understood that the terms "system," "apparatus," "unit," and / or "module" used in this application are a method of distinguishing different components, elements, parts, sections, or assemblies at different levels. However, if other terms can achieve the same purpose, they may be replaced by other expressions.

[0078] As indicated in this application and claims, unless the context clearly indicates otherwise, the words "a," "an," "a," and / or "the" are not specifically singular and may include the plural. Generally, the terms "comprising" and "including" only indicate the inclusion of expressly identified steps and elements, which do not constitute an exclusive list, and the method or apparatus may also include other steps or elements. An element defined by the phrase "comprising an..." does not exclude the presence of other identical elements in the process, method, product, or apparatus that includes the element.

[0079] In the description of the embodiments of this application, unless otherwise stated, " / " means "or", for example, A / B can mean A or B; "and / or" in this document is merely a description of the relationship between related objects, indicating that three relationships can exist. For example, A and / or B can represent: A existing alone, A and B existing simultaneously, and B existing alone. Furthermore, in the description of the embodiments of this application, "multiple" refers to two or more.

[0080] Hereinafter, the terms "first" and "second" are used for descriptive purposes only and should not be construed as indicating or implying relative importance or implicitly specifying the number of technical features indicated. Thus, a feature defined as "first" or "second" may explicitly or implicitly include one or more of that feature.

[0081] Flowcharts are used in this application to illustrate the operations performed by the system according to embodiments of this application. It should be understood that the preceding or following operations are not necessarily performed precisely in sequence. Instead, the steps can be processed in reverse order or simultaneously. Furthermore, other operations can be added to these processes, or one or more steps can be removed from them.

[0082] Database proxy devices in related technologies only determine the liveness of database instances in the cluster, without performance evaluation. Furthermore, the forwarding strategy for database instances is fixed and cannot be dynamically adjusted according to actual conditions. This results in excessively long execution times for user-initiated forwarding requests, failing to meet user business needs.

[0083] In related technologies, common database proxy middleware implementation methods are as follows: After the database proxy tool starts, a monitor subprocess is launched to monitor the status of the user-configured forwarding databases. Once the monitor detects that the current backend database is running normally, it creates N connections for each backend database as subsequent connection requests. When a user request is received, the request undergoes simple SQL syntax parsing and semantic completeness checks, and is distributed according to the current query type. If the user has configured read / write separation, read-only queries are distributed to the backend read-only databases, and write requests are distributed to the primary database, according to the rules. After distribution, the user waits for the backend database to return the execution result. Once the backend database returns the execution result, it is returned to the client.

[0084] In the above methods, existing database proxy middleware technologies only determine the liveness of database instances in the cluster, without performing performance checks. This results in data request statements with high performance requirements being distributed to database instances that are not being checked for performance, leading to excessively long execution times for these data request statements and causing business operations that rely on these data requests to fail.

[0085] To address at least one deficiency in related technologies, this disclosure calculates a score for each cluster node based on the hardware status information of each node on the server side, and simultaneously scores the database instances in each node. By scoring the database instances in each node, the database instance used for forwarding SQL statements is determined, thereby improving the flexibility of statement forwarding when users initiate forwarding request statements, enhancing the execution efficiency of statements when users initiate forwarding request statements, and meeting the user's business needs.

[0086] The present disclosure will now be described in further detail with reference to the accompanying drawings and specific embodiments.

[0087] like Figure 1 As shown, Figure 1This is a flowchart illustrating an SQL statement forwarding method provided in an embodiment of this disclosure. The SQL statement forwarding method provided in this embodiment can be executed by an SQL statement forwarding server, which can be a local host or a virtual host.

[0088] The SQL statement forwarding method provided in this disclosure includes the following steps:

[0089] Step 101: Receive SQL statement forwarding request;

[0090] In one embodiment, the SQL statement forwarding request includes a first SQL statement to be forwarded.

[0091] In one embodiment, the first SQL statement refers to the SQL query, insert, update, or delete data statement to be executed in the forwarding request.

[0092] In one embodiment, the SQL statement forwarding request includes the following:

[0093] SQL statement: The SQL query or operation statement to be executed in the forwarding request;

[0094] Database connection information: In order to execute SQL statements, the necessary information to establish a connection with the database is required;

[0095] Database type: Specifies the type of database to connect to, such as MySQL, Oracle, SQL Server, etc.

[0096] Step 102: Obtain the hardware status information of each node in the node cluster;

[0097] In one embodiment, the node cluster contains at least one node.

[0098] In one embodiment, a node cluster refers to a collection of multiple computing nodes, typically used in distributed systems, where each node independently runs the same application or service and they communicate and collaborate with each other over a network.

[0099] In one embodiment, at least one database instance runs on each of at least one node.

[0100] In one embodiment, the database instance is used to forward SQL statements.

[0101] In one embodiment, the hardware status information includes at least one of the following: CPU, memory, data disk I / O, and network bandwidth utilization.

[0102] Step 103: Calculate the node score for each node based on the hardware status information of each node;

[0103] In one embodiment, the method for calculating the node score for each node includes:

[0104] The distributed system deploys a proxy and a server on the server side. The proxy collects hardware status information from each node, and the server calculates the initial node score for each node based on the weights of the hardware status information. Then, using the cost function for backing up data in a database instance, the cost incurred during database backup is calculated. Finally, based on the initial node Cost of database backup The node score for each node is calculated. .

[0105] Step 104: Based on the relationship between the first SQL statement and the second SQL statement, calculate the instance score for each database instance in each node;

[0106] In one embodiment, the second SQL statement is the SQL statement that the database instance is currently executing.

[0107] In one embodiment, the method for calculating the instance score for each database instance in each node includes:

[0108] The SQL statement is input into the database agent's data preprocessing engine. Based on the statistical information collected previously, the execution of the SQL statement in the database instance is simulated to calculate the initial execution cost incurred by the database in executing the query statement. This cost is manually marked as follows. The second execution cost, which arises from the interaction between the SQL statement to be forwarded and the statement currently being executed in the instance, is manually marked as... .

[0109] The database instances in the database cluster connected to each node obtain the SQL statement being executed in the instance and perform a hash comparison with the current SQL statement. If the SQL statement being executed in the instance is consistent with the statement to be forwarded, the corresponding proxy service subprocess is found according to the database proxy device mapping table.

[0110] If no agent child process with the same lifecycle as the currently executing SQL statement is found, the database agent considers the SQL statement to be a residual statement and sends a kill command to the database instance to terminate the execution process. After waiting for 1 second, if the process cannot be terminated, the query execution on the node where the current instance resides will incur additional execution costs. If the SQL statement is a read-only statement, then... If the statement involves a write operation, then .

[0111] If it can be killed normally, no additional execution cost will be incurred. If the SQL statement being executed in the instance is inconsistent with the statement to be forwarded, the currently executing SQL statement is parsed into a query execution plan tree and decomposed into query phases. This tree structure is then compared with the query execution plan tree parsed from the statement being executed in the current instance. A tree structure overlap detection is performed. If an overlap is detected, the two statements will affect each other, resulting in a deadlock. If both the SQL statement and the SQL statement overlapping in execution phase are read SQL statements, then... ,otherwise, .

[0112] Based on the above steps, ,in, The final score is assigned to each database instance in each node.

[0113] In one embodiment, the execution statistics of SQL statements in the simulated database instance refer to the table execution information in the current database that exists in the current database system tables.

[0114] Step 105: Based on the node score of each node in the node cluster and the instance score of each database instance in each node, determine the database instance used to forward the first SQL statement.

[0115] In one embodiment, the node scoring method based on each node in the node cluster includes:

[0116] The nodes are scored based on their hardware specifications. Ranking: If a node's ranking is too low, it is removed from the high-performance node set and simultaneously moved to the low-performance node set. If a node's ranking is normal and it is not in the high-performance node set, it is added to the high-performance node set and simultaneously removed from the low-performance node set.

[0117] In one embodiment, the method for scoring each database instance in each node includes:

[0118] Based on the instances on each node The system selects an available instance from all instances, forwards the query request, and waits for the result to be returned.

[0119] In summary, the SQL statement forwarding method proposed in this disclosure includes: receiving an SQL statement forwarding request, wherein the SQL statement forwarding request contains a first SQL statement to be forwarded; obtaining hardware status information of each node in a node cluster; wherein the node cluster contains at least one node, and each of the at least one node runs at least one database instance, the database instance being used to forward the first SQL statement; calculating a node score for each node based on the hardware status information of each node; calculating an instance score for each database instance in each node based on the association between the first SQL statement and the second SQL statement; wherein the second SQL statement is the SQL statement being executed by the database instance; and determining the database instance used to forward the first SQL statement based on the node score of each node in the node cluster and the instance score of each database instance in each node.

[0120] The solution provided in this disclosure calculates a score for each cluster node based on the hardware status information of each node, and also scores the database instances in each node. By scoring the database instances in each node, the database instance used for forwarding SQL statements is determined, thereby improving the flexibility of statement forwarding when users initiate forwarding request statements, improving the execution efficiency of statements when users initiate forwarding request statements, and meeting the user's business needs.

[0121] Figure 2 A flowchart of the SQL statement forwarding method proposed in this disclosure is further shown. Based on Figure 1 The illustrated embodiment further explains the method by including the following steps:

[0122] Step 201: Determine whether the first SQL statement is an inefficient SQL statement;

[0123] In one embodiment, the method for determining whether the first SQL statement is an inefficient SQL statement is as follows:

[0124] Semantic parsing is performed on the statements to be executed in the database to generate a query plan tree. The execution plan tree is then pruned and decomposed. The decomposed statements are then classified. If it is a SELECT statement, duplicate validation is performed to determine whether there is duplicate data scanning.

[0125] First, using the same WHERE condition as the root node of the tree, construct the execution phase tree. Traverse the execution phase tree; if a tree node is found to be traversed repeatedly, it is determined to be an inefficient SELECT query SQL statement. At the same time, construct a range query R-tree for WHERE conditions of the same database table; if there is a high degree of overlap in the range, it is also determined to be an inefficient SQL statement. If it is an INSERT statement, based on the database table information, determine if there is a possibility of batch merging with mutual dependencies; if so, it is determined to be an inefficient SQL statement. If it is an INSERT OR UPDATE statement, based on the query range in the parsed statement, find the intersection with the statistical table information in the corresponding database table and system table. If the statistical table information does not overlap with the INSERT OR UPDATE statement to be executed, the statement is an inefficient SQL statement.

[0126] Step 202: If the judgment result is yes, rewrite the first SQL statement according to the preset optimization rules.

[0127] In one embodiment, a preset optimization rule generates a logical view for repeatedly executed data. The logical view simplifies the query statement and optimizes complex nested query statements into simple union statements.

[0128] In one embodiment, if the first SQL statement is a half-join SQL statement, a preset optimization rule forces the connection order to be swapped based on the amount of data on the left and right sides of the half-join; if the amount of data on the left and right sides of the half-join is small, the statements on the left and right sides of the half-join are distributed to the two database instances for execution, and finally the results are joined.

[0129] Figure 3 A flowchart of the SQL statement forwarding method proposed in this disclosure is further shown. Based on Figure 1 The illustrated embodiment further describes step 103, which includes the following steps:

[0130] Step 301: Calculate the first node score for each node based on the hardware status information of each node and the preset hardware status information weights.

[0131] In one embodiment, it is assumed that the hardware status information of each node, including CPU, memory, data disk I / O, and network bandwidth utilization, are as follows: , , , Then, the weights of the preset hardware status information—CPU, memory, data disk I / O, and network bandwidth—are respectively... , , , Then the first node of each node is scored. for:

[0132] (1.1)

[0133] Step 302: In response to at least one first database instance triggering a backup action, obtain the cost incurred when the first database instance performs the backup action;

[0134] In one embodiment, database primary instance triggering backup refers to the operation of backing up data from the database primary instance to the database backup instance. This is mainly used to forward write operations to the primary instance and read operations to the backup instance, so as to reduce the pressure on the primary instance while ensuring the data integrity and security of the database.

[0135] In one embodiment, the cost overhead indicates the hardware overhead of the node corresponding to the first database instance triggering the backup action.

[0136] In one embodiment, the cost of backing up database instance data is a time-varying function, incurring only at the moment of triggering. By summarizing and organizing the backup strategies for database instances on a node, a time-related cost model is obtained. It is assumed that this part is... Assume a node Examples on The overhead of triggering a backup is ,in, The amount of data can be found in MySQL statistics. This represents the CPU overhead incurred during compression during backup. This represents the network overhead incurred during backup data upload. The cost model is then:

[0137] (1.2)

[0138] in, For nodes The number of instances on it.

[0139] in ;

[0140] In one embodiment, the database instance needs to perform data backups at intervals of a certain period, denoted by 't' in the cost model. When 't' is outside the backup time period, the cost incurred by the database instance during backup is 0; when 't' is within the backup time period, normal cost is incurred. .

[0141] Step 303: Calculate the second node score for each node based on the cost of triggering backup behavior when the first database instance is in operation, the first node score for each node, and the time period during which the first database instance performs backup behavior.

[0142] In one embodiment, the second node score for each node is obtained according to equation (1.1) in step 301 and equation (1.2) in step 302. .

[0143] ;

[0144] Figure 4 A flowchart of the SQL statement forwarding method proposed in this disclosure is further shown. Based on Figure 3 The illustrated embodiment further describes step 303, which includes the following steps:

[0145] Step 401: If the time of receiving the SQL statement forwarding request is not within the time period when the first database instance performs backup behavior, then determine the first node score for each node as the second node score.

[0146] In one embodiment, if the time of receiving the SQL statement forwarding request is not within the time period during which the first database instance performs backup behavior, the cost model... At this point, the score of the first node for each node is the score of the second node.

[0147] Step 402: If the time of receiving the SQL statement forwarding request is within the time period when the first database instance performs the backup behavior, then determine the node corresponding to the first database instance that performs the backup behavior in each node.

[0148] Step 403: Based on the cost of triggering the backup behavior of the first database instance and the first node score of each node, calculate the second node score of the node corresponding to the first database instance that performed the backup behavior in each node.

[0149] Step 404: Determine the first node score and the second node score for the database instance that does not perform backup behavior in each node.

[0150] Figure 5 A flowchart of the SQL statement forwarding method proposed in this disclosure is further shown. Based on Figure 1 The illustrated embodiment further describes step 104, which includes the following steps:

[0151] Step 501: Obtain the first execution cost when the database instance forwards the SQL statement;

[0152] In one embodiment, the SQL is input into the data preprocessing engine of the database agent. Based on the statistical information collected previously, the execution of the SQL statement in the database instance is simulated to calculate the query execution cost generated by the database for executing the query statement. The first execution cost is manually marked as... .

[0153] Step 502: Determine if there is a database instance executing the second SQL statement among all nodes;

[0154] In one embodiment, the second SQL statement refers to the SQL statement currently being executed.

[0155] Step 503: If the judgment result is yes, then determine whether the first SQL statement is associated with the second SQL statement;

[0156] In one embodiment, a hash comparison method is used to determine the correlation between the first SQL statement and the second SQL statement.

[0157] In one embodiment, the hash comparison method converts the SQL statement to be forwarded and the SQL statement being executed into fixed-length hash values ​​based on a hash algorithm for comparison. If the hash values ​​generated by the two SQL statements are the same, they can be determined to be consistent.

[0158] Step 504: If the judgment result is yes, then obtain the second execution cost of the database instance that is executing the second SQL statement.

[0159] In one embodiment, the query cost resulting from the interaction between the SQL statement to be forwarded and the statement currently being executed in the instance is defined as the second execution cost, which is manually labeled as... .

[0160] Step 505: Determine the first execution cost as the instance score of the database instance that does not execute the second SQL statement in each database instance; and determine the sum of the first execution cost and the second execution cost as the instance score of the database instance that is executing the second SQL statement in each database instance.

[0161] Figure 6 A flowchart of the SQL statement forwarding method proposed in this disclosure is further shown. Based on Figure 5 The illustrated embodiment further describes step 504, which includes the following steps:

[0162] Step 601: Obtain the mapping relationship table;

[0163] In one embodiment, the mapping table refers to a mapping table between child process IDs and SQL statements maintained by the database proxy device during proxy forwarding. This mapping table is dynamically updated in real time as the lifecycle of the SQL statements forwarded by the proxy changes.

[0164] Step 602: Determine whether there is a forwarding subprocess in the mapping table that corresponds to the lifecycle of the second SQL statement;

[0165] In one embodiment, the proxy service subprocess ID corresponding to the currently executing SQL statement is found according to the mapping table, thereby finding the forwarding subprocess corresponding to the lifecycle of the second SQL statement.

[0166] Step 603: If the judgment result is negative, then determine whether the forwarding subprocess corresponding to the lifecycle of the second SQL statement can be deleted;

[0167] In one embodiment, the database agent determines that the second SQL statement is a residual statement, sends a kill command to the database instance to kill the execution process, and waits for 1 second.

[0168] Step 604: If the judgment result is negative and the second SQL statement is a read-only statement, then the second execution cost of the database instance executing the second SQL statement is the same as the first execution cost.

[0169] In one embodiment, if the currently executing process cannot be killed, the execution of the query on the node where the current instance resides will incur additional execution costs, and if the SQL statement is a read-only statement, then... .

[0170] Step 605: If the judgment result is negative, and the second SQL statement contains a write operation, then the second execution cost of the database instance executing the second SQL statement is the second preset value.

[0171] In one embodiment, if the currently executing process cannot be killed, and the SQL statement is a read-only statement, then the second execution cost of the database instance executing the second SQL statement is... .

[0172] Step 606: If the judgment result is yes, then the second execution cost of the database instance executing the second SQL statement is the first preset value;

[0173] In one embodiment, if the currently executing process can be killed normally, no additional execution cost is incurred, i.e., the second execution cost of the database instance of the second SQL statement. .

[0174] Figure 7 A flowchart of the SQL statement forwarding method proposed in this disclosure is further shown. Based on Figure 5 The illustrated embodiment further describes step 503, which includes the following steps:

[0175] Step 701: If the judgment result is negative, parse the first SQL statement and the second SQL statement, and determine whether there is any overlap between the first SQL statement and the second SQL statement based on the parsing result;

[0176] In one embodiment, the method for parsing the first SQL statement and the second SQL statement includes:

[0177] The first SQL statement is parsed into a query execution plan tree and the query phase is decomposed. This tree structure overlap detection is performed with the query execution technology tree parsed from the second SQL statement to determine whether there are any overlapping parts between the first SQL statement and the second SQL statement.

[0178] Step 702: If the judgment result is negative, the second execution cost of the database instance executing the second SQL statement is the first preset value.

[0179] In one embodiment, if the first SQL statement and the second SQL statement do not have overlapping tree structures, then the database instance of the second SQL statement... .

[0180] Step 703: If the first SQL statement and the second SQL statement have overlapping tree structures, and the overlapping parts are read-only statements, then the second execution cost of the database instance executing the second SQL statement is the same as the first execution cost.

[0181] In one embodiment, if the first SQL statement and the second SQL statement have an overlapping part in their tree structure, and the overlapping part is a read-only statement, then the database instance of the second SQL statement... .

[0182] Step 704: If the judgment result is yes, and the overlapping part contains a write operation, then the second execution cost of the database instance executing the second SQL statement is the second preset value.

[0183] In one embodiment, if the determination result is yes, and the overlapping portion contains a write operation, then the database instance of the second SQL statement... .

[0184] Figure 8 A flowchart of the SQL statement forwarding method proposed in this disclosure is further shown. Based on Figure 1 The illustrated embodiment further describes step 105, which includes the following steps:

[0185] Step 801: Based on the preset node scores, divide all nodes in the node cluster into high-performance nodes and low-performance nodes.

[0186] In one embodiment, based on a preset node score The node is ranked. If the node's ranking is too low, it is removed from the high-performance node set and simultaneously moved to the low-performance node set. If a node is repeatedly added to the low-performance node set, its position in the low-performance node set is further reduced. If the node's ranking is normal and the node is not in the high-performance node set, the node is added to the high-performance node set and simultaneously removed from the low-performance node set.

[0187] Step 802: Based on the instance score of each instance in the high-performance node, determine the instance with the highest instance score as the instance used to forward the first SQL statement.

[0188] In one embodiment, instances in high-performance nodes of the current database cluster are located, based on the instances on each node. The system selects an available instance from the high-performance instances and determines the instance with the highest score to be used for forwarding the first SQL statement.

[0189] Figure 9 A flowchart of the SQL statement forwarding method proposed in this disclosure is further shown. Based on Figure 2 The illustrated embodiment further describes step 201, which includes the following steps:

[0190] Step 901: Parse the first SQL statement and generate a query plan tree;

[0191] In one embodiment, the query plan tree refers to the visual representation of the execution plan generated by the database system when executing a query statement. When parsing the first SQL statement, it is first parsed into a syntax tree or abstract syntax tree so that the system can understand the structure and semantics of the query. Secondly, based on the query statement and database statistics, query optimization is performed to select the optimal execution plan. Finally, the query plan tree is generated based on the optimized execution plan.

[0192] Step 902: Prune and decompose the query plan tree to obtain the decomposed query statement;

[0193] In one embodiment, query plan tree pruning decomposition is an optimization technique that removes invalid or unnecessary operations from the query execution plan in order to reduce the execution time and resource consumption of the obtained query statements.

[0194] Step 903: If the decomposed query statement contains at least one of the following conditions, then the first SQL statement is determined to be an inefficient SQL statement;

[0195] In one embodiment, the query statement contains a select statement, and the select statement contains operations that need to be scanned repeatedly;

[0196] In one embodiment, the repeated scan operation includes at least one of the following:

[0197] Subqueries: If a SELECT statement contains a subquery that requires multiple scans of the same dataset, it will result in duplicate scans. This is because the same dataset needs to be scanned again each time the subquery is executed.

[0198] Nested loop join: When using a nested loop join algorithm, if a table or subquery in the join operation needs to be scanned repeatedly, it will result in repeated scans.

[0199] Grouping or sorting operations: If a query requires grouping or sorting the results, and the fields to be grouped or sorted require repeated scanning of the dataset to complete the operation, it will lead to the problem of repeated scans.

[0200] In one embodiment, the query statement contains an insert statement, and the insert statement contains batches of non-transactional dependencies that can be merged;

[0201] In one embodiment, an insert statement containing a non-transactional dependency batch means that multiple insert statements are interdependent, must be executed in a specific order, and can be merged into a batch insert operation.

[0202] In one embodiment, the query statement contains an insert or update statement, and the query scope indicated by the insert or update statement does not intersect with the target database;

[0203] In one embodiment, the query range indicated by the insert or update statement having no intersection with the target database means that the query range in the parsed statement is compared with the statistical table information in the corresponding database table and system table. If there is no overlap between the statistical table information and the information of the insert or update statement to be executed, then the query range indicated by the insert or update statement has no intersection with the target database.

[0204] To implement the SQL statement forwarding method provided in this disclosure, this disclosure also provides an SQL statement forwarding device, such as... Figure 10 As shown. Figure 10 This is a schematic diagram of the structure of an SQL statement forwarding device provided in an embodiment of the present disclosure. The SQL statement forwarding device 1000 includes:

[0205] Receiving unit 1001 is used to receive an SQL statement forwarding request, which contains the first SQL statement to be forwarded;

[0206] The acquisition unit 1002 is used to acquire the hardware status information of each node in the node cluster; the node cluster contains at least one node, and each of the at least one node runs at least one database instance, which is used to forward the first SQL statement;

[0207] The first computing unit 1003 is used to calculate the node score of each node based on the hardware status information of each node.

[0208] The second calculation unit 1004 is used to calculate the instance score of each database instance in each node based on the association between the first SQL statement and the second SQL statement; the second SQL statement is the SQL statement being executed by the database instance.

[0209] The determination unit 1005 is used to determine the database instance used to forward the first SQL statement based on the node score of each node in the node cluster and the instance score of each database instance in each node.

[0210] In one embodiment, the SQL statement forwarding device 1000 further includes an optimization unit, which is used to:

[0211] Determine if the first SQL statement is an inefficient SQL statement;

[0212] If the judgment result is yes, rewrite the first SQL statement according to the preset optimization rules.

[0213] In one embodiment, the first computing unit 1003 is specifically used for:

[0214] Based on the hardware status information of each node and the preset hardware status information weights, calculate the first node score for each node;

[0215] In one embodiment, the acquisition unit 1002 is further configured to:

[0216] In response to a backup action being triggered by at least one first database instance, the cost of the first database instance performing the backup action is obtained; the cost indicates the hardware overhead of the node corresponding to the first database instance when the backup action is triggered.

[0217] In one embodiment, the first computing unit 1003 is further configured to:

[0218] The second node score for each node is calculated based on the cost of triggering backup behavior on the first database instance, the first node score for each node, and the time period during which the first database instance performs backup behavior.

[0219] In one embodiment, the determining unit 1005 is used to:

[0220] If the time of receiving the SQL statement forwarding request is not within the time period when the first database instance is performing backup behavior, then the first node score for each node is determined to be the second node score;

[0221] If the SQL statement forwarding request is received during the period when the first database instance is performing backup operations, then

[0222] Determine the node corresponding to the first database instance that performs the backup operation in each node;

[0223] In one embodiment, the second computing unit 1004 is specifically used for:

[0224] Based on the cost of triggering backup behavior when the first database instance is used and the first node score of each node, calculate the second node score of the node corresponding to the first database instance that performs backup behavior in each node.

[0225] In one embodiment, the determining unit 1005 is specifically used for:

[0226] For each node, the first node score is assigned to the node corresponding to the database instance that does not perform backup actions, and the second node score is assigned to the node corresponding to that instance.

[0227] In one embodiment, the acquisition unit 1002 is specifically used for:

[0228] Obtain the first execution cost when the database instance forwards an SQL statement;

[0229] Determine if any of the nodes contain a database instance currently executing the second SQL statement;

[0230] If the result is yes, then determine whether the first SQL statement is associated with the second SQL statement;

[0231] If the judgment result is yes, then obtain the second execution cost of the database instance that is currently executing the second SQL statement;

[0232] In one embodiment, the determining unit 1005 is specifically used for:

[0233] The first execution cost is determined as the instance score of the database instance that does not execute the second SQL statement in each database instance; and the sum of the first execution cost and the second execution cost is determined as the instance score of the database instance that is executing the second SQL statement in each database instance.

[0234] In one embodiment, the acquisition unit 1002 is specifically used for:

[0235] Retrieve the mapping table; the mapping table is used to indicate the mapping relationship between the forwarding subprocess that executes the SQL statement and the lifecycle of the corresponding SQL statement;

[0236] Determine if a forwarding subprocess exists in the mapping table corresponding to the lifecycle of the second SQL statement; (the forwarding subprocess is the proxy service subprocess).

[0237] If the result is negative, then determine whether the forwarding subprocess corresponding to the lifecycle of the second SQL statement can be deleted.

[0238] If the judgment result is negative, and the second SQL statement is a read-only statement, then the second execution cost of the database instance executing the second SQL statement is the same as the first execution cost.

[0239] If the judgment result is negative, and the second SQL statement contains a write operation, then the second execution cost of the database instance executing the second SQL statement is the second preset value.

[0240] If the judgment result is yes, then the second execution cost of the database instance executing the second SQL statement is the first preset value.

[0241] In one embodiment, the second computing unit 1004 is specifically used for:

[0242] Determine if the first SQL statement is related to the second SQL statement.

[0243] If the result is negative, parse the first SQL statement and the second SQL statement, and determine whether there is any overlap between the first SQL statement and the second SQL statement based on the parsing result.

[0244] If the judgment result is negative, the second execution cost of the database instance executing the second SQL statement is the first preset value;

[0245] If the judgment result is yes, and the overlapping part is a read-only statement, then the second execution cost of the database instance executing the second SQL statement is the same as the first execution cost;

[0246] If the judgment result is yes, and the overlapping part contains a write operation, then the second execution cost of the database instance executing the second SQL statement is the second preset value.

[0247] In one embodiment, the determining unit 1005 is specifically used for:

[0248] Based on preset node scores, all nodes in the node cluster are divided into high-performance nodes and low-performance nodes.

[0249] Based on the instance score of each instance in the high-performance node, the instance with the highest instance score is determined as the instance used to forward the first SQL statement.

[0250] In one embodiment, the SQL statement forwarding device 1000 further includes a parsing unit, which is used for:

[0251] Parse the first SQL statement and generate a query plan tree;

[0252] Prune and decompose the query plan tree to obtain the decomposed query statements;

[0253] If the decomposed query statement contains at least one of the following conditions, the first SQL statement is determined to be an inefficient SQL statement;

[0254] The query statement contains a SELECT statement, and the SELECT statement contains operations that require repeated scanning;

[0255] The query statement contains an insert statement, and the insert statement contains batches of non-transactional dependencies that can be merged;

[0256] The query statement contains INSERT or UPDATE statements, and the query scope indicated by the INSERT or UPDATE statements does not overlap with the target database; the target database refers to the database corresponding to the first SQL statement.

[0257] It should be noted that the SQL statement forwarding device provided in the above embodiments is only illustrated by the division of the above program modules when performing SQL statement forwarding. In actual applications, the above processing can be assigned to different program modules as needed, that is, the internal structure of the SQL statement forwarding device can be divided into different program modules to complete all or part of the processing described above. In addition, the SQL statement forwarding device provided in the above embodiments and the SQL statement forwarding method embodiments provided in this disclosure belong to the same concept, and the specific implementation process can be found in the method embodiments, which will not be repeated here.

[0258] Figure 11 This is a schematic diagram of the hardware composition structure of the electronic device provided in the embodiments of this disclosure, such as... Figure 11 As shown, the electronic device 1100 includes at least one processor 1102; and a memory 1101 communicatively connected to the at least one processor 1102; wherein the memory 1101 stores instructions that can be executed by the at least one processor 1102, and the instructions are executed by the at least one processor 1102 to implement the steps of the SQL statement forwarding method of the present disclosure embodiments.

[0259] Optionally, the electronic device may specifically be the SQL statement forwarding device of the present application embodiment, and the electronic device may implement the corresponding processes implemented by the SQL statement forwarding device in the various methods of the present application embodiment. For the sake of brevity, it will not be described in detail here.

[0260] It is understood that the electronic device also includes a communication interface 1103. Various components in the electronic device are coupled together via a bus system 1104. It is understood that the bus system 1104 is used to implement communication between these components. In addition to a data bus, the bus system 1104 also includes a power bus, a control bus, and a status signal bus. However, for clarity, in... Figure 11 The general designated all buses as Bus System 1104.

[0261] It is understood that memory 1101 can be volatile memory or non-volatile memory, or both. Specifically, non-volatile memory can be read-only memory (ROM) or programmable read-only memory (PROM). Erasable Programmable Read-Only Memory (EPROM) Electrically Erasable Programmable Read-Only Memory (EEPROM) Only memory), magnetic random access memory (FRAM), flash memory, magnetic surface memory, optical disc, or read-only optical disc (CD). ROM, Compact Disc Read Only memory); magnetic surface memory can be disk storage or tape storage. Volatile memory can be random access memory (RAM), which is used as an external cache. By way of example, but not limitation, many forms of RAM are available, such as static random access memory (SRAM), synchronous static random access memory (SSRAM), dynamic random access memory (DRAM), synchronous dynamic random access memory (SDRAM), double data rate synchronous dynamic random access memory (DDRSDRAM), enhanced synchronous dynamic random access memory (ESDRAM), synclink dynamic random access memory (SLDRAM), and direct memory bus random access memory (DRRAM). The memory 1101 described in the embodiments of the present invention is intended to include, but is not limited to, these and any other suitable types of memory.

[0262] The methods disclosed in the above embodiments can be applied to or implemented by processor 1102. Processor 1102 may be an integrated circuit chip with signal processing capabilities. During implementation, each step of the above methods can be completed by integrated logic circuits in the hardware of processor 1102 or by instructions in software form. Processor 1102 may be a general-purpose processor, DSP, or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. Processor 1102 can implement or execute the methods, steps, and logic block diagrams disclosed in the embodiments of this invention. A general-purpose processor may be a microprocessor or any conventional processor, etc. The steps of the methods disclosed in the embodiments of this invention can be directly manifested as execution by a hardware decoding processor, or execution by a combination of hardware and software modules in the decoding processor. The software modules may be located in a storage medium located in memory 1101. Processor 1102 reads information from memory 1101 and, in conjunction with its hardware, completes the steps of the aforementioned methods.

[0263] In an exemplary embodiment, the electronic device may be implemented by one or more application-specific integrated circuits (ASICs), DSPs, programmable logic devices (PLDs), complex programmable logic devices (CPLDs), FPGAs, general-purpose processors, controllers, MCUs, microprocessors, or other electronic components to perform the aforementioned method.

[0264] This disclosure also provides a non-transitory computer-readable storage medium storing computer instructions, which are used to cause a computer to execute the steps of the SQL statement forwarding method of the present invention.

[0265] Optionally, the computer-readable storage medium can be applied to the SQL statement forwarding device in the embodiments of this application, and the computer instructions cause the computer to execute the corresponding processes implemented by the SQL statement forwarding device in the various methods of the embodiments of this application. For the sake of brevity, these will not be described in detail here.

[0266] In the several embodiments provided in this application, it should be understood that the disclosed devices and methods can be implemented in other ways. The device embodiments described above are merely illustrative. For example, the division of units is only a logical functional division, and in actual implementation, there may be other division methods, such as: multiple units or components can be combined, or integrated into another system, or some features can be ignored or not executed. In addition, the coupling, direct coupling, or communication connection between the various components shown or discussed can be through some interfaces, and the indirect coupling or communication connection between devices or units can be electrical, mechanical, or other forms.

[0267] The units described above as separate components may or may not be physically separate. The components shown as units may or may not be physical units, that is, they may be located in one place or distributed across multiple network units. Some or all of the units may be selected to achieve the purpose of this embodiment according to actual needs.

[0268] In addition, in the various embodiments of the present invention, each functional unit can be integrated into one processing unit, or each unit can be a separate unit, or two or more units can be integrated into one unit; the integrated unit can be implemented in hardware or in the form of hardware plus software functional units.

[0269] Those skilled in the art will understand that all or part of the steps of the above method embodiments can be implemented by hardware related to program instructions. The aforementioned program can be stored in a computer-readable storage medium. When the program is executed, it performs the steps of the above method embodiments. The aforementioned storage medium includes various media that can store program code, such as mobile storage devices, ROM, RAM, magnetic disks, or optical disks.

[0270] Alternatively, if the integrated units of this invention are implemented as software functional modules and sold or used as independent products, they can also be stored in a computer-readable storage medium. Based on this understanding, the technical solutions of the embodiments of this invention, or the parts that contribute to the prior art, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the methods of the various embodiments of this invention. The aforementioned storage medium includes various media capable of storing program code, such as mobile storage devices, ROM, RAM, magnetic disks, or optical disks.

[0271] The above description is merely a specific embodiment of the present invention, but the scope of protection of the present invention is not limited thereto. Any variations or substitutions that can be easily conceived by those skilled in the art within the technical scope disclosed in the present invention should be included within the scope of protection of the present invention. Therefore, the scope of protection of the present invention should be determined by the scope of the claims.

Claims

1. A method of forwarding an SQL statement, characterized by, include: Receive an SQL statement forwarding request, wherein the SQL statement forwarding request contains a first SQL statement to be forwarded; Obtain the hardware status information of each node in the node cluster; The node cluster contains at least one node, and each of the at least one node runs at least one database instance, which is used to forward the first SQL statement; Based on the hardware status information of each node, calculate the node score for each node; Based on the relationship between the first SQL statement and the second SQL statement, calculate the instance score for each database instance in each node; The second SQL statement is the SQL statement that the database instance is currently executing; Based on the node score of each node in the node cluster and the instance score of each database instance in each node, the database instance used to forward the first SQL statement is determined. The calculation of the instance score for each database instance in each node based on the association between the first SQL statement and the second SQL statement includes: Obtain the first execution cost when the database instance forwards an SQL statement; Determine if any of the nodes contain a database instance currently executing the second SQL statement; If the result is yes, then determine whether the first SQL statement is associated with the second SQL statement; If the result is yes, then obtain the second execution cost of the database instance that is currently executing the second SQL statement; If there is no database instance executing the second SQL statement in any of the nodes, then the first execution cost is determined to be the instance score of the database instance that is not executing the second SQL statement in each of the database instances; and if there is a database instance executing the second SQL statement in any of the nodes, then the sum of the first execution cost and the second execution cost is determined to be the instance score of the database instance that is executing the second SQL statement in each of the database instances. The step of determining the database instance used to forward the first SQL statement based on the node score of each node in the node cluster and the instance score of each database instance in each node includes: Based on preset node scores, all nodes in the node cluster are divided into high-performance nodes and low-performance nodes. Based on the instance score of each database instance in the high-performance node, the instance with the highest instance score is determined as the database instance used to forward the first SQL statement.

2. The method of claim 1, wherein, The method further includes: Determine whether the first SQL statement is an inefficient SQL statement; If the judgment result is yes, the first SQL statement is rewritten according to the preset optimization rules.

3. The method of claim 1, wherein, The calculation of a node score for each node based on its hardware status information includes: Based on the hardware status information of each node and the preset hardware status information weights, the first node score of each node is calculated; In response to at least one first database instance triggering a backup action, the cost incurred when the first database instance performs the backup action is obtained; the cost incurred indicates the hardware overhead of the node corresponding to the first database instance triggering the backup action. The second node score of each node is calculated based on the cost of triggering backup behavior when the first database instance is executed, the first node score of each node, and the time period during which the first database instance performs backup behavior.

4. The method of claim 3, wherein, The calculation of the second node score for each node based on the cost of triggering the backup action on the first database instance, the first node score for each node, and the time period during which the first database instance performs the backup action includes: If the time of receiving the SQL statement forwarding request is not within the time period when the first database instance performs backup behavior, then the first node score for each node is determined to be the second node score; If the time of receiving the SQL statement forwarding request falls within the time period during which the first database instance performs backup operations, then Determine the node corresponding to the first database instance that performs the backup action in each of the nodes; Based on the cost of triggering backup behavior when the first database instance triggers backup behavior and the first node score of each node, calculate the second node score of the node corresponding to the first database instance that performs backup behavior in each node; For each node, the first node score is assigned to the node corresponding to the database instance that does not perform backup actions, and the second node score is assigned to the node corresponding to that instance.

5. The method of claim 1, wherein, The second execution cost of obtaining the database instance executing the second SQL statement includes: Obtain the mapping table; the mapping table is used to indicate the mapping relationship between the lifecycle of the forwarding subprocess that executes the SQL statement and the corresponding SQL statement. Determine whether a forwarding subprocess exists in the mapping table that corresponds to the lifecycle of the second SQL statement; If the result is negative, then determine whether the forwarding subprocess corresponding to the lifecycle of the second SQL statement can be deleted; If the judgment result is negative, and the second SQL statement is a read-only statement, then the second execution cost of the database instance executing the second SQL statement is the same as the first execution cost. If the judgment result is negative, and the second SQL statement contains a write operation, then the second execution cost of the database instance executing the second SQL statement is the second preset value; If the forwarding subprocess corresponding to the lifecycle of the second SQL statement can be deleted, then the second execution cost of the database instance executing the second SQL statement is the first preset value.

6. The method of claim 1, wherein, After determining whether the first SQL statement is associated with the second SQL statement, the method further includes: If the judgment result is negative, parse the first SQL statement and the second SQL statement, and determine whether there is any overlap between the first SQL statement and the second SQL statement based on the parsing result; If the judgment result is negative, the second execution cost of the database instance executing the second SQL statement is the first preset value; If the determination result is yes, and the overlapping part is a read-only statement, then the second execution cost of the database instance executing the second SQL statement is the same as the first execution cost; If the determination result is yes, and the overlapping part includes a write operation, then the second execution cost of the database instance executing the second SQL statement is the second preset value.

7. The method of claim 2, wherein, The determination of whether the first SQL statement is an inefficient SQL statement includes: Parse the first SQL statement and generate a query plan tree; The query plan tree is pruned and decomposed to obtain the decomposed query statements; If the decomposed query statement contains at least one of the following conditions, then the first SQL statement is determined to be an inefficient SQL statement; The query statement contains a select statement, and the select statement contains operations that require repeated scanning; The query statement contains an insert statement, and the insert statement contains non-transactional dependent batches that can be merged; The query statement contains an insert or update statement, and the query scope indicated by the insert or update statement does not intersect with the target database; the target database refers to the database corresponding to the first SQL statement.

8. An SQL statement forwarding apparatus characterized by comprising: include: A receiving unit is used to receive an SQL statement forwarding request, wherein the SQL statement forwarding request contains a first SQL statement to be forwarded; The acquisition unit is used to acquire the hardware status information of each node in the node cluster; The node cluster contains at least one node, and each of the at least one node runs at least one database instance, which is used to forward the first SQL statement; The first calculation unit is used to calculate the node score of each node based on the hardware status information of each node; The second calculation unit is used to calculate the instance score of each database instance in each node based on the association relationship between the first SQL statement and the second SQL statement; The second SQL statement is the SQL statement that the database instance is currently executing; The determining unit is used to determine the database instance used to forward the first SQL statement based on the node score of each node in the node cluster and the instance score of each database instance in each node. The second calculation unit is further configured to obtain the first execution cost when the database instance forwards the SQL statement; Determine if any of the nodes contain a database instance currently executing the second SQL statement; If the result is yes, then determine whether the first SQL statement is associated with the second SQL statement; If the result is yes, then obtain the second execution cost of the database instance that is currently executing the second SQL statement; If there is no database instance executing the second SQL statement in any of the nodes, then the first execution cost is determined to be the instance score of the database instance that is not executing the second SQL statement in each of the database instances; and if there is a database instance executing the second SQL statement in any of the nodes, then the sum of the first execution cost and the second execution cost is determined to be the instance score of the database instance that is executing the second SQL statement in each of the database instances. The determining unit is further configured to divide all nodes in the node cluster into high-performance nodes and low-performance nodes based on a preset node score. Based on the instance score of each database instance in the high-performance node, the instance with the highest instance score is determined as the database instance used to forward the first SQL statement.

9. An electronic device, characterized in that, include: At least one processor; as well as A memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed by the at least one processor to enable the at least one processor to perform the method of any one of claims 1 to 7.

10. A non-transitory computer-readable storage medium storing computer instructions, characterized in that, The computer instructions are used to cause the computer to perform the method according to any one of claims 1 to 7.

Images