Query statement backup machine intelligent routing method, device, equipment and medium

Abstract

The application relates to the technical field of distributed databases, and discloses a query statement backup machine intelligent routing method, device, equipment and medium, which comprises the following steps: parsing and constructing an abstract syntax tree for an SQL query request SQL statement, extracting statistical features and execution plan features of the abstract syntax tree, and generating an SQL portrait vector; building an initial static capability portrait vector for each backup machine, and updating the initial static capability portrait vector to obtain a capability portrait vector; matching the SQL portrait vector with the capability portrait vector, calculating the matching scores of each dimension, and dynamically distributing the weights of each dimension according to the resource requirement features of the SQL portrait vector to obtain a comprehensive matching degree; filtering backup machines that do not meet the conditions according to business requirement constraints, and screening out a candidate backup machine set based on the comprehensive matching degree; determining a target backup machine according to a final routing score, and sending the SQL statement to the target backup machine for execution. The application intelligently routes to the optimal backup machine through dynamic matching of an SQL portrait and backup machine capability, and improves execution efficiency.

Description

Technical Field

[0001] This invention relates to the field of distributed database technology, and in particular to a method, apparatus, device, and medium for intelligent routing of query statements on standby machines. Background Technology

[0002] In mainstream relational database clusters, a read-write splitting strategy is commonly used to distribute query pressure on the primary database, directing write operations to the primary database and distributing read operations to one or more standby databases. However, existing technologies still have several shortcomings in this area. First, the most common solution is simple routing based on read / write type. This strategy treats all read requests equally, using only the instantaneous load status of each standby machine as the selection criterion, completely ignoring the significant differences in resource consumption characteristics among different read requests. Second, some solutions introduce dynamic weighting mechanisms based on load balancing, configuring each slave node with weights that are dynamically adjusted according to network jitter or primary-standby synchronization delays. While this can achieve load balancing to some extent, its adjustment granularity remains at the node level, failing to perceive the semantic characteristics of specific queries. Furthermore, more advanced solutions allow users to create "workload classes" and define workload mappings via SQL commands, specifying routing location hints for each workload class. However, the core flaw of this solution lies in its deterministic mapping logic—when all nodes specified by a workload class are unavailable, the system lacks an elastic matching mechanism to find a "suboptimal but compatible" standby machine among non-precise standby machines, leading to routing failures or forced fallback to the primary database.

[0003] However, existing technologies completely ignore the heterogeneity of standby machines. Different standby machines may have vastly different hardware configurations, including the number of CPU cores, disk types (e.g., the IOPS of NVMe SSDs differ by tens of times from that of SATA HDDs), memory capacity and bandwidth, or different resource reservation strategies. As a result, their processing capabilities may differ by orders of magnitude when facing workloads such as computationally intensive queries, I / O-intensive queries, and low-latency transactions. Existing technologies treat all standby machines as a homogeneous resource pool, ranking and selecting the best one based solely on a single-dimensional comprehensive score, failing to leverage the differentiated structural advantages of each standby machine. The granularity of SQL type identification is extremely coarse. Most solutions only distinguish between read and write operations, failing to recognize the semantic differences within the same read operation—for example, a simple single-table point lookup (primary key equality query) and a complex report query involving multi-table JOINs, aggregate functions, subqueries, and sorting have drastically different resource consumption models, but existing routing strategies treat them equally, leading to a severe mismatch between decision-making and actual load characteristics. The lack of business-level awareness and constraints means that in multi-tenant or business-tiered deployment scenarios, different business lines have varying service quality requirements regarding response latency (e.g., real-time transactions require millisecond-level responses, while offline reports can tolerate second-level latency) and data freshness (e.g., risk control systems require standby replication latency of less than 1 second, while statistical analysis can accept 30-second latency). Existing technologies cannot fine-tune candidate standby machines based on these business constraints, easily routing high-priority queries to standby machines with excessive latency or outdated data. The routing matching logic lacks flexibility; existing solutions are essentially based on a strict deterministic mapping relationship—a hard-coded "one-to-one" or "one-to-many" binding between a specific workload class and a specific routing location. When the bound target node becomes unavailable due to failure, network partitioning, or maintenance, the system cannot intelligently evaluate and select a "capability-compatible and currently healthy" suboptimal node from the remaining standby machines, but can only execute a simple fallback strategy, sacrificing the overall availability and robustness of the system. These shortcomings collectively limit the query execution efficiency and resource utilization of distributed database clusters in mixed load scenarios. Summary of the Invention

[0004] The main objective of this invention is to provide a method, apparatus, device, and storage medium for intelligent routing of standby servers for query statements. This invention aims to solve the technical problem that existing distributed database standby server routing strategies cannot perform fine-grained matching based on the resource requirements of SQL statements and the dynamic capabilities of standby servers, resulting in low query execution efficiency and uneven resource utilization.

[0005] To achieve the above objectives, the present invention provides a method for intelligent routing of query statements to backup machines, comprising: Receive SQL query requests, parse the SQL statements in the SQL query requests, construct an abstract syntax tree based on the parsing results, and extract the statistical features and execution plan features of the abstract syntax tree; SQL profile vectors are generated using the abstract syntax tree, statistical features, and execution plan features. An initial static capability profile vector is pre-constructed for each standby machine, and the initial static capability profile vector is dynamically and smoothly updated based on historical execution data to obtain a capability profile vector. The SQL profile vector is matched with the capability profile vector of each standby machine in multiple dimensions, the matching score of each dimension is calculated, and the weight of each dimension is dynamically allocated according to the resource requirement characteristics of the SQL profile vector. The matching score of each dimension and the corresponding weight are weighted and summed to obtain the comprehensive matching degree. Based on business requirements, standby machines that do not meet the conditions are filtered out, and a set of candidate standby machines is selected based on the comprehensive matching degree. If the candidate standby set is not empty, then a second dynamic filtering is performed based on the real-time status information of each candidate standby to calculate the final routing score. The target standby machine is determined based on the final routing score, and the SQL statement is sent to the target standby machine for execution.

[0006] Furthermore, to achieve the above objectives, the present invention provides a query statement backup intelligent routing device, comprising: The request parsing module is used to receive SQL query requests, parse the SQL statements in the SQL query requests, construct an abstract syntax tree based on the parsing results, and extract the statistical features and execution plan features of the abstract syntax tree; The request profiling module is used to generate an SQL profiling vector using the abstract syntax tree, statistical features, and execution plan features. The standby machine profiling module is used to pre-build an initial static capability profile vector for each standby machine, and dynamically and smoothly update the initial static capability profile vector based on historical execution data to obtain a capability profile vector. The multi-dimensional matching module is used to perform multi-dimensional matching between the SQL profile vector and the capability profile vector of each standby machine, calculate the matching score of each dimension, dynamically allocate the weight of each dimension according to the resource requirement characteristics of the SQL profile vector, and perform a weighted sum of the matching scores of each dimension and the corresponding weights to obtain the comprehensive matching degree. The standby machine screening module is used to filter standby machines that do not meet the conditions according to business requirements, and to select a set of candidate standby machines based on the comprehensive matching degree. The secondary filtering module is used to perform secondary dynamic filtering based on the real-time status information of each candidate backup machine if the candidate backup machine set is not empty, and to calculate the final routing score. The target standby module is used to determine the target standby machine based on the final routing score and send the SQL statement to the target standby machine for execution.

[0007] Furthermore, to achieve the above objectives, the present invention also provides a computer device, the computer device including a memory, a processor, and a query statement standby intelligent routing program stored in the memory and executable on the processor, wherein when the query statement standby intelligent routing program is executed by the processor, it implements the steps of the query statement standby intelligent routing method as described above.

[0008] Furthermore, to achieve the above objectives, the present invention also provides a computer-readable storage medium storing a query statement standby intelligent routing program, wherein when the query statement standby intelligent routing program is executed by a processor, it implements the steps of the query statement standby intelligent routing method as described above.

[0009] Beneficial Effects: This invention relates to the field of distributed database technology and discloses a method, apparatus, device, and medium for intelligent routing of query statements to standby machines. The method includes: receiving an SQL query request; parsing the SQL statement of the SQL query request; constructing an abstract syntax tree (AST) based on the parsing results; extracting statistical features and execution plan features from the AST; generating an SQL profile vector using the AST, statistical features, and execution plan features; pre-constructing an initial static capability profile vector for each standby machine; and dynamically and smoothly updating the initial static capability profile vector based on historical execution data to obtain a capability profile vector; and routing the SQL statement to standby machines. The SQL profile vector is matched with the capability profile vector of each standby machine in multiple dimensions, and the matching score for each dimension is calculated. Weights for each dimension are dynamically allocated based on the resource requirement characteristics of the SQL profile vector. The matching scores for each dimension are then weighted and summed to obtain a comprehensive matching degree. Standby machines that do not meet the business requirement constraints are filtered out, and a candidate standby machine set is selected based on the comprehensive matching degree. If the candidate standby machine set is not empty, a second dynamic filtering is performed based on the real-time status information of each candidate standby machine to calculate the final routing score. The target standby machine is determined based on the final routing score, and the SQL statement is sent to the target standby machine for execution. This invention improves execution efficiency by dynamically matching SQL profiles with standby machine capabilities and intelligently routing to the optimal standby machine. Attached Figure Description

[0010] The present invention will be further described below with reference to the accompanying drawings and embodiments. In the accompanying drawings: Figure 1 This is a schematic diagram of an application environment for the intelligent routing method for standby machines in query statements according to an embodiment of the present invention; Figure 2 This is a flowchart illustrating an embodiment of the intelligent routing method for standby machines in the present invention. Figure 3 This is a schematic diagram of the functional modules of a preferred embodiment of the intelligent routing device for standby machines in the present invention. Figure 4 This is a schematic diagram of the structure of a computer device according to an embodiment of the present invention; Figure 5 This is another structural schematic diagram of a computer device according to one embodiment of the present invention. Detailed Implementation

[0011] It should be understood that the specific embodiments described herein are for illustrative purposes only and are not intended to limit the scope of the invention.

[0012] The query statement standby intelligent routing method provided in this embodiment of the invention can be applied to applications such as... Figure 1 In this application environment, the client communicates with the server via a network. The server can receive SQL query requests initiated by the client, parse the SQL statements of the SQL query requests, construct an abstract syntax tree, and extract the statistical features and execution plan features of the abstract syntax tree; generate an SQL profile vector based on the abstract syntax tree, statistical features, and execution plan features; pre-construct an initial static capability profile vector for each standby machine, and dynamically and smoothly update the initial static capability profile vector based on historical execution data to obtain a capability profile vector; perform multi-dimensional matching between the SQL profile vector and the capability profile vector of each standby machine, calculate the matching score for each dimension, and dynamically allocate the weights of each dimension according to the resource requirement features of the SQL profile vector; perform a weighted sum of the matching scores of each dimension and their corresponding weights to obtain a comprehensive matching degree; filter standby machines that do not meet the conditions according to business requirement constraints, and select a set of candidate standby machines based on the comprehensive matching degree; if the set of candidate standby machines is not empty, perform a second dynamic filtering based on the real-time status information of each candidate standby machine, and calculate the final routing score; determine the target standby machine based on the final routing score, and send the SQL statement to the target standby machine for execution. This invention dynamically matches SQL profiles with standby machine capabilities, intelligently routing to the optimal standby machine to improve execution efficiency. The client can be, but is not limited to, various personal computers, laptops, smartphones, tablets, and portable wearable devices. The server can be implemented using a standalone server or a server cluster consisting of multiple servers. The invention will be described in detail below through specific embodiments.

[0013] Please see Figure 2 , Figure 2 This is a flowchart illustrating an embodiment of the intelligent backup routing method for query statements provided by the present invention. It should be noted that although the logical order is shown in the flowchart, in some cases, the steps shown or described may be performed in a different order than that shown here.

[0014] like Figure 2 As shown, the intelligent routing method for standby machines based on query statements proposed in this invention includes the following steps: S100: Receive an SQL query request, parse the SQL statement of the SQL query request, construct an abstract syntax tree based on the parsing result, and extract the statistical features and execution plan features of the abstract syntax tree; S200: Generate an SQL profile vector using the abstract syntax tree, statistical features, and execution plan features; S300. An initial static capability profile vector is pre-constructed for each standby machine, and the initial static capability profile vector is dynamically and smoothly updated based on historical execution data to obtain a capability profile vector. S400. Perform multi-dimensional matching between the SQL profile vector and the capability profile vector of each standby machine, calculate the matching score of each dimension, dynamically allocate the weight of each dimension according to the resource requirement characteristics of the SQL profile vector, and sum the matching score of each dimension with the corresponding weight to obtain the comprehensive matching degree. S500: Filter standby machines that do not meet the conditions according to business requirements, and select a set of candidate standby machines based on the comprehensive matching degree. S600. If the candidate standby set is not empty, then perform a second dynamic screening based on the real-time status information of each candidate standby and calculate the final routing score. S700. Determine the target standby machine based on the final routing score, and send the SQL statement to the target standby machine for execution.

[0015] In this embodiment, when a client initiates a Structured Query Language (SQL) query request and it reaches the routing layer, the system first performs lexical and syntactic analysis on the SQL statement in the request to construct an Abstract Syntax Tree (AST). The AST accurately represents the syntactic composition of the SQL statement in a tree structure. Based on this, the system further extracts three types of key information: first, the structural features of the AST itself, such as the number of tables involved, the number of join operations, the number of subqueries, the number of aggregate functions, the number of sorting operations, and whether it contains deduplication or union operation flags; second, execution plan features, including full table scan flags, estimated number of scanned rows, estimated number of result set rows, whether temporary tables are used, and whether file sorting is used; and third, statistical features, such as the SQL statement length, historical average execution time, and SQL template hash value. These features together constitute the raw data for generating the SQL profile.

[0016] Subsequently, the system generates a four-dimensional SQL profile vector, denoted as Q, based on these features. This vector contains four components: computational intensity, input / output (IO) intensity, memory intensity, and low-latency requirement. Each component's value ranges from 0 to 1. The computational intensity component is obtained by weighted combination of normalized sub-items such as the number of join operations, the number of aggregate functions, the number of sorting operations, the number of subqueries, and the estimated number of rows scanned. Join operations are considered the primary source of computational intensity and therefore receive the highest weight. The IO intensity component focuses on the full table scan flag, the estimated number of rows scanned, the temporary table flag, the number of tables, and the SQL length. Full table scans are considered the strongest signal of IO intensity. The memory intensity component focuses on the estimated number of rows in the result set, the temporary table flag, the estimated number of rows scanned, the number of joins, and the file sorting flag. The low-latency requirement component is designed in the opposite way to the other three dimensions. It is calculated in reverse based on the complexity of the query structure; the simpler the query structure and the more suitable it is for low-latency processing, the higher this component's value. After obtaining the values ​​of the four components, the system will also determine the main type of the SQL based on the maximum value and a preset threshold, such as compute-intensive, input / output-intensive, low-latency, hybrid, or general type, so as to provide a type reference for subsequent matching.

[0017] Regarding standby machine capability profiling, the system pre-constructs an initial static capability profile vector for each standby machine in the cluster. This vector also includes four dimensions: Central Processing Unit (CPU) computing power index, input / output throughput index, memory capacity index, and low latency index. The CPU computing power index is calculated based on the number of cores, clock speed, and generation coefficient of the standby machine, and then normalized by dividing by a reference baseline value. The input / output throughput index combines three indicators: disk type coefficient, sequential read / write throughput, and random read / write input / output operations per second (IOPS), with the disk type coefficient having the greatest impact on input / output capabilities. The memory capacity index combines available memory capacity and memory bandwidth, giving capacity a higher weight because capacity has a more significant impact on cache hit rate. The low latency index is calculated based on the historical average response latency of the standby machine; a higher value indicates lower latency and is more suitable for low-latency queries. These initial profiles can be statically configured through the management interface or configuration files, making them particularly suitable for scenarios where hardware configuration is known and stable.

[0018] In addition to static configuration, the system also supports dynamic and smooth updates based on historical execution data. Every evaluation cycle, for example, every fifteen minutes, the system collects actual execution performance data for various SQL profile queries on each standby machine, including average execution time, 95th percentile execution time, throughput, average CPU utilization, and average I / O latency. Based on this data, the system calculates the actual performance score of each standby machine across various dimensions, and then uses an Exponential Moving Average (EMA) algorithm to smoothly update the original profile vector, with the learning rate typically set between 0.15 and 0.3. This allows the standby machine's capability profile to automatically adapt to dynamic factors such as hardware aging, changes in resource contention, or configuration adjustments. Simultaneously, the system monitors the continuous downward trend of the capability index; when a certain dimension continues to decline beyond a threshold, an alarm is triggered, alerting administrators to potential hardware failures or resource anomalies.

[0019] Once the system obtains the profile vector of the current SQL query, the routing decision engine retrieves the capability profile vectors of all available standby machines from the standby machine capability profile module and performs multi-dimensional compatibility matching. The core of this matching is calculating the weighted matching degree between the SQL profile vector and the capability profile vector of each standby machine. Specifically, the capability indices of each dimension of the standby machine are first normalized to the range of 0 to 1 within the cluster. Then, the matching score for each of the four dimensions is calculated. The matching score for each dimension is equal to 1 minus the absolute value of the difference between the SQL profile component and the normalized standby machine capability component in that dimension. The closer the matching score is to 1, the better the SQL requirement matches the standby machine capability in that dimension.

[0020] The matching weights are not fixed but dynamically allocated based on the resource requirements of the current SQL profile. The system uses the sum of the four dimensions as the denominator, and the proportion of each component is its weight. Thus, if the computational intensity of a SQL query is significantly higher than other components, the weight of the CPU computing power dimension automatically increases during matching; conversely, if the low-latency requirement component is high, the weight of the low-latency dimension automatically increases. This dynamic weighting mechanism ensures that the matching process focuses on the resource dimensions most urgently needed by the query.

[0021] After calculating the overall matching score, the system filters based on business requirement strategies. These strategies are configured by tenant or business level and typically include maximum response latency constraints and data freshness constraints. The latter is specifically expressed as the allowed standby machine replication latency threshold, such as less than one second, five seconds, or thirty seconds. The system checks whether each standby machine meets these constraints; standby machines that do not meet them, even with a high matching score, are excluded. Furthermore, the system sets a minimum compatibility matching threshold, typically between 0.4 and 0.6. Standby machines with an overall matching score below this threshold are considered fundamentally incompatible and are also excluded, ultimately generating a candidate standby machine set.

[0022] If the candidate standby machine set is not empty, the system enters a second dynamic optimization phase. The system obtains real-time status information for each candidate standby machine, including CPU utilization, memory utilization, request queue length, and primary / standby replication latency. Then, a comprehensive real-time status score is calculated, consisting of four parts: CPU idle rate, memory idle rate, request queue idle rate, and replication latency normalized idle rate, each assigned a weight of 0.30, 0.20, 0.25, and 0.25, respectively. Next, the system weights and sums the previous comprehensive matching score with the comprehensive real-time status score to obtain a final routing score. The balance coefficient between matching score and real-time status is set to 0.5 by default; for scenarios with strong business constraints, the weight of matching score can be increased to emphasize matching quality. The system sorts all candidate standby machines from highest to lowest based on their final routing scores, and the machine with the highest score is selected as the target standby machine.

[0023] If the candidate standby set is empty, or if the selected optimal standby suddenly becomes unavailable before execution, the system will trigger the degradation handling module. The degradation strategy can try the following options in sequence: first, select the standby with the second highest overall matching degree but still above the minimum threshold as a candidate; if there is no suitable standby, the query will be rolled back to the primary database; if the primary database is also under too much pressure, the request will be allowed to enter the queuing queue. This tiered elastic degradation strategy ensures high availability of the system under abnormal conditions.

[0024] Finally, the routing execution module sends the SQL statement to the finally determined target standby machine, which executes the query and returns the result to the client. Throughout the entire process, all profile data, matching calculations, and status assessments are completed in real time at the routing layer, completely transparent to the client, thus achieving refined and intelligent standby machine routing.

[0025] In one embodiment, S100 includes: S101. Receive the SQL query request initiated by the client and obtain the SQL statement in the SQL query request; S102. Perform lexical analysis on the SQL statement, and segment the character sequence of the SQL statement according to the SQL lexical strategy to obtain a Token stream; S103. Perform syntax analysis on the Token stream, and combine the Tokens in the Token stream according to the SQL syntax strategy to obtain syntax units; S104. Construct an abstract syntax tree based on the Token stream and the syntax unit; S105. Parse the abstract syntax tree and extract statistical features and execution plan features.

[0026] In this embodiment, after the routing layer receives a Structured Query Language (SQL) query request initiated by the client, the system first obtains the complete SQL statement text to be processed from the request. Then, in the lexical analysis phase, the system, based on a predefined lexical strategy of LEAST, identifies and segments the original character sequence in the SQL statement according to categories such as keywords, identifiers, operators, constants, and delimiters, thereby generating an ordered token stream, where each token represents the smallest syntactic unit. Next is the syntax analysis phase, where the system, according to the syntax strategy of LEAST, combines and reduces the tokens in the token stream, gradually assembling adjacent tokens into units with clear syntactic meanings, such as expressions, conditional clauses, table references, and query blocks, according to SQL syntax rules. Based on this, the system recursively assembles a complete Abstract Syntax Tree (AST) using the token stream generated in the lexical analysis phase and the various syntactic units obtained in the syntax analysis phase, following a syntax tree construction algorithm. This abstract syntax tree accurately expresses the syntactic composition, operation order, and logical nesting relationship of the original SQL statement in a tree-like hierarchical structure. Each node in the tree corresponds to a syntactic construct, such as a select node, join node, filter node, or sort node.

[0027] After constructing the abstract syntax tree (AST), the system performs a depth-first traversal and parsing of the tree to extract various features necessary for generating the SQL profile. Regarding structural features, the system counts the number of key nodes in the AST, specifically including the number of tables involved in the query, the number of JOIN operations, the number of subqueries, the number of aggregate functions (e.g., summation, counting, averaging), and the number of sorting operations. It also records whether deduplication and union operations are included. Regarding execution plan features, the system combines the optimizer's output estimates to extract the table scan type (e.g., full table scan or index scan), the estimated number of rows to be scanned, the estimated number of rows in the final result set, whether a temporary table must be used to complete the query, and whether file sorting is required. Simultaneously, the system extracts statistical features related to the query itself, including the original text length of the SQL statement, the average execution time of the statement template in historical execution records, and the hash value used to identify the same SQL template. All of the above features, including structural features, execution plan features, and statistical features, together constitute the complete original data foundation for the subsequent generation of multidimensional SQL profile vectors, providing sufficient basis for accurately identifying the resource requirement type of the query.

[0028] In one embodiment, S200 includes: S201. Calculate the computational intensity score based on the number of join operations, the number of aggregate functions, the number of sorting operations, the number of subqueries, and the estimated number of rows scanned in the execution plan features. S202. Calculate the IO intensity score based on the full table scan flag, estimated number of scanned rows, temporary table usage flag, number of tables, and SQL length in the execution plan characteristics; S203. Calculate the memory density score based on the estimated number of rows in the result set, the temporary table usage flag, the number of join operations, and the file sorting flag in the execution plan characteristics. S204. Calculate the low latency requirement score based on the SQL structure complexity of the abstract syntax tree; S205. Combine the computational intensity score, IO intensity score, memory intensity score, and low latency requirement score into an SQL profile vector.

[0029] In this embodiment, after constructing the Abstract Syntax Tree (AST) and extracting statistical and execution plan features, the system enters the SQL profile vector generation stage. The core task of this stage is to quantify the aforementioned features into four-dimensional scores, reflecting the query's requirements in terms of computing resources, input / output resources, memory resources, and latency sensitivity.

[0030] For computational intensity scoring, the system primarily calculates based on five metrics from the execution plan characteristics: the number of join operations, the number of aggregate functions, the number of sorting operations, the number of subqueries, and the estimated number of rows scanned. First, the system normalizes each metric: the number of join operations is truncated to a maximum value of 1.0 for every 8 joins; the number of aggregate functions is normalized to a maximum value of 5; the number of sorting operations to a maximum value of 4; the number of subqueries to a maximum value of 5; and the estimated number of rows scanned is normalized using a base-10 logarithmic normalization, with 10 million rows set as the reference point. After normalization, the system multiplies each of the five normalized values ​​by its respective weight coefficient: the number of join operations has the highest weight (0.35), the number of aggregate functions to 0.25, the number of sorting operations to 0.20, the number of subqueries to 0.10, and the estimated number of rows scanned to 0.10. The products are then summed, and finally, a truncation function is used to limit the result to the range of 0 to 1, yielding the final computational intensity score.

[0031] For the input / output density score, the system calculates based on the full table scan flag, estimated number of rows scanned, temporary table usage flag, number of tables, and SQL statement length. The full table scan flag is a Boolean value, set to 1 if the execution plan includes a full table scan, and 0 otherwise. The temporary table usage flag is also a Boolean value. The number of tables is normalized to the maximum value corresponding to every 6 tables. The SQL statement length is normalized to the maximum value corresponding to 5000 characters. The estimated number of rows scanned is also logarithmically normalized. After normalization, each item is multiplied by a weight: the full table scan flag has a weight of 0.40, the estimated number of rows scanned has a weight of 0.20, the temporary table usage flag has a weight of 0.25, the number of tables has a weight of 0.10, and the SQL statement length has a weight of 0.05. The sum is then truncated to the range of 0 to 1 using a truncation function to obtain the input / output density score.

[0032] For memory density scoring, the system calculates based on the estimated result set row count, temporary table usage flag, estimated scan row count, number of join operations, and file sorting flag. The estimated result set row count is logarithmically normalized, with 10 million rows as the reference point; the temporary table usage flag is a Boolean value; the estimated scan row count is also logarithmically normalized; the number of join operations is normalized to every 8 joins; the file sorting flag is a Boolean value, set to 1 if the execution plan includes file sorting, and 0 otherwise. Each normalized indicator is multiplied by its weight: estimated result set row count has a weight of 0.30, temporary table usage flag has a weight of 0.25, estimated scan row count has a weight of 0.20, number of join operations has a weight of 0.15, and file sorting flag has a weight of 0.10. The sum is then truncated to the range of 0 to 1 to obtain the memory density score.

[0033] For the low-latency requirement score, the system employs an evaluation logic opposite to the three dimensions mentioned above. This score does not directly calculate the query's resource consumption; instead, it assesses the query's sensitivity to latency based on its structural complexity. Specifically, the system uses 1.0 as the maximum score, then subtracts the weighted values ​​of five indicators: the number of join operations, the estimated number of rows scanned, the number of aggregate functions, the full table scan flag, and the temporary table usage flag. The number of join operations and the estimated number of rows scanned each account for 0.25 of the weight, the number of aggregate functions accounts for 0.20, and the full table scan flag and the temporary table usage flag each account for 0.15. Finally, a truncation function limits the result to the range of 0 to 1. Thus, the simpler the query structure and the lower the resource consumption, the closer its low-latency requirement score is to 1, indicating that the query has high requirements for response time and is suitable for processing on a standby machine with excellent latency characteristics. Conversely, the more complex the query structure, the closer its score is to 0, indicating that it is not suitable for deployment on a standby machine that requires extremely low latency.

[0034] After independently calculating the scores for the four dimensions, the system combines the computational intensity score, input / output intensity score, memory intensity score, and low-latency requirement score into a four-dimensional vector in a fixed order, which is the final structured SQL profile vector. This vector fully characterizes the resource requirements of the current query in four dimensions: CPU (Central Processing Unit) computing power, disk input / output throughput, memory capacity, and low-latency response. Based on this, the system further determines the primary type label of the query according to the distribution of the four scores and preset thresholds. For example, a computational intensity score of 0.5 or higher, and the highest of the four, is labeled as computationally intensive; a low-latency requirement score of 0.7 or higher is labeled as low-latency; two or more dimensions with scores of 0.4 or higher are labeled as mixed; otherwise, it is labeled as general. This provides type-level auxiliary reference for subsequent compatibility matching. Through this precise quantitative profile generation process, the system achieves a fundamental leap from coarse external label matching to refined semantic feature matching.

[0035] In one embodiment, S300 includes: S301. Calculate the CPU computing power index based on the number of CPU cores, clock speed, and generation coefficient of the standby machine. S302. Calculate the IO throughput index based on the standby machine's disk type coefficient, sequential read / write throughput, and random IOPS; S303. Calculate the memory capacity index based on the available memory capacity and memory bandwidth of the standby machine; S304. Calculate the low latency index based on the historical average response latency of the standby unit; S305. Combine the CPU computing power index, the IO throughput index, the memory capacity index, and the low latency index into an initial static capability profile vector; S306. Collect the actual execution time, throughput and resource utilization of each standby machine when processing various SQL profiles according to the preset cycle, and calculate the actual performance score. S307. Using the exponential moving average method, the actual capability performance score is smoothly fused with the initial static capability profile vector of the current standby machine to obtain the capability profile vector.

[0036] In this embodiment, in the initial stage of building the standby machine capability profile, the system establishes a static capability profile vector for each standby machine in the cluster. This vector consists of four dimensions: central processing unit (CPU) computing power index, input / output (IO) throughput index, memory capacity index, and low latency index, which respectively reflect the hardware and configuration capabilities of the standby machine in terms of computing, storage, memory, and response latency.

[0037] The calculation of the CPU computing power index comprehensively considers the number of CPU cores, clock speed (in gigahertz / GHz), and generation factor of the standby CPU. The generation factor is evaluated with reference to industry-standard performance evaluation benchmarks (such as the SPEC CPU benchmark) to reflect the microarchitectural performance differences between different generations of CPUs with the same number of cores and clock speed. The specific calculation formula is: multiply the number of cores by the clock speed, then multiply by the generation factor, and then divide the product by a preset benchmark reference value. This benchmark reference value represents the reference computing power level under standard configuration, thereby normalizing the computing power index within a reasonable range.

[0038] The input / output throughput index is calculated by combining three metrics: disk type coefficient, sequential read / write throughput (in megabytes per second, MB / s), and random read / write input / output operations per second (IOPS). The disk type coefficient is set based on the performance differences of the storage media. For example, solid-state drives (SSDs) with non-volatile memory express (NVMe) interfaces have the highest coefficient, followed by SSDs with Serial Advanced Technology Attachment (SATA) interfaces, then hard disk drives (HDDs) with Serial Attached SCSI (SAS) interfaces, while HDDs with SATA interfaces have the lowest coefficient. The system normalizes these three metrics and multiplies them by their corresponding weighting coefficients: disk type coefficient (0.40), sequential read / write throughput (0.30), and random read / write IOPS (0.30). The summation yields the input / output throughput index. This weighting distribution reflects the fact that disk type has the greatest decisive impact on overall input / output capabilities.

[0039] The memory capacity index is calculated based on the standby machine's available memory capacity (in gigabytes per second) and memory bandwidth (in gigabytes per second). The system divides the available memory capacity by a preset memory capacity benchmark and the memory bandwidth by a preset memory bandwidth benchmark, obtaining normalized capacity and bandwidth scores respectively. These scores are then multiplied by weights of 0.60 and 0.40 respectively before being summed. The capacity weight is higher because memory capacity has a more direct and significant impact on cache hit rate during query execution, while the bandwidth weight is slightly lower, primarily affecting data transfer efficiency in scenarios involving large result sets.

[0040] The low latency index is calculated based on the historical average response latency (in milliseconds, ms) of the standby machine. The system divides the preset reference latency by the sum of the historical average response latency and a very small positive number to obtain a dimensionless index value. When the actual latency of the standby machine is lower than the reference latency, the index is greater than 1, indicating that the latency performance is better than the reference; when the actual latency is higher than the reference latency, the index is less than 1, indicating that the latency performance is worse than the reference.

[0041] After completing independent calculations across the four dimensions, the system combines the CPU computing power index, I / O throughput index, memory capacity index, and low latency index into an initial static capability profile vector for the standby machine. This vector can be manually configured through a management interface or configuration file, making it particularly suitable for deployment scenarios where hardware configurations are known and remain stable over a long period.

[0042] During system operation, the aforementioned static profile vectors are continuously updated through a dynamic learning mechanism to ensure that the capability profile accurately reflects the performance changes of the standby machines under actual load. The system collects actual execution data from each standby machine when processing various SQL profile queries according to a preset evaluation period (e.g., every 15 minutes). Collected metrics include: average execution time (in milliseconds) for each type of SQL profile query, 95th percentile execution time, query throughput per second, and the overall average CPU utilization and average I / O latency of the standby machines. Based on this collected data, the system calculates the actual capability performance score for each standby machine across various dimensions at the end of each evaluation period. The CPU performance score is calculated by multiplying the standby machine's throughput for processing computationally intensive queries by the maximum throughput of the same type in the cluster, plus a CPU idle rate penalty. The I / O performance score is similar, but the penalty is based on I / O latency. The memory performance score is calculated by the ratio of cache hit rate to the reference maximum hit rate. The low-latency performance score is calculated by dividing the reference baseline latency by the average time taken for the standby machine to process low-latency queries.

[0043] After obtaining the actual performance score for the current period, the system uses an Exponential Moving Average (EMA) algorithm to smoothly merge it with the existing capability profile vector of the standby machine. For each dimension, the new capability index equals the learning rate multiplied by the actual performance score for the current period, plus one minus the learning rate multiplied by the original capability index. The learning rate is typically between 0.15 and 0.30; a larger value indicates a more sensitive profile to recent performance, while a smaller value indicates smoother profile changes. Through this exponentially weighted moving average method, the standby machine capability profile can automatically adapt to various dynamic factors such as hardware performance degradation, changes in resource contention, load pattern evolution, and configuration adjustments, without experiencing drastic fluctuations due to single abnormal fluctuations. Simultaneously, the system continuously monitors the changing trends of the capability indices for each dimension. When the capability index of a certain dimension declines beyond a preset threshold for several consecutive evaluation periods, an alarm is automatically triggered to alert the administrator to potential hardware failures or abnormal resource contention issues with the standby machine.

[0044] In one embodiment, S500 includes: S501. Obtain the business requirement constraints of the tenant or business configuration to which the current query belongs, the business requirement constraints including the maximum response latency threshold and the standby replication latency threshold; S502. Compare the replication delay of each standby machine with the standby machine replication delay threshold, and remove standby machines whose replication delay exceeds the replication delay threshold to obtain the first standby machine candidate sequence; S503. Based on the real-time status information of each standby machine, estimate the expected response time of the SQL query on each standby machine; S504. Compare the expected response time of each standby machine with the maximum response delay threshold, and eliminate standby machines whose expected response time exceeds the maximum response delay threshold to obtain the second standby machine candidate sequence. S505. Compare the overall matching degree of each backup machine with the preset compatibility threshold, and remove backup machines with an overall matching degree lower than the preset compatibility threshold to obtain the third backup machine candidate sequence. S506. Generate a candidate standby set based on the first standby candidate sequence, the second standby candidate sequence, and the third standby candidate sequence.

[0045] In this embodiment, after calculating the comprehensive matching degree between the SQL profile vector and the capability profile vectors of each standby machine, the system enters the business constraint filtering and candidate standby machine set generation stage. The core purpose of this stage is to exclude standby machines that, although the matching degree is acceptable, cannot meet the business-level service quality requirements, and at the same time, to eliminate standby machines with severely mismatched capabilities, thereby narrowing down the selection range.

[0046] The system first obtains the routing requirements policy at the tenant or business level to which the current query belongs. In a multi-tenant architecture, different tenants or business lines often have differentiated service quality requirements, which are pre-configured in the system in the form of business requirement constraints. These business requirement constraints mainly include two categories: the first is the maximum response latency threshold, which is the longest tolerable execution time for the query; standby machines exceeding this threshold should not be considered even if they possess other advantages. The second is the standby machine replication latency threshold, which is the upper limit of the data lag between the standby machine and the primary database during primary-standby replication. This threshold directly determines the data freshness of the query results and is particularly critical for business scenarios with high real-time requirements.

[0047] The system then begins a layer-by-layer filtering process. The first layer of filtering is based on standby replication latency: the system obtains the current primary / standby replication latency value for each standby machine from the real-time monitoring module, compares it with the standby replication latency threshold configured for the current business, and removes any standby machine whose replication latency exceeds the threshold. Through this layer of filtering, the system ensures that the data returned by the candidate standby machines meets the minimum requirements for data timeliness for the business, avoiding outdated query results due to replication lag. The standby machines that are not removed form the first standby machine candidate sequence.

[0048] The second layer of filtering is based on expected response time. The system estimates the potential response time of a current SQL query executed on each standby machine based on the real-time status information of each standby machine. This estimation process comprehensively considers real-time and historical factors such as the standby machine's current CPU utilization, memory utilization, I / O queue depth, and the average execution time of similar queries on that standby machine, using a lightweight regression model or empirical formula to estimate the expected response time. Subsequently, the system compares the expected response time of each standby machine with the maximum response latency threshold configured for the business. Standby machines with expected response times exceeding this threshold are eliminated. Through this filtering layer, the system ensures that even if a standby machine's capability profile highly matches the SQL profile, it will not be included in the candidate pool if the current load is heavy or resources are scarce, preventing the query from being completed within the specified time. The remaining standby machines form the second standby machine candidate sequence.

[0049] The third layer of filtering is based on a comprehensive matching degree threshold. The system compares the comprehensive matching degree between each standby machine and the current SQL query, calculated previously, with a preset compatibility matching threshold (typically between 0.4 and 0.6). This threshold represents the minimum requirement for "basic compatibility" between the standby machine's capabilities and the SQL requirements. Standby machines below this threshold are considered fundamentally unsuitable for handling this type of query; for example, routing computationally intensive queries to standby machines with extremely weak CPU capabilities, or routing I / O intensive queries to mechanical hard drive standby machines. Even if these standby machines pass the first two layers of business constraint filtering, they do not possess the basic conditions to provide efficient processing and are therefore eliminated. The remaining standby machines form the third standby machine candidate sequence.

[0050] Finally, the system takes the intersection of the three candidate sequences, that is, the standby machines that simultaneously meet the three conditions of replication latency meeting the target, expected response time meeting the target, and overall matching degree not lower than the threshold constitute the final candidate standby machine set. It should be noted that the execution order of the three-layer filtering can be flexibly adjusted according to the actual deployment strategy, but the final intersection result is equivalent to simultaneously performing joint screening on the constraints of the three dimensions. If the candidate standby machine set is empty, the system enters the degradation processing flow; if it is not empty, the set will be used as the input for the subsequent secondary dynamic optimization. Based on this, the system further combines the real-time status to perform fine-grained scoring and ranking, and selects the optimal target standby machine.

[0051] In one embodiment, S600 includes: S601. Determine whether the candidate standby machine set is empty; S602. If the candidate standby machine set is not empty and there are multiple candidate standby machines, then a second dynamic screening is performed. S603. Obtain the real-time CPU utilization, memory utilization, request queue length and replication latency of each candidate standby machine, and calculate the real-time status score based on the real-time CPU utilization, memory utilization, request queue length and replication latency. S604. For each candidate backup machine, the comprehensive matching degree and the real-time status score are weighted and summed according to a preset weight to obtain the final routing score.

[0052] In this embodiment, after completing the business constraint filtering and matching threshold screening and obtaining a non-empty candidate standby machine set, the system first determines whether the candidate standby machine set is empty. If the set is empty, it directly enters the degradation processing flow; if the set is not empty, it further determines the number of candidate standby machines. When there is only one standby machine in the candidate standby machine set, that standby machine is directly determined as the target standby machine, without the need for secondary dynamic screening. However, when there are multiple standby machines in the candidate standby machine set, the system initiates a secondary dynamic selection mechanism to select the optimal machine under the current load conditions from multiple standby machines that have met the basic matching requirements and business constraints.

[0053] The core of secondary dynamic optimization lies in introducing real-time status information as a supplementary evaluation dimension. This is because the overall matching degree reflects the degree of fit between the SQL requirement and the static and historical capabilities of the standby machine, while the real-time status determines whether the standby machine can handle the query with optimal performance at the current moment. The system obtains four key real-time status indicators for each candidate standby machine from the real-time monitoring module: CPU utilization, memory utilization, request queue length, and primary-standby replication latency. The request queue length reflects the number of pending requests currently backed up on the standby machine; a longer queue indicates a busier standby machine. Replication latency reflects the current status of the standby machine in terms of data freshness. Even if the replication latency threshold has been passed in the preliminary filtering stage, it still needs to be considered comprehensively during the final optimization to prioritize the standby machine with lower latency.

[0054] After obtaining the above indicators, the system calculates a comprehensive real-time status score for each candidate standby machine. The score is calculated as follows: First, the idle rate of CPU utilization and memory utilization is calculated by subtracting the utilization rate from 1. For the request queue length, the system divides it by a preset maximum queue length reference and truncates it to a value not exceeding 1 to obtain the normalized queue length. Then, this value is subtracted from 1 to obtain the queue idle rate. Similarly, for replication latency, the system divides it by a preset maximum replication latency reference and truncates it to a value not exceeding 1 to obtain the normalized replication latency. This value is then subtracted from 1 to obtain the replication latency idle rate. Subsequently, the system multiplies these four idle rate indicators by preset weighting coefficients and sums them. The weighting for CPU idle rate is 0.30, memory idle rate is 0.20, queue idle rate is 0.25, and replication latency idle rate is 0.25. The resulting real-time status score ranges from 0 to 1, with higher values ​​indicating a more idle and suitable state for the standby machine to handle new queries.

[0055] After obtaining the real-time status score of each candidate standby machine, the system weights and merges it with the previously calculated comprehensive matching score to generate the final routing score. Specifically, the comprehensive matching score is multiplied by a balancing coefficient β, the real-time status score is multiplied by 1 and subtracted from β, and then the two products are added together. The balancing coefficient β is used to adjust the relative importance of profile matching quality and real-time load status in the final decision; its default value is set to 0.5, meaning each has half the weight. For scenarios with strong business constraints and a greater emphasis on "sending the correct query to the correct standby machine," the β value can be appropriately increased to above 0.7 to give the matching score a larger weight. For scenarios with drastic load fluctuations and a greater emphasis on "avoiding sending queries to currently busy standby machines," the β value can be appropriately decreased. Finally, the system sorts all standby machines in the candidate standby machine set from highest to lowest according to their final routing scores. The standby machine with the highest score is the optimal target standby machine at the current moment after comprehensively considering capability matching and real-time load, and the routing execution module sends the SQL statement to that standby machine for execution.

[0056] In one embodiment, determining whether the candidate standby machine set is empty includes: If the candidate standby machine set is empty, or the target standby machine fails, a degradation processing strategy is executed. Based on the comprehensive matching degree and business requirement constraints, a secondary target backup machine is selected from the candidate backup machine set; If the candidate standby set has no secondary target standby machine, the SQL query request will be rolled back to the primary database for execution. If the master database rejects the SQL request, the SQL request is placed in the waiting queue.

[0057] In this embodiment, during the process of screening the candidate standby machine set and selecting the target standby machine, the system will determine whether the candidate standby machine set is empty at key decision points. When the system detects that the candidate standby machine set is empty, it means that no standby machine can simultaneously meet the minimum threshold for comprehensive matching and the business requirement constraints. This situation usually occurs when the overall cluster load is high, replication latency is generally exceeding the standard, or the current SQL query type is relatively special and the capability profiles of all standby machines cannot meet the basic compatibility standard. In addition, even if the candidate standby machine set is not empty, if the target standby machine finally selected by the system fails before routing is executed, such as standby instance crash, network connection interruption, or health check failure, the degradation processing logic will also be triggered. These two triggering conditions are collectively referred to as the route unreachable scenario. In such scenarios, the system will no longer force routing, but will instead start a tiered elastic degradation strategy to ensure that the query request can be executed with the highest possible success rate.

[0058] The degradation handling strategy attempts multiple fallback options sequentially according to a predefined priority order. The system's first degradation choice is to find a suboptimal compatible standby machine from the original candidate standby machine set. Specifically, if the candidate standby machine set was not originally empty, but the optimal standby machine failed, the system selects the standby machine with the second-highest overall matching degree from the remaining candidate standby machines, which is still higher than the compatibility matching threshold, as the secondary target standby machine. If the original candidate standby machine set was originally empty, the system will appropriately relax the filtering conditions, such as lowering the compatibility matching threshold by one step from the original setting, or temporarily ignoring non-critical business constraints, and then re-evaluate all available standby machines, selecting the standby machine with the highest matching degree that exceeds the lowered threshold as the secondary target standby machine. It should be noted that although the suboptimal compatible standby machine is not as good as the optimal standby machine in terms of overall matching degree, it is still within the basic compatibility range and has the basic capability to handle the query.

[0059] If the system still cannot find any available suboptimal compatible standby machine after relaxing the conditions, a second-level degradation strategy is executed, which involves rolling back the current SQL query request to the primary database for execution. The primary database handles all write operations for the cluster and typically does not handle read queries. However, in extreme cases where standby resources are completely unavailable, the system allows read queries to be temporarily routed to the primary database to ensure business continuity is not affected. Before forwarding a query to the primary database, the system performs necessary checks, such as whether the primary database's current load is within acceptable limits and whether the query is indeed read-only, to avoid mistakenly sending requests with modification intentions to the primary database. Furthermore, in some scenarios, tenant or business policies may explicitly prohibit rolling back queries to the primary database; in such cases, the system will skip this strategy and proceed directly to the next level.

[0060] When the primary database refuses to accept a query request due to excessive load, resource scarcity, or policy restrictions, the system executes a third-level degradation strategy, placing the SQL query request into a waiting queue. The waiting queue employs a first-in, first-out (FIFO) queuing mechanism, where requests are temporarily stored and routing decisions are periodically retried. Whenever a new standby server status update arrives or a routing cycle is triggered, the system re-executes the candidate standby server screening and selection process, attempting to allocate waiting requests to newly available standby server resources. To prevent requests from waiting indefinitely in the queue, the system sets a timeout for each queued request. If a request waits in the queue for longer than the preset maximum queuing time, the system directly returns a query failure or timeout error response to the client, preventing the client from exhausting connection resources due to indefinite blocking. Through this tiered elastic degradation strategy, the system can still maximize the processing of query requests even under abnormal conditions such as standby server resource scarcity, missing matching candidates, or high pressure on the primary database, significantly improving the overall cluster availability and robustness under high load and failure scenarios.

[0061] In one embodiment, a query statement backup intelligent routing device is provided, which corresponds one-to-one with the query statement backup intelligent routing method described in the above embodiments. (Refer to...) Figure 3 , Figure 3 This is a schematic diagram of the functional modules of a preferred embodiment of the intelligent backup machine routing device for query statements of the present invention. The modules include a request parsing module 10, a request profiling module 20, a backup machine profiling module 30, a multi-dimensional matching module 40, a backup machine filtering module 50, a secondary filtering module 60, and a target backup machine module 70. Detailed descriptions of each functional module are as follows: The request parsing module 10 is used to receive SQL query requests, parse the SQL statements of the SQL query requests, construct an abstract syntax tree based on the parsing results, and extract the statistical features and execution plan features of the abstract syntax tree. The request profiling module 20 is used to generate an SQL profiling vector through the abstract syntax tree, statistical features, and execution plan features; The standby machine profiling module 30 is used to pre-build an initial static capability profile vector for each standby machine, and dynamically and smoothly update the initial static capability profile vector based on historical execution data to obtain a capability profile vector. The multi-dimensional matching module 40 is used to perform multi-dimensional matching between the SQL profile vector and the capability profile vector of each standby machine, calculate the matching score of each dimension, dynamically allocate the weight of each dimension according to the resource demand characteristics of the SQL profile vector, and perform a weighted sum of the matching score of each dimension and the corresponding weight to obtain the comprehensive matching degree. The standby machine screening module 50 is used to filter standby machines that do not meet the conditions according to business requirements, and to select a set of candidate standby machines based on the comprehensive matching degree. The secondary filtering module 60 is used to perform secondary dynamic filtering based on the real-time status information of each candidate backup machine if the candidate backup machine set is not empty, and to calculate the final routing score. The target standby module 70 is used to determine the target standby machine based on the final routing score and send the SQL statement to the target standby machine for execution.

[0062] In one embodiment, the request parsing module 10 includes: Receive SQL query requests initiated by clients and obtain the SQL statements from the SQL query requests; Lexical analysis is performed on the SQL statement, and the character sequence of the SQL statement is segmented according to the SQL lexical strategy to obtain a Token stream; The token stream is parsed, and the tokens in the token stream are combined according to the SQL syntax strategy to obtain syntactic units; Construct an abstract syntax tree based on the Token stream and the syntax units; The abstract syntax tree is parsed to extract statistical features and execution plan features.

[0063] In one embodiment, the request profiling module 20 includes: The computational intensity score is calculated based on the number of join operations, aggregate functions, sorting operations, subqueries, and estimated number of rows scanned in the execution plan features. The IO intensity score is calculated based on the full table scan flag, estimated number of scanned rows, temporary table usage flag, number of tables in the execution plan characteristics, and the SQL length in the statistical characteristics. Calculate the memory density score based on the estimated number of rows in the result set, the temporary table usage flag, the number of join operations, and the file sorting flag in the execution plan characteristics. Calculate the low latency requirement score based on the SQL structure complexity of the abstract syntax tree; The computational intensity score, IO intensity score, memory intensity score, and low latency requirement score are combined into an SQL profile vector.

[0064] In one embodiment, the backup machine profiling module 30 includes: The CPU computing power index is calculated based on the number of CPU cores, clock speed, and generation coefficient of the standby CPU. Calculate the IO throughput index based on the standby machine's disk type coefficient, sequential read / write throughput, and random IOPS; The memory capacity index is calculated based on the available memory capacity and memory bandwidth of the standby machine; The low latency index is calculated based on the historical average response latency of the backup unit. The CPU computing power index, the IO throughput index, the memory capacity index, and the low latency index are combined to form an initial static capability profile vector; Collect the actual execution time, throughput and resource utilization of each standby machine when processing various SQL profiles according to a preset period, and calculate the actual performance score. The actual capability performance score is smoothly fused with the initial static capability profile vector of the current standby machine using the exponential moving average method to obtain the capability profile vector.

[0065] In one embodiment, the standby machine screening module 50 includes: Obtain the business requirement constraints of the tenant or service configuration to which the current query belongs, including the maximum response latency threshold and the standby replication latency threshold; The replication latency of each standby machine is compared with the standby machine replication latency threshold, and standby machines whose replication latency exceeds the replication latency threshold are eliminated to obtain the first standby machine candidate sequence; Based on the real-time status information of each standby machine, the expected response time of the SQL query on each standby machine is estimated; The expected response time of each backup machine is compared with the maximum response delay threshold, and backup machines whose expected response time exceeds the maximum response delay threshold are eliminated to obtain the second backup machine candidate sequence; The overall matching degree of each backup machine is compared with the preset compatibility threshold, and backup machines with an overall matching degree lower than the preset compatibility threshold are eliminated to obtain the third backup machine candidate sequence. A candidate standby set is generated based on the first standby candidate sequence, the second standby candidate sequence, and the third standby candidate sequence.

[0066] In one embodiment, the secondary screening module 60 includes: Determine whether the candidate standby machine set is empty; If the candidate standby machine set is not empty and there are multiple candidate standby machines, then a second dynamic screening is performed; Obtain the real-time CPU utilization, memory utilization, request queue length, and replication latency of each candidate standby machine, and calculate the real-time status score based on the real-time CPU utilization, memory utilization, request queue length, and replication latency; For each candidate backup machine, the comprehensive matching degree and the real-time status score are weighted and summed according to preset weights to obtain the final routing score.

[0067] In one embodiment, determining whether the candidate standby machine set is empty includes: If the candidate standby machine set is empty, or the target standby machine fails, a degradation processing strategy is executed. Based on the comprehensive matching degree and business requirement constraints, a secondary target backup machine is selected from the candidate backup machine set; If the candidate standby set has no secondary target standby machine, the SQL query request will be rolled back to the primary database for execution. If the master database rejects the SQL request, the SQL request is placed in the waiting queue.

[0068] For specific limitations regarding the intelligent routing device for query statement backup, please refer to the aforementioned limitations on the intelligent routing method for query statement backup, which will not be repeated here. Each module in the aforementioned intelligent routing device for query statement backup can be implemented entirely or partially through software, hardware, or a combination thereof. These modules can be embedded in or independent of the processor in the computer device in hardware form, or stored in the memory of the computer device in software form, so that the processor can call and execute the operations corresponding to each module.

[0069] In one embodiment, a computer device is provided, which may be a server, and its internal structure diagram may be as follows: Figure 4 As shown. The computer device includes a processor, memory, network interface, and database connected via a system bus. The processor provides determination and control capabilities. The memory includes non-volatile and / or volatile storage media and internal memory. The non-volatile storage media stores the operating system, computer programs, and database. The internal memory provides an environment for the operation of the operating system and computer programs in the non-volatile storage media. The network interface is used to communicate with external clients via a network connection. When the computer program is executed by the processor, it implements the functions or steps of a query statement standby intelligent routing method on the server side.

[0070] In one embodiment, a computer device is provided, which may be a client, and its internal structure diagram may be as follows: Figure 5 As shown, the computer device includes a processor, memory, network interface, display screen, and input devices connected via a system bus. The processor provides determination and control capabilities. The memory includes non-volatile storage media and internal memory. The non-volatile storage media stores the operating system and computer programs. The internal memory provides an environment for the operation of the operating system and computer programs in the non-volatile storage media. The network interface is used to communicate with an external server via a network connection. When executed by the processor, the computer program implements the client-side functions or steps of a query statement standby intelligent routing method.

[0071] In one embodiment, a computer device is provided, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to perform the following steps: Receive SQL query requests, parse the SQL statements in the SQL query requests, construct an abstract syntax tree based on the parsing results, and extract the statistical features and execution plan features of the abstract syntax tree; SQL profile vectors are generated using the abstract syntax tree, statistical features, and execution plan features. An initial static capability profile vector is pre-constructed for each standby machine, and the initial static capability profile vector is dynamically and smoothly updated based on historical execution data to obtain a capability profile vector. The SQL profile vector is matched with the capability profile vector of each standby machine in multiple dimensions, the matching score of each dimension is calculated, and the weight of each dimension is dynamically allocated according to the resource requirement characteristics of the SQL profile vector. The matching score of each dimension and the corresponding weight are weighted and summed to obtain the comprehensive matching degree. Based on business requirements, standby machines that do not meet the conditions are filtered out, and a set of candidate standby machines is selected based on the comprehensive matching degree. If the candidate standby set is not empty, then a second dynamic filtering is performed based on the real-time status information of each candidate standby to calculate the final routing score. The target standby machine is determined based on the final routing score, and the SQL statement is sent to the target standby machine for execution.

[0072] In one embodiment, a computer-readable storage medium is provided, which may be non-volatile or volatile, and a computer program is stored thereon, which, when executed by a processor, performs the following steps: Receive SQL query requests, parse the SQL statements in the SQL query requests, construct an abstract syntax tree based on the parsing results, and extract the statistical features and execution plan features of the abstract syntax tree; SQL profile vectors are generated using the abstract syntax tree, statistical features, and execution plan features. An initial static capability profile vector is pre-constructed for each standby machine, and the initial static capability profile vector is dynamically and smoothly updated based on historical execution data to obtain a capability profile vector. The SQL profile vector is matched with the capability profile vector of each standby machine in multiple dimensions, the matching score of each dimension is calculated, and the weight of each dimension is dynamically allocated according to the resource requirement characteristics of the SQL profile vector. The matching score of each dimension and the corresponding weight are weighted and summed to obtain the comprehensive matching degree. Based on business requirements, standby machines that do not meet the conditions are filtered out, and a set of candidate standby machines is selected based on the comprehensive matching degree. If the candidate standby set is not empty, then a second dynamic filtering is performed based on the real-time status information of each candidate standby to calculate the final routing score. The target standby machine is determined based on the final routing score, and the SQL statement is sent to the target standby machine for execution.

[0073] It should be noted that the functions or steps that can be implemented by the computer-readable storage medium or computer device described above can be referred to the relevant descriptions on the server side and client side in the foregoing method embodiments. To avoid repetition, they will not be described one by one here.

[0074] Those skilled in the art will understand that all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware. The computer program can be stored in a non-volatile computer-readable storage medium, and when executed, it can include the processes of the embodiments of the above methods. Any references to memory, storage, databases, or other media used in the embodiments provided in this application can include non-volatile and / or volatile memory. Non-volatile memory can include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM), or flash memory. Volatile memory can include random access memory (RAM) or external cache memory. By way of illustration and not limitation, RAM is available in various forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), dual data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronous link DRAM (SLDRAM), Rambus direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM), etc.

[0075] Those skilled in the art will clearly understand that, for the sake of convenience and brevity, the above-described division of functional units and modules is used as an example. In practical applications, the above functions can be assigned to different functional units and modules as needed, that is, the internal structure of the device can be divided into different functional units or modules to complete all or part of the functions described above.

[0076] It should be noted that any AI models, software tools, or components not belonging to this company appearing in the embodiments of this application are merely illustrative examples and do not represent actual use. All user personal information involved in the embodiments of this application has been authorized (with the knowledge and consent) by the relevant parties or has been fully authorized by all parties, and the executing entity may obtain it through various legal and compliant means. The collection, storage, use, processing, transmission, provision, and disclosure of the information, data, and signals involved all comply with relevant laws and regulations and do not violate public order and good morals.

[0077] The above-described embodiments are only used to illustrate the technical solutions of the present invention, and are not intended to limit it. Although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of the present invention, and should all be included within the protection scope of the present invention.

Claims

1. A method for intelligent routing of standby machines for query statements, characterized in that, Includes the following steps: Receive SQL query requests, parse the SQL statements in the SQL query requests, construct an abstract syntax tree based on the parsing results, and extract the statistical features and execution plan features of the abstract syntax tree; SQL profile vectors are generated using the abstract syntax tree, statistical features, and execution plan features. An initial static capability profile vector is pre-constructed for each standby machine, and the initial static capability profile vector is dynamically and smoothly updated based on historical execution data to obtain a capability profile vector. The SQL profile vector is matched with the capability profile vector of each standby machine in multiple dimensions, the matching score of each dimension is calculated, and the weight of each dimension is dynamically allocated according to the resource requirement characteristics of the SQL profile vector. The matching score of each dimension and the corresponding weight are weighted and summed to obtain the comprehensive matching degree. Based on business requirements, standby machines that do not meet the conditions are filtered out, and a set of candidate standby machines is selected based on the comprehensive matching degree. If the candidate standby set is not empty, then a second dynamic filtering is performed based on the real-time status information of each candidate standby to calculate the final routing score. The target standby machine is determined based on the final routing score, and the SQL statement is sent to the target standby machine for execution.

2. The query statement standby intelligent routing method as described in claim 1, characterized in that, The process of receiving an SQL query request, parsing the SQL statement in the SQL query request, constructing an abstract syntax tree based on the parsing results, and extracting the statistical features and execution plan features of the abstract syntax tree includes: Receive SQL query requests initiated by clients and obtain the SQL statements from the SQL query requests; Lexical analysis is performed on the SQL statement, and the character sequence of the SQL statement is segmented according to the SQL lexical strategy to obtain a Token stream; The token stream is parsed, and the tokens in the token stream are combined according to the SQL syntax strategy to obtain syntactic units; Construct an abstract syntax tree based on the Token stream and the syntax units; The abstract syntax tree is parsed to extract statistical features and execution plan features.

3. The query statement standby intelligent routing method as described in claim 1, characterized in that, The process of generating an SQL profile vector using the abstract syntax tree, statistical features, and execution plan features includes: The computational intensity score is calculated based on the number of join operations, aggregate functions, sorting operations, subqueries, and estimated number of rows scanned in the execution plan features. The IO intensity score is calculated based on the full table scan flag, estimated number of scanned rows, temporary table usage flag, number of tables in the execution plan characteristics, and the SQL length in the statistical characteristics. Calculate the memory density score based on the estimated number of rows in the result set, the temporary table usage flag, the number of join operations, and the file sorting flag in the execution plan characteristics. Calculate the low latency requirement score based on the SQL structure complexity of the abstract syntax tree; The computational intensity score, IO intensity score, memory intensity score, and low latency requirement score are combined into an SQL profile vector.

4. The intelligent routing method for standby machines in query statements as described in claim 1, characterized in that, The process of pre-constructing an initial static capability profile vector for each standby machine and dynamically and smoothly updating the initial static capability profile vector based on historical execution data to obtain a capability profile vector includes: The CPU computing power index is calculated based on the number of CPU cores, clock speed, and generation coefficient of the standby CPU. Calculate the IO throughput index based on the standby machine's disk type coefficient, sequential read / write throughput, and random IOPS; The memory capacity index is calculated based on the available memory capacity and memory bandwidth of the standby machine; The low latency index is calculated based on the historical average response latency of the backup unit. The CPU computing power index, the IO throughput index, the memory capacity index, and the low latency index are combined to form an initial static capability profile vector; Collect the actual execution time, throughput and resource utilization of each standby machine when processing various SQL profiles according to a preset period, and calculate the actual performance score. The actual capability performance score is smoothly fused with the initial static capability profile vector of the current standby machine using the exponential moving average method to obtain the capability profile vector.

5. The intelligent routing method for standby machines in query statements as described in claim 1, characterized in that, Based on business requirements, standby machines that do not meet the conditions are filtered out, and a candidate standby machine set is selected based on the comprehensive matching degree, including: Obtain the business requirement constraints of the tenant or service configuration to which the current query belongs, including the maximum response latency threshold and the standby replication latency threshold; The replication latency of each standby machine is compared with the standby machine replication latency threshold, and standby machines whose replication latency exceeds the replication latency threshold are eliminated to obtain the first standby machine candidate sequence; Based on the real-time status information of each standby machine, the expected response time of the SQL query on each standby machine is estimated; The expected response time of each backup machine is compared with the maximum response delay threshold, and backup machines whose expected response time exceeds the maximum response delay threshold are eliminated to obtain the second backup machine candidate sequence; The overall matching degree of each backup machine is compared with the preset compatibility threshold, and backup machines with an overall matching degree lower than the preset compatibility threshold are eliminated to obtain the third backup machine candidate sequence. A candidate standby set is generated based on the first standby candidate sequence, the second standby candidate sequence, and the third standby candidate sequence.

6. The intelligent routing method for standby machines in query statements as described in claim 1, characterized in that, If the candidate backup set is not empty, a secondary dynamic filtering is performed based on the real-time status information of each candidate backup to calculate the final routing score, including: Determine whether the candidate standby machine set is empty; If the candidate standby machine set is not empty and there are multiple candidate standby machines, then a second dynamic screening is performed; Obtain the real-time CPU utilization, memory utilization, request queue length, and replication latency of each candidate standby machine, and calculate the real-time status score based on the real-time CPU utilization, memory utilization, request queue length, and replication latency; For each candidate backup machine, the comprehensive matching degree and the real-time status score are weighted and summed according to preset weights to obtain the final routing score.

7. The intelligent routing method for standby machines in query statements as described in claim 1 or 6, characterized in that, Determining whether the candidate standby machine set is empty includes: If the candidate standby machine set is empty, or the target standby machine fails, a degradation processing strategy is executed. Based on the comprehensive matching degree and business requirement constraints, a secondary target backup machine is selected from the candidate backup machine set; If the candidate standby set has no secondary target standby machine, the SQL query request will be rolled back to the primary database for execution. If the master database rejects the SQL request, the SQL request is placed in the waiting queue.

8. A query statement standby intelligent routing device, characterized in that, The standby intelligent routing device for the query statement includes: The request parsing module is used to receive SQL query requests, parse the SQL statements in the SQL query requests, construct an abstract syntax tree based on the parsing results, and extract the statistical features and execution plan features of the abstract syntax tree; The request profiling module is used to generate an SQL profiling vector using the abstract syntax tree, statistical features, and execution plan features. The standby machine profiling module is used to pre-build an initial static capability profile vector for each standby machine, and dynamically and smoothly update the initial static capability profile vector based on historical execution data to obtain a capability profile vector. The multi-dimensional matching module is used to perform multi-dimensional matching between the SQL profile vector and the capability profile vector of each standby machine, calculate the matching score of each dimension, dynamically allocate the weight of each dimension according to the resource requirement characteristics of the SQL profile vector, and perform a weighted sum of the matching scores of each dimension and the corresponding weights to obtain the comprehensive matching degree. The standby machine screening module is used to filter standby machines that do not meet the conditions according to business requirements, and to select a set of candidate standby machines based on the comprehensive matching degree. The secondary filtering module is used to perform secondary dynamic filtering based on the real-time status information of each candidate backup machine if the candidate backup machine set is not empty, and to calculate the final routing score. The target standby module is used to determine the target standby machine based on the final routing score and send the SQL statement to the target standby machine for execution.

9. A computer device, characterized in that, The computer device includes a memory, a processor, and a query statement standby intelligent routing program stored in the memory and executable on the processor. When the query statement standby intelligent routing program is executed by the processor, it implements the steps of the query statement standby intelligent routing method as described in any one of claims 1-7.

10. A computer-readable storage medium, characterized in that, The storage medium stores a query statement standby intelligent routing program, which, when executed by the processor, implements the steps of the query statement standby intelligent routing method as described in any one of claims 1-7.

Images