Routing query method and device, equipment, storage medium and program product

Abstract

The embodiment of the invention provides a query routing method and device, equipment, a storage medium and a program product, and relates to the field of database management. The method comprises the following steps: determining a classification label of a first SQL statement according to execution time information of a first structured query language SQL, wherein the classification label comprises a blocked SQL, a high-frequency SQL or a common SQL; determining a first routing strategy corresponding to the first SQL statement according to the classification label and node state information, wherein the node state information is used for indicating SQL statement response time of each data node in the data node cluster; and determining a corresponding target data node according to the first routing strategy, and routing the first SQL statement to the target data node. According to the method provided by the invention, the load balancing capacity for query request routing allocation is improved.

Description

Technical Field

[0001] This application relates to the field of database management, and more particularly to a query routing method, apparatus, device, storage medium, and program product. Background Technology

[0002] In a distributed database architecture, as business scale expands and concurrent user access increases, the load balancing strategy of the database cluster becomes a crucial element in ensuring system performance and stability.

[0003] In a read-write split database cluster (such as a master-slave replication database cluster), the client needs to dynamically allocate requests to the corresponding data nodes based on the type of structured query language (SQL) to avoid write operations blocking read operations or resource contention.

[0004] However, the above implementation method has poor load balancing capability for routing and allocating query requests, making it difficult to balance multiple data nodes, resulting in uneven resource allocation, which in turn leads to problems such as node overload, response delay, or even service unavailability. Summary of the Invention

[0005] This application provides a query routing method, apparatus, device, storage medium, and program product to solve the technical problem of poor load balancing capability in route allocation.

[0006] In a first aspect, this application provides a query routing method, comprising: determining a classification label of a first SQL statement based on the execution time information of a first structured query language (SQL), wherein the classification label includes blocking SQL, high-frequency SQL, or ordinary SQL;

[0007] The first routing strategy corresponding to the first SQL statement is determined based on the classification label and node status information. The node status information is used to indicate the SQL statement response time of each data node in the data node cluster.

[0008] The first routing strategy determines the corresponding target data node, and the first SQL statement is routed to the target data node.

[0009] In one possible implementation of the first aspect, the classification label of the first SQL statement is determined based on the execution time information of the first SQL statement, including:

[0010] If the execution time of the first SQL statement exceeds the execution time threshold, the first SQL statement is classified as a blocking SQL statement.

[0011] If the execution time of the first SQL statement is less than or equal to the execution time threshold and the execution frequency is greater than the execution frequency threshold, the first SQL statement is classified as a high-frequency SQL statement.

[0012] If the execution time of the first SQL statement is less than or equal to the execution time threshold and the execution frequency is less than or equal to the execution frequency threshold, the first SQL statement is classified as ordinary SQL.

[0013] In one possible implementation of the first aspect, determining the first routing strategy corresponding to the first SQL statement based on the category label and node status information includes:

[0014] If the first SQL statement is categorized as "blocking SQL", then the corresponding first routing strategy is to route the first SQL statement to a blocking data node, which is a data node used to process blocking SQL statements; or,

[0015] If the first SQL statement is categorized as "high-frequency SQL," then the corresponding first routing strategy is to route the first SQL statement according to the dynamic routing configuration. The dynamic routing configuration indicates the weight of each data node for the first SQL statement, determined based on the response time of each data node; or...

[0016] If the category label of the first SQL statement is "normal SQL", then the corresponding first routing strategy is to route the first SQL statement according to the preset static weight configuration.

[0017] In one possible implementation of the first aspect, the classification label is a blocking SQL statement, and the first routing strategy corresponding to the first SQL statement is determined based on the classification label and node status information, including:

[0018] The routing policy corresponding to the blocking SQL statement is determined to be the first routing policy; or,

[0019] If the previous category label of the first SQL statement is a high-frequency SQL statement, delete the routing policy corresponding to the high-frequency SQL statement and determine the routing policy corresponding to the blocking SQL statement as the first routing policy.

[0020] In one possible implementation of the first aspect, the classification label is a high-frequency SQL statement, and the first routing strategy corresponding to the first SQL statement is determined based on the classification label and node status information, including:

[0021] If the previous category label for the first SQL statement is "blocking SQL", adjust the blocking routing strategy corresponding to the first SQL statement according to the routing strategy corresponding to high-frequency SQL; or,

[0022] If the previous category label for the first SQL statement is "normal SQL", adjust the normal routing strategy corresponding to the first SQL statement according to the routing strategy corresponding to high-frequency SQL; or,

[0023] The routing strategy corresponding to the high-frequency SQL queries is determined as the first routing strategy.

[0024] In one possible implementation of the first aspect, the classification label is a high-frequency SQL statement, and the first routing strategy corresponding to the first SQL statement is determined based on the classification label and node status information, including:

[0025] The first routing strategy corresponding to the first SQL statement is determined based on the node status information, which includes the node response time.

[0026] Among them, the data node with the shorter the response time has the higher priority, and the data node with the longer the response time has the lower priority.

[0027] In one possible implementation of the first aspect, the method further includes:

[0028] After the first SQL statement is executed, the first execution information corresponding to the first SQL statement is obtained and stored in the first-in-first-out (FIFO) storage space.

[0029] In one possible implementation of the first aspect, the method further includes:

[0030] Obtain node status information of the data node cluster. The node status information includes at least one of the following: average response time, longest response time, and shortest response time of SQL statements for each data node in the data node cluster.

[0031] Secondly, this application provides a query routing apparatus, comprising:

[0032] The first determining module is used to determine the classification label of the first SQL statement based on the execution time information of the first structured query language SQL. The classification label includes blocking SQL, high-frequency SQL, or ordinary SQL.

[0033] The second determining module is used to determine the first routing strategy corresponding to the first SQL statement based on the classification label and node status information. The node status information is used to indicate the status information of each data node in the data node cluster, and the first routing strategy is used to indicate the weight of each data node for the first SQL statement.

[0034] The routing module is used to determine the corresponding target data node according to the first routing strategy and route the first SQL statement to the target data node.

[0035] Thirdly, this application provides an electronic device, comprising: a processor; a memory communicatively connected to the processor; the memory storing computer-executable instructions; and the processor executing the computer-executable instructions stored in the memory to implement the method of any one of the first aspects.

[0036] Fourthly, this application provides a computer-readable storage medium storing computer-executable instructions, which, when executed by a processor, are used to implement the method of any one of the first aspects.

[0037] Fifthly, this application provides a computer program product, including a computer program that, when executed by a processor, implements the method of any one of the first aspects.

[0038] The query routing method, apparatus, device, storage medium, and product provided in this application improve the load balancing capability for query request routing and allocation by classifying SQL and adjusting routing strategies. This enhances the load balancing capability of the database cluster, dynamically allocating resources to matching nodes based on the resource consumption characteristics of different SQL queries (such as memory-intensive and CPU-intensive), thus avoiding resource waste. Furthermore, by identifying and centrally executing blocking SQL queries, it prevents them from affecting the execution efficiency of other SQL queries, reducing the risk of cascading performance degradation. Based on real-time execution time statistics and FIFO buffer analysis, it dynamically adjusts node weights, ensuring that load allocation always adapts to changes in node status. By designing load strategies on a SQL-by-SQL basis, it overcomes the limitations of traditional "node-by-node" approaches, improving service stability in high-concurrency scenarios. It supports the combined application of multiple load strategies, adapting to different business scenario requirements. Attached Figure Description

[0039] The accompanying drawings, which are incorporated in and form part of this specification, illustrate embodiments consistent with this application and, together with the description, serve to explain the principles of this application.

[0040] Figure 1 A schematic diagram illustrating a query routing method provided in an embodiment of this application;

[0041] Figure 2 A flowchart illustrating a query routing method provided in an embodiment of this application;

[0042] Figure 3 A flowchart illustrating another query routing method provided in an embodiment of this application;

[0043] Figure 4 A flowchart illustrating another query routing method provided in an embodiment of this application;

[0044] Figure 5 This is a schematic diagram of the structure of a query routing device provided in an embodiment of this application;

[0045] Figure 6 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application.

[0046] The accompanying drawings have illustrated specific embodiments of this application, which will be described in more detail below. These drawings and descriptions are not intended to limit the scope of the concept in any way, but rather to illustrate the concept of this application to those skilled in the art through reference to specific embodiments. Detailed Implementation

[0047] Exemplary embodiments will now be described in detail, examples of which are illustrated in the accompanying drawings. When the following description relates to the drawings, unless otherwise indicated, the same numbers in different drawings denote the same or similar elements. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with this application. Rather, they are merely examples of apparatuses and methods consistent with some aspects of this application as detailed in the appended claims.

[0048] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, data stored, data displayed, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties. Furthermore, the collection, storage, use, processing, transmission, provision, disclosure, and application of the relevant data all comply with the relevant laws, regulations, and standards of the relevant countries and regions, have taken necessary confidentiality measures, do not violate public order and good morals, and provide corresponding operation access points for users to choose to authorize or refuse.

[0049] Figure 1 This is a schematic diagram illustrating a scenario where the query routing method of this application is applied. The database node cluster may include multiple data nodes, such as... Figure 1 As shown, the database node cluster can include read-write node 1, read node 1, and read node 2. The database middleware can route the received SQL to the data nodes in the database node cluster.

[0050] In a distributed database architecture, as business scale expands and concurrent user access increases, the load balancing strategy of the database cluster becomes a crucial element in ensuring system performance and stability.

[0051] In a read-write split database cluster (such as a master-slave replication database cluster), the client needs to dynamically allocate requests to the corresponding data nodes based on the type of structured query language (SQL) to avoid write operations blocking read operations or resource contention.

[0052] However, the above implementation method has poor load balancing capability for routing and allocating query requests, making it difficult to balance the load of multiple data nodes, resulting in uneven resource allocation, which in turn leads to problems such as node overload, response delay, or even service unavailability.

[0053] The query routing method, apparatus, device, storage medium, and program products provided in this application are intended to solve the above-mentioned technical problems of the prior art.

[0054] The technical solution of this application and how the technical solution of this application solves the above-mentioned technical problems are described in detail below with specific embodiments. These specific embodiments can be combined with each other, and the same or similar concepts or processes may not be described again in some embodiments. The embodiments of this application will be described below with reference to the accompanying drawings.

[0055] Figure 2 This is a flowchart illustrating a query routing method provided in an embodiment of this application, as shown below. Figure 2 As shown, the method includes:

[0056] S201. The electronic device determines the classification label of the first SQL statement based on the execution time information of the first SQL statement. The classification label includes blocking SQL, high-frequency SQL, or normal SQL.

[0057] Specifically, the electronic device can determine the classification label of the first SQL statement based on the execution time information of the first SQL statement according to the preset classification rules. The classification rules can be a preset set of rules for classifying SQL types, such as classification logic based on indicators such as execution time and frequency.

[0058] For example, electronic devices can mark SQL queries that take more than 3 seconds to execute as blocking SQL queries, and SQL queries that are executed more than 60 times within 180 seconds as high-frequency SQL queries.

[0059] S202. The electronic device determines the first routing strategy corresponding to the first SQL statement based on the classification label and node status information. The node status information is used to indicate the status information of each data node in the data node cluster, and the first routing strategy is used to indicate the strategy for routing the first SQL statement.

[0060] The node status information can be the response time of the data node to SQL statements. For example, it can include the response time to the first SQL statement, and it can also include the response time to other SQL statements.

[0061] The data nodes in this application embodiment can be database instances participating in load balancing, including read-write nodes, read-only nodes, and blocking SQL execution nodes. The blocking SQL execution nodes are used to execute blocking SQL and are isolated from other nodes.

[0062] S203. The electronic device determines the corresponding target data node according to the first routing strategy.

[0063] After determining the first routing strategy corresponding to the first SQL statement, the electronic device can determine the corresponding target data node based on the first routing strategy. For example, the data node with the largest weight value can be determined as the target data node, or the weights of each data node can be converted into corresponding selection probabilities, and then the target data node can be determined based on the selection probability of each data node.

[0064] S204. The electronic device routes the first SQL statement to the target data node.

[0065] After the target data node is determined, the electronic device routes the first SQL statement to the target data node, which then processes the first SQL statement.

[0066] This possible implementation addresses the problem of existing load balancing strategies' inability to dynamically adapt to SQL resource consumption characteristics and node status by using preset classification rules, dynamically adjusting weight configurations, and employing differentiated allocation strategies. First, preset classification rules clarify the resource consumption characteristics of SQL queries through metrics such as execution time and frequency, ensuring the accuracy of classification labels. Second, dynamically adjusting weight configurations, combined with classification labels and node status information (such as response time), ensures that weight configurations reflect the cluster status in real time. Finally, the differentiated allocation strategy distributes SQL requests to target nodes based on the weight configuration, achieving a match between resource allocation and node capabilities. This improves the throughput and stability of the database cluster and achieves more refined load balancing.

[0067] In some embodiments, such as Figure 3 As shown, when processing the first SQL statement, the electronic device can first determine whether it is a write SQL statement or a read SQL statement. If it is a write SQL statement, the SQL routing is performed according to the pre-configured node weights. If it is a read SQL statement, it determines whether there is a separate routing strategy, i.e., a routing strategy determined according to the category label and node status information. If it exists, the SQL routing is performed according to that separate routing strategy; if it does not exist, the SQL routing is performed according to the default routing strategy, such as the static weight strategy.

[0068] In some embodiments, data nodes in a data node cluster can be divided into read-write nodes, read-only nodes, and blocking SQL execution nodes. Read-write nodes can be used to process read SQL and write SQL, read-only nodes can only be used to process read SQL, and blocking SQL execution nodes are used to execute blocking SQL.

[0069] In some embodiments, the electronic device determines the classification label of the first SQL statement based on the execution time information of the first SQL statement, including:

[0070] If the execution time of the first SQL statement exceeds the execution time threshold, the first SQL statement is classified as a blocking SQL statement.

[0071] If the execution time of the first SQL statement is less than or equal to the execution time threshold and the execution frequency is greater than the execution frequency threshold, the first SQL statement is classified as a high-frequency SQL statement.

[0072] If the execution time of the first SQL statement is less than or equal to the execution time threshold and the execution frequency is less than or equal to the execution frequency threshold, the first SQL statement is classified as ordinary SQL.

[0073] The execution time is the time it takes for the SQL statement to complete execution on the storage node. For example, the SQL statement `SELECT * FROM products` takes 200ms to execute on node A.

[0074] Execution frequency is the number of times an SQL statement is executed per unit of time. For example, the SQL statement `SELECT * FROM ordersWHERE user_id = ?` will be executed 60 times in 180 seconds.

[0075] The electronic device generates classification labels based on the execution time and frequency of SQL. The process of generating classification labels is as follows: First, the execution time of SQL at each node is collected. If the execution time exceeds a preset threshold (such as 3 seconds), it is marked as a blocked SQL label. Second, the execution frequency of SQL within a unit of time is counted. If the execution frequency exceeds a preset threshold (such as 60 times within 180 seconds), it is marked as a high-frequency SQL label. The rest of the SQL is marked as ordinary SQL labels.

[0076] For example, if the execution time of the first SQL statement is 4 seconds, which is greater than the execution time threshold of 3 seconds, the electronic device can determine that the first SQL statement is classified as blocking SQL. That is, regardless of whether the execution frequency is greater than the execution frequency threshold, if the execution time is greater than the execution time threshold, the first SQL statement can be classified as blocking SQL.

[0077] If the execution time of the first SQL statement is less than or equal to the execution time threshold of 3 seconds, and the execution frequency is 30 times within 60 seconds or greater than the execution frequency threshold of 20 times within 60 seconds, the electronic device can determine that the classification label of the first SQL statement is high-frequency SQL.

[0078] If the execution time of the first SQL statement is less than or equal to the execution time threshold of 3 seconds, and the execution frequency is 10 times within 60 seconds or less than or equal to the execution frequency threshold of 20 times within 60 seconds, the electronic device can determine that the classification label of the first SQL statement is ordinary SQL.

[0079] In some embodiments, the aforementioned execution time threshold and execution frequency threshold can be pre-set fixed values, or they can be dynamically determined based on relevant information of the database node cluster or relevant information of the SQL. For example, the corresponding threshold can be determined based on the historical execution time and historical execution frequency of the SQL. For instance, if the historical average execution time of the first SQL statement is 5 seconds, the corresponding execution time threshold can be determined to be 7 seconds. Alternatively, the corresponding execution time threshold and execution frequency threshold can be determined based on the type of SQL; they can also be determined based on the resource utilization rate of the database node cluster. For example, if the memory utilization rate is less than or equal to 80%, the execution time threshold is determined to be 8 seconds; if the memory utilization rate is greater than 80%, the execution time threshold is reduced to 5 seconds. Other methods can also be used to determine these thresholds, which are not limited here.

[0080] In addition, electronic devices can also determine the category label of the first SQL statement in other ways, which are not limited here.

[0081] This possible implementation achieves more accurate SQL classification by jointly determining execution time and frequency. This improves the accuracy of classification labels, provides more reliable input data for dynamic weight adjustment, and further optimizes the efficiency and stability of load balancing.

[0082] In some embodiments, the electronic device determines the first routing strategy corresponding to the first SQL statement based on the classification label and node status information, including:

[0083] If the first SQL statement is categorized as "blocking SQL," then the corresponding first routing strategy is to route the first SQL statement to one of the blocking data nodes, where the blocking data node is the data node used to process blocking SQL statements; or,

[0084] If the first SQL statement is categorized as a high-frequency SQL statement, then the corresponding first routing strategy is to route the first SQL statement based on dynamic routing configuration. Dynamic routing configuration indicates the weight of each data node for the first SQL statement, determined based on the response time of each data node; or...

[0085] If the first SQL statement is classified as "normal SQL", then the corresponding first routing strategy is to route the first SQL statement according to the preset static weight configuration.

[0086] Static weight configuration refers to a routing strategy based on preset weights and fixed allocation rules that does not change with real-time data. For example, ordinary SQL queries are allocated to nodes according to preset weights (such as R1=3, R2=3).

[0087] This possible implementation achieves targeted resource adaptation for different SQL types through the generation of differentiated weight strategies. For example, isolating blocking SQL prevents it from interfering with other SQL, dynamically adjusting the weights of high-frequency SQL optimizes resource utilization, and statically allocating low-frequency ordinary SQL reduces unnecessary statistical overhead. This improves the granularity of load balancing and ensures service stability in high-concurrency scenarios.

[0088] In some embodiments, such as Figure 4 As shown, the first SQL statement is categorized as "blocking SQL". The electronic device determines the first routing strategy corresponding to the first SQL statement based on the categorization label and node status information, which may specifically include:

[0089] The routing policy corresponding to the blocking SQL statement is determined to be the first routing policy; or,

[0090] If the previous category label of the first SQL statement is a high-frequency SQL statement, delete the routing policy corresponding to the high-frequency SQL statement and determine the routing policy corresponding to the blocking SQL statement as the first routing policy.

[0091] In other words, if a routing policy already exists for the first SQL statement, and the first SQL statement is categorized as a blocking SQL, the electronic device can delete the routing policy corresponding to the high-frequency SQL statement and determine the routing policy corresponding to the blocking SQL statement as the routing policy corresponding to the first SQL statement.

[0092] It is understandable that in some embodiments, if the classification label of the first SQL statement is blocking SQL, then the SQL needs to be routed to the blocking data node used to process the blocking SQL. Therefore, it is necessary to delete the routing policy corresponding to the high-frequency SQL and determine the routing policy corresponding to the blocking SQL as the routing policy corresponding to the first SQL statement.

[0093] In some embodiments, such as Figure 4 As shown, the first SQL statement is categorized as a high-frequency SQL statement. The electronic device determines the first routing strategy corresponding to the first SQL statement based on the categorization label and node status information, which may specifically include:

[0094] If the previous category label for the first SQL statement is "blocking SQL," the blocking routing strategy corresponding to the first SQL statement is adjusted according to the routing strategy for high-frequency SQL statements. The blocking routing strategy indicates the weight of each blocking data node for the first SQL statement, and the blocking data node is the data node used to process blocking SQL statements; or,

[0095] If the previous category label for the first SQL statement is "normal SQL", adjust the normal routing strategy corresponding to the first SQL statement according to the routing strategy corresponding to high-frequency SQL; or,

[0096] The routing strategy corresponding to the high-frequency SQL queries is determined as the first routing strategy.

[0097] If the category label of the first SQL statement is high-frequency SQL, and the first SQL statement does not have a separate routing strategy, then the routing strategy corresponding to the high-frequency SQL statement is determined to be the first routing strategy corresponding to the first SQL statement.

[0098] If the previous category label of the first SQL statement is ordinary SQL, that is, the routing strategy corresponding to the first SQL statement is the routing strategy corresponding to ordinary SQL, the electronic device can adjust the ordinary routing strategy corresponding to the first SQL statement, for example, adjust the weight value of each data node corresponding to the first SQL statement.

[0099] If the previous category label of the first SQL statement is "blocking SQL", then the first SQL statement still needs to be routed to a blocking data node. However, the electronic device can adjust the weight of at least one blocking data node for the first SQL statement.

[0100] For example, if the previous category label of the first SQL statement is "blocking SQL", that is, the first SQL statement has been configured with a strategy corresponding to blocking SQL, such as routing the first SQL statement to a blocking routing node.

[0101] At this point, based on the routing strategy corresponding to the high-frequency SQL statement, the blocking routing strategy corresponding to the first SQL statement can be adjusted. That is, the weight of each blocking data node for the first SQL statement can be adjusted, increasing the weight of the blocking data node with the shorter response time and decreasing the weight of the blocking data node with the longer response time.

[0102] For example, for the first SQL statement, the weight of blocking data node 1 is 1, and the response time is 800ms; the weight of blocking data node 2 is 0, and the response time is 200ms; the electronic device can adjust accordingly to: the weight of blocking data node 1 is 0.2, and the weight of blocking data node 2 is 0.8.

[0103] In some embodiments, the classification label of the first SQL statement is high-frequency SQL. The electronic device determines the first routing strategy corresponding to the first SQL statement based on the classification label and node status information, which may specifically include:

[0104] The first routing strategy corresponding to the first SQL statement is determined based on the node status information, including the node response time.

[0105] Among them, the data node with the shorter the response time has the higher priority, and the data node with the longer the response time has the lower priority.

[0106] In other words, electronic devices can dynamically adjust weights by combining classification labels with node status information: for high-frequency SQL queries, the weight of nodes with faster responses is increased, and the weight of nodes with slower responses is decreased, based on their average response time across nodes. For example, in IoT data processing scenarios, the response time of high-frequency SQL queries is monitored in real time, and nodes with faster responses are given higher weights, thereby allocating more requests to nodes with better performance. Therefore, the priority of different data nodes in the routing strategy can be adjusted accordingly.

[0107] For example, the response time of an electronic device can be the execution time of the same type of SQL on the data node in the last 60 seconds. The node load can be determined based on the CPU utilization, memory utilization, and the percentage of active connections. For example, it can be: Node load = 0.5 * CPU utilization + 0.3 * Memory utilization + 0.2 * Active connections. In addition, there are other ways to determine it, which are not limited here.

[0108] In some embodiments, the electronic device may determine the comprehensive score of a data node based on the weight corresponding to the node response time of the data node, combined with the normalized node response time and node load, and use this score as the weight of the data node.

[0109] For example, NodeA has a response time RT = 50ms and a CPU utilization of 90%; NodeB has a response time RT = 200ms and a CPU utilization of 30%.

[0110] The node response time (RT) threshold is 1000ms, and the weight of RT is higher, at 0.6, while the weight of CPU utilization is 0.4.

[0111] For NodeA, RT score: 1 - 50 / 1000 = 0.95; Load score: 1 - 90 / 100 = 0.10; Overall score: Score = 0.6 × 0.95 + 0.4 × 0.10 = 0.57 + 0.04 = 0.61.

[0112] For NodeB, RT component: 1 - 200 / 1000 = 0.80; Load component: 1 - 30 / 100 = 0.70; Overall score: Score = 0.6 × 0.80 + 0.4 × 0.70 = 0.48 + 0.28 = 0.76.

[0113] It is understandable that different SQL statements consume different types of computer resources. Suppose there are two database nodes, A and B. Node A has more remaining memory resources than node B, while node B has more remaining CPU resources than node A. SQL1 mainly consumes memory resources, while SQL2 mainly consumes CPU resources. In this case, SQL1 should be routed more to node A, and SQL2 should be routed more to node B. Therefore, the first routing strategy corresponding to the first SQL statement can be determined based on the category label and node status information.

[0114] In some embodiments, for ordinary SQL, SQL routing can be performed using node static weight configuration.

[0115] This possible implementation achieves refined optimization of resource allocation through a differentiated weight adjustment strategy. For example, in scenarios with high-frequency, SQL-intensive operations, nodes with short response times are prioritized to avoid resource waste; static weight configuration for less frequent, ordinary SQL queries reduces interference from low-frequency SQL queries on dynamic adjustments. This improves the adaptability of weight configuration and optimizes node resource utilization and SQL execution efficiency.

[0116] In some embodiments, the first SQL statement is categorized as blocking SQL. The electronic device determines the corresponding target data node according to the first routing policy and assigns the first SQL statement to the target data node, which may specifically include:

[0117] The electronic device determines the corresponding target blocked data node among at least one blocked data node according to the first routing strategy, and assigns the first SQL statement to the target blocked data node, whereby the blocked data node is a data node used to process the blocked SQL.

[0118] In other words, the electronic device can identify the first SQL statement as a blocking SQL statement based on classification tags; then, the electronic device will forcibly assign the blocking SQL to a preset blocking SQL execution node (such as B1). This blocking SQL execution node is a data node specifically designed to execute blocking SQL. For example, node B1 is configured as a blocking SQL execution node. After a complex query is marked as a blocking SQL, it is directly assigned to node B1 for execution.

[0119] This possible implementation effectively avoids resource contention by forcibly isolating blocking SQL queries. For example, in a flash sale, the isolated execution of blocking SQL queries prevents them from consuming excessive CPU resources and slowing down the response time of high-frequency SQL queries. This reduces the negative impact of blocking SQL queries on cluster performance, improves the execution efficiency of high-priority SQL queries, and enhances the overall stability of the cluster.

[0120] In one possible implementation, the electronic device can also obtain the first execution information corresponding to the execution of the first SQL statement. Therefore, optionally, the method further includes the following steps:

[0121] S205. After the first SQL statement is executed, the electronic device can obtain the first execution information corresponding to the first SQL statement and store it in the first-in-first-out storage space.

[0122] The first-in-first-out (FIFO) mechanism refers to the storage space where data is stored in chronological order, and the oldest record is removed first when the capacity is insufficient.

[0123] For example, the SQL sampling cache size sql_execute_time_fifo_size can be 200. Each SQL statement has a SQL sampling FIFO buffer. The FIFO buffer is a first-in-first-out buffer. When the buffer capacity is insufficient, the data entered into the buffer earliest is popped first. In this solution, the FIFO buffer only retains the last 200 execution records of the SQL statement.

[0124] The first execution information may include the execution response time, the node in which it was executed, and other information, such as the start time of execution. The specifics are not limited here.

[0125] In this possible implementation, real-time statistics through FIFO caching ensure the accuracy of dynamic weight adjustments. For example, FIFO caching avoids interference from historical data on weight adjustments, ensuring that the weighting strategy always aligns with the latest execution state. This further enhances the real-time response capability of load balancing and avoids resource allocation deviations caused by delayed statistics.

[0126] In one possible implementation, the electronic device can also acquire node state information in advance. Therefore, optionally, the method further includes the following steps:

[0127] S206. The electronic device acquires node status information, which includes at least one of the average response time, longest response time, and shortest response time of SQL statements for each data node in the data node cluster.

[0128] The node status information may include at least one of the following: the average response time, the longest response time, and the shortest response time for SQL statements for each data node in the data node cluster.

[0129] In some embodiments, the electronic device determines the classification label of SQL in a periodic statistical manner, that is, the classification label of SQL can be determined at fixed time intervals (such as 180 seconds).

[0130] In some embodiments, the electronic device can dynamically adjust the interval of periodic statistics based on the real-time load status of the database node.

[0131] This means that electronic devices can dynamically adjust the statistical interval based on real-time load conditions. For example, when the node load is high, the statistical interval is shortened to update execution time data more frequently; when the load is low, the interval is lengthened to reduce statistical overhead.

[0132] Real-time load status refers to the current resource usage of database nodes, such as the load levels of CPU, memory, and I / O. For example, node R1 has a CPU load of 90%, while node R2 has a load of 50%.

[0133] In this possible implementation, the statistical frequency is adaptively matched to the load status by dynamically adjusting the statistical interval. For example, more frequent statistics during high loads can reflect resource changes in a timely manner, while statistical overhead is reduced during low loads. This improves resource utilization efficiency and avoids statistical redundancy or lag caused by fixed intervals.

[0134] In this embodiment, the load balancing capability of the database cluster is significantly improved through SQL classification and routing strategy adjustment. Different SQL queries are dynamically allocated to matching nodes based on their resource consumption characteristics (e.g., memory-intensive, CPU-intensive), avoiding resource waste. Furthermore, by identifying and centrally executing blocking SQL queries, it prevents them from affecting the execution efficiency of other SQL queries, reducing the risk of cascading performance degradation. Based on real-time execution time statistics and FIFO buffer analysis, node weights are dynamically adjusted to ensure load distribution always adapts to changes in node status. By designing load strategies on a SQL-by-SQL basis, the limitations of traditional "node-by-node" approaches are overcome, improving service stability in high-concurrency scenarios. The application of multiple load strategies in combination is supported, adapting to different business scenario requirements.

[0135] Figure 5 This is a schematic diagram of the structure of a query routing device provided in an embodiment of this application, as shown below. Figure 5 As shown, the query routing device 500 provided in this embodiment includes, in some embodiments:

[0136] The first determining module 501 is used to determine the classification label of the first SQL statement based on the execution time information of the first structured query language SQL. The classification label includes blocking SQL, high-frequency SQL, or ordinary SQL.

[0137] The second determining module 502 is used to determine the first routing strategy corresponding to the first SQL statement based on the classification label and node status information. The node status information is used to indicate the SQL statement response time of each data node in the data node cluster, and the first routing strategy is used to indicate the weight of each data node for the first SQL statement.

[0138] The routing module 503 is used to determine the corresponding target data node according to the first routing strategy and route the first SQL statement to the target data node.

[0139] In one possible implementation, the first determining module 501 is specifically used to: determine the classification label of the first SQL statement as blocking SQL when the execution time of the first SQL statement is greater than the execution time threshold; determine the classification label of the first SQL statement as high-frequency SQL when the execution time of the first SQL statement is less than or equal to the execution time threshold and the execution frequency is greater than the execution frequency threshold; and determine the classification label of the first SQL statement as ordinary SQL when the execution time of the first SQL statement is less than or equal to the execution time threshold and the execution frequency is less than or equal to the execution frequency threshold.

[0140] In one possible implementation, the second determining module 502 is specifically configured to: if the classification label of the first SQL statement is blocking SQL, then the corresponding first routing strategy is to route the first SQL statement to a blocking data node, wherein the blocking data node is a data node used to process blocking SQL; or, if the classification label of the first SQL statement is high-frequency SQL, then the corresponding first routing strategy is to route the first SQL statement according to a dynamic routing configuration, wherein the dynamic routing configuration is used to indicate the weight of each data node for the first SQL statement determined according to the response time of each data node; or, if the classification label of the first SQL statement is ordinary SQL, then the corresponding first routing strategy is to route the first SQL statement according to a preset static weight configuration.

[0141] In one possible implementation, the category label is "blocking SQL". The second determining module 502 is specifically used to: determine the routing policy corresponding to the blocking SQL as the first routing policy; or, if the previous category label of the first SQL statement is "high-frequency SQL", delete the routing policy corresponding to the high-frequency SQL and determine the routing policy corresponding to the blocking SQL as the first routing policy.

[0142] In one possible implementation, the classification label is high-frequency SQL, and the second determining module 502 is specifically used to: adjust the blocking routing strategy corresponding to the first SQL statement according to the routing strategy corresponding to the high-frequency SQL when the previous classification label of the first SQL statement is blocking SQL; or, adjust the ordinary routing strategy corresponding to the first SQL statement according to the routing strategy corresponding to the high-frequency SQL when the previous classification label of the first SQL statement is ordinary SQL; or, determine the routing strategy corresponding to the high-frequency SQL as the first routing strategy.

[0143] In one possible implementation, the classification label is high-frequency SQL, and the second determining module 502 is specifically used to: determine the first routing strategy corresponding to the first SQL statement based on the node status information, the node status information including the node response time; wherein, the data node with the shorter the node response time has the higher priority, and the data node with the longer the node response time has the lower priority.

[0144] In one possible implementation, the query routing device 500 further includes:

[0145] The first acquisition module 504 is used to acquire the first execution information corresponding to the first SQL statement after the first SQL statement is executed and store it in the first-in-first-out storage space.

[0146] In one possible implementation, the query routing device 500 further includes:

[0147] The second acquisition module 505 is used to acquire node status information of the data node cluster. The node status information includes at least one of the average response time, longest response time and shortest response time of SQL statements for each data node in the data node cluster.

[0148] The query routing device provided in this embodiment can execute the methods implemented in the above method embodiments. Its implementation principle and technical effect are similar, and will not be described in detail here.

[0149] Figure 6 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application. Figure 6 As shown, the electronic device 60 may include a memory 601 and a processor 602. Optionally, the electronic device may also include a transceiver 603, wherein the memory 601 and the processor 602 communicate; for example, the memory 601, the processor 602 and the transceiver 603 may communicate via a communication bus 604, the memory 601 is used to store a computer program, and the processor 602 executes the computer program to implement the method of the above embodiments.

[0150] Optionally, the aforementioned processor can be a Central Processing Unit (CPU), or other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), etc. The general-purpose processor can be a microprocessor or any conventional processor. The steps in the method embodiments disclosed in this application can be directly implemented by a hardware processor, or implemented by a combination of hardware and software modules within the processor.

[0151] This application also provides a computer-readable storage medium storing computer-executable instructions, which, when executed by a processor, implement the methods in any of the above method embodiments.

[0152] This application also provides a computer program product, including a computer program that, when executed by a processor, implements the methods in any of the above method embodiments.

[0153] All or part of the steps in the above method embodiments can be implemented by hardware related to program instructions. The aforementioned program can be stored in a readable memory. When the program is executed, it performs the steps of the above method embodiments; and the aforementioned memory (storage medium) includes: read-only memory (ROM), RAM, flash memory, hard disk, solid-state drive, magnetic tape, floppy disk, optical disk, and any combination thereof.

[0154] This application describes embodiments with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of this application. It should be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processing unit of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processing unit of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0155] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0156] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0157] Obviously, those skilled in the art can make various modifications and variations to the embodiments of this application without departing from the spirit and scope of this application. Therefore, if these modifications and variations to the embodiments of this application fall within the scope of the claims of this application and their equivalents, this application also intends to include these modifications and variations.

[0158] In this application, the term "comprising" and its variations can refer to non-limiting inclusion; the term "or" and its variations can refer to "and / or". The terms "first", "second", etc., in this application are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. In this application, "multiple" refers to two or more. "And / or" describes 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. The character " / " generally indicates that the preceding and following related objects have an "or" relationship.

[0159] It should be noted that, for the sake of simplicity, the foregoing method embodiments are all described as a series of actions. However, those skilled in the art should understand that this application is not limited to the described order of actions, as some steps may be performed in other orders or simultaneously according to this application. Furthermore, those skilled in the art should also understand that the embodiments described in the specification are all optional embodiments, and the actions and modules involved are not necessarily essential to this application.

[0160] It should be further noted that although the steps in the flowchart are shown sequentially according to the arrows, these steps are not necessarily executed in the order indicated by the arrows. Unless explicitly stated herein, there is no strict order restriction on the execution of these steps, and they can be executed in other orders. Moreover, at least some steps in the flowchart may include multiple sub-steps or multiple stages. These sub-steps or stages are not necessarily completed at the same time, but can be executed at different times. The execution order of these sub-steps or stages is not necessarily sequential, but can be performed alternately or in turn with other steps or at least some of the sub-steps or stages of other steps.

[0161] It should be understood that the above-described device embodiments are merely illustrative, and the device of this application can also be implemented in other ways. For example, the division of units / modules in the above embodiments is only a logical functional division, and there may be other division methods in actual implementation. For example, multiple units, modules, or components may be combined, or integrated into another system, or some features may be ignored or not executed.

[0162] Furthermore, unless otherwise specified, the functional units / modules in the various embodiments of this application can be integrated into one unit / module, or each unit / module can exist physically separately, or two or more units / modules can be integrated together. The integrated units / modules described above can be implemented in hardware or as software program modules.

[0163] When integrated units / modules are implemented in hardware, the hardware can be digital circuits, analog circuits, etc. The physical implementation of the hardware structure includes, but is not limited to, transistors, memristors, etc. Unless otherwise specified, the processor can be any suitable hardware processor, such as a CPU, GPU, FPGA, DSP, and ASIC, etc. Unless otherwise specified, the storage unit can be any suitable magnetic or magneto-optical storage medium, such as Resistive Random Access Memory (RRAM), Dynamic Random Access Memory (DRAM), Static Random Access Memory (SRAM), Enhanced Dynamic Random Access Memory (EDRAM), High-Bandwidth Memory (HBM), Hybrid Memory Cube (HMC), etc.

[0164] If the integrated unit / module is implemented as a software program module and sold or used as an independent product, it can be stored in a computer-readable storage device (CMD). Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, or all or part of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a memory 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 steps of the methods of the various embodiments of this application. The aforementioned memory includes various media capable of storing program code, such as a USB flash drive, read-only memory (ROM), random access memory (RAM), portable hard drive, magnetic disk, or optical disk.

[0165] In the above embodiments, the descriptions of each embodiment have their own emphasis. For parts not described in detail in a certain embodiment, please refer to the relevant descriptions of other embodiments. The technical features of the above embodiments can be combined arbitrarily. For the sake of brevity, not all possible combinations of the technical features in the above embodiments are described. However, as long as the combination of these technical features does not contradict each other, it should be considered within the scope of this specification.

[0166] Other embodiments of this application will readily occur to those skilled in the art upon consideration of the specification and practice of the invention disclosed herein. This application is intended to cover any variations, uses, or adaptations of this application that follow the general principles of this application and include common knowledge or customary techniques in the art not disclosed herein. The specification and examples are to be considered exemplary only, and the true scope and spirit of this application are indicated by the following claims.

[0167] It should be understood that this application is not limited to the precise structure described above and shown in the accompanying drawings, and various modifications and changes can be made without departing from its scope. The scope of this application is limited only by the appended claims.

Claims

1. A query routing method, characterized in that, The method includes: The classification label of the first SQL statement is determined based on the execution time information of the first structured query language SQL statement. The classification label includes blocking SQL, high-frequency SQL, or ordinary SQL. The first routing strategy corresponding to the first SQL statement is determined based on the classification label and node status information, wherein the node status information is used to indicate the SQL statement response time of each data node in the data node cluster. The first routing strategy determines the corresponding target data node, and the first SQL statement is routed to the target data node.

2. The method according to claim 1, characterized in that, The step of determining the classification label of the first SQL statement based on the execution time information of the first SQL statement includes: If the execution time of the first SQL statement exceeds the execution time threshold, the first SQL statement is classified as a blocking SQL statement. If the execution time of the first SQL statement is less than or equal to the execution time threshold and the execution frequency is greater than the execution frequency threshold, the first SQL statement is classified as a high-frequency SQL statement. If the execution time of the first SQL statement is less than or equal to the execution time threshold and the execution frequency is less than or equal to the execution frequency threshold, the first SQL statement is classified as ordinary SQL.

3. The method according to claim 1, characterized in that, The step of determining the first routing strategy corresponding to the first SQL statement based on the classification label and node status information includes: If the first SQL statement is categorized as "blocking SQL", then the corresponding first routing strategy is to route the first SQL statement to a blocking data node, where the blocking data node is a data node used to process blocking SQL statements; or, If the category label of the first SQL statement is "high-frequency SQL", then the corresponding first routing strategy is to route the first SQL statement according to the dynamic routing configuration, wherein the dynamic routing configuration is used to indicate the weight of each data node for the first SQL statement, determined based on the response time of each data node; or, If the category label of the first SQL statement is "ordinary SQL", then the corresponding first routing strategy is to route the first SQL statement according to the preset static weight configuration.

4. The method according to claim 1, characterized in that, The classification label is the blocking SQL statement. Determining the first routing strategy corresponding to the first SQL statement based on the classification label and node status information includes: The routing policy corresponding to the blocking SQL is determined to be the first routing policy; or, If the previous category label of the first SQL statement is a high-frequency SQL statement, delete the routing policy corresponding to the high-frequency SQL statement and determine the routing policy corresponding to the blocking SQL statement as the first routing policy.

5. The method according to claim 1, characterized in that, The classification label is the high-frequency SQL statement, and the step of determining the first routing strategy corresponding to the first SQL statement based on the classification label and node status information includes: If the previous category label of the first SQL statement is the blocking SQL, the blocking routing strategy corresponding to the first SQL statement is adjusted according to the routing strategy corresponding to the high-frequency SQL; or, If the previous category label of the first SQL statement is the "normal SQL", then the normal routing strategy corresponding to the first SQL statement is adjusted according to the routing strategy corresponding to the high-frequency SQL; or, The routing strategy corresponding to the high-frequency SQL is determined to be the first routing strategy.

6. The method according to claim 1, characterized in that, The classification label is the high-frequency SQL statement, and the step of determining the first routing strategy corresponding to the first SQL statement based on the classification label and node status information includes: The first routing strategy corresponding to the first SQL statement is determined based on the node status information, wherein the node status information includes the node response time. Among them, the data node with the shorter the node response time has the higher priority, and the data node with the longer the node response time has the lower priority.

7. The method according to any one of claims 1-6, characterized in that, The method further includes: After the first SQL statement is executed, the first execution information corresponding to the first SQL statement is obtained and stored in the first-in-first-out (FIFO) storage space.

8. The method according to claim 1, characterized in that, The method further includes: Obtain the node status information of the data node cluster, which includes at least one of the average response time, longest response time, and shortest response time of SQL statements for each data node in the data node cluster.

9. A query routing device, characterized in that, The query routing device includes: The first determining module is used to determine the classification label of the first SQL statement based on the execution time information of the first structured query language SQL, wherein the classification label includes blocking SQL, high-frequency SQL, or ordinary SQL; The second determining module is used to determine the first routing strategy corresponding to the first SQL statement based on the classification label and node status information. The node status information is used to indicate the status information of each data node in the data node cluster, and the first routing strategy is used to indicate the weight of each data node for the first SQL statement. The routing module is used to determine the corresponding target data node according to the first routing strategy and route the first SQL statement to the target data node.

10. An electronic device, characterized in that, include: A processor, and a memory communicatively connected to the processor; The memory stores computer-executed instructions; The processor executes computer execution instructions stored in the memory to implement the method as described in any one of claims 1 to 8.

Images