Database access adaptive routing method, system, and storage medium

Abstract

The application relates to the technical field of large Internet and artificial intelligence, and in particular to a database access adaptive routing method and system and a storage medium, which solve the problem that it is difficult to meet the dynamic change of business requirements in a high-concurrency scenario. The method comprises the following steps: performing morphological, syntactic and semantic analysis on a database access request to generate a structured request analysis report; processing the request analysis report to generate a feature vector; collecting the running state data of each node in a database cluster to form a cluster state snapshot; inputting the feature vector and the cluster state snapshot into a preset AI model to make a routing decision, calculating the generation value of each candidate node through a preset optimization objective function, selecting the candidate node with the minimum generation value as a target routing node, and outputting the target routing node and an execution strategy; and based on the execution strategy, adaptively optimizing the database access request, and sending the optimized database access request to the target routing node for execution.

Description

Technical Field

[0001] This application relates to the fields of big data and artificial intelligence technologies, and in particular to an adaptive routing method, system and storage medium for database access. Background Technology

[0002] In business scenarios across industries such as internet, finance, and e-commerce, databases (especially relational databases) face increasingly severe pressure from high-concurrency access as user scale expands and business complexity increases. In these scenarios, the database often becomes the performance bottleneck of the entire business system, manifesting as increased query response latency, a surge in the number of slow queries, and database connection exhaustion.

[0003] To alleviate the pressure of high concurrency in databases, existing technologies typically employ read-write separation, database sharding, and other implementations for request distribution. However, these implementations have significant drawbacks in practical applications, primarily the static nature of routing strategies. Most existing implementations process requests based on preset, fixed rules (such as consistently routing read requests to slave databases), which easily leads to uneven load distribution within the cluster. This results in some nodes being overloaded while others are idle, limiting the overall performance improvement of the database cluster and making it difficult to meet the dynamically changing business needs in high-concurrency scenarios. Summary of the Invention

[0004] This application provides a database access adaptive routing method, system, and storage medium to solve the technical problem that traditional embodiments are prone to uneven load distribution within the cluster, making it difficult to meet the dynamically changing business needs in high-concurrency scenarios.

[0005] An adaptive routing method for database access includes: Receive database access requests sent by the application layer, and perform lexical, syntactic and semantic analysis on the database access requests to generate a structured request parsing report; Based on the request parsing report, query feature quantification is performed to generate a feature vector containing multi-dimensional query feature parameters. Collect the running status data of each node in the database cluster to form a cluster status snapshot; The feature vector and the cluster state snapshot are input into a preset AI model for routing decision-making. The cost value of each candidate node is calculated through a preset optimization objective function, and the candidate node with the smallest cost value is selected as the target routing node. The target routing node and execution strategy are then output. Based on the execution strategy, the database access request is adaptively optimized, and the optimized database access request is sent to the target routing node for execution.

[0006] Furthermore, the step of parsing the database access request and generating a structured request parsing report includes: The semantic analysis is used to extract the query type, operation object, and filtering conditions from the database access request; Parse the transaction context of the database access request to determine the transaction isolation level requirements and read / write dependencies; Identify the access pattern of the database access request, including the request frequency, whether it is a periodic query, and the data access range.

[0007] Furthermore, the multidimensional query feature parameters include: Query complexity score based on SQL syntax tree depth and data scan range; The expected execution time is predicted based on historical execution data; Data popularity is determined based on access frequency; And consistency weights that characterize the consistency level of a transaction, including strong consistency, eventual consistency, and latency-allowed levels.

[0008] Furthermore, the collection of operational status data for each node in the database cluster includes: Real-time collection of runtime status data of the master database, slave database, shard nodes and cache instances in the database cluster; The operational status data includes CPU utilization, IO wait time, current number of connections, request queue depth, and memory usage. The collection period for the operational status data is dynamically adjusted based on the concurrency of the database access requests.

[0009] Furthermore, the optimization objective function is: T = α * T_query + β * T_queue; Where T_query is the expected execution time of the query, T_queue is the waiting time of the node queue, and α and β are dynamic weights.

[0010] Furthermore, the adaptive optimization processing of the database access request includes: Identify similar SQL requests received within a preset time window, and generate merged batch query statements based on the similar SQL requests; Based on the configuration characteristics of the target routing node, the corresponding native database index hints are injected into the database access request.

[0011] Furthermore, the method also includes: Collect end-to-end execution data corresponding to the database access request. The end-to-end execution data includes: the feature vector, the routing identifier information of the target routing node, the actual execution time of the database access request on the target routing node, and the execution result status. A feedback dataset is constructed based on the full-link execution data, and the model parameters of the AI ​​model are updated using the gradient descent algorithm.

[0012] A database access adaptive routing system includes: The intelligent database proxy layer is used to receive database access requests sent by the application layer, parse the database access requests, and generate a structured request parsing report. The AI-driven load analysis and routing module is used to perform query feature quantification processing based on the request parsing report to generate a feature vector containing multi-dimensional query feature parameters; and to input the feature vector and the obtained cluster state snapshot into a preset AI model for routing decision-making, calculate the cost value of each candidate node through a preset optimization objective function, select the candidate node with the smallest cost value as the target routing node, and output the target routing node and execution strategy. The database cluster interaction module is used to collect the running status data of each node in the database cluster to form a snapshot of the cluster status. An adaptive query optimization module is used to adaptively optimize the database access request based on the execution strategy. The database cluster interaction module is also used to send the optimized database access request to the target routing node for execution.

[0013] Furthermore, it also includes: The feedback learning module is used to collect end-to-end execution data corresponding to the database access request. The end-to-end execution data includes: the feature vector, the routing identification information of the target routing node, the actual execution time and execution result status of the database access request on the target routing node, and constructs a feedback dataset based on the end-to-end execution data, and updates the model parameters of the AI ​​model through the gradient descent algorithm.

[0014] A computer device includes a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor, when executing the computer program, performs the steps of the method as described in any of the preceding claims.

[0015] In one of the embodiments provided above, the surge in slow queries and node overload caused by uneven load distribution in the database cluster are significantly reduced, the utilization efficiency of cluster resources is maximized, and the overall throughput of the database cluster is significantly improved in high-concurrency scenarios. Attached Figure Description

[0016] To more clearly illustrate the technical embodiments of this application, the accompanying drawings used in the description of the embodiments of this application will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0017] Figure 1 This is a schematic diagram of a system architecture for an adaptive routing method for database access according to an embodiment of this application; Figure 2 This is a flowchart of a database access adaptive routing method according to an embodiment of this application; Figure 3 This is a schematic diagram of the structure of a computer device according to one embodiment of this application. Detailed Implementation

[0018] The technical embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some, not all, of the embodiments of this application. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0019] Please continue reading. Figure 1 The system architecture diagram, Figure 1The overall processing architecture and data flow logic of the adaptive routing system for database access are demonstrated. The overall processing architecture consists of five core components working together: an intelligent database proxy layer, an AI-driven load analysis and routing module, an adaptive query optimization module, a database cluster interaction module, and a feedback learning module. The intelligent database proxy layer, acting as the access hub, is responsible for performing deep analysis of SQL or transaction commands sent by the application layer, including semantics, transaction context, and access patterns, and outputting a structured analysis report. The AI-driven load analysis and routing module generates feature vectors based on this report (covering complexity and execution time prediction quantification), and combines this with real-time node status (CPU, IO, and connection count, etc.) collected from the database cluster interaction module to make multi-dimensional decisions, aiming to minimize latency and balance load, and outputting an optimal routing implementation example including the target node and execution strategy. The adaptive query optimization module then optimizes the request by performing similar query batch processing or intelligent SQL rewriting, and the database cluster interaction module distributes the optimized request to specific database cluster nodes, including the master, slave, or cache, for execution. Finally, the feedback learning module collects end-to-end request data (such as actual execution time and resource consumption), updates AI model parameters through offline training and online fine-tuning, and synchronously updates the routing strategy library, thereby achieving continuous closed-loop optimization of routing decisions. The entire process constitutes a closed-loop system from request reception, intelligent decision-making, adaptive optimization to feedback learning.

[0020] As a general approach, this system adopts a distributed deployment architecture, including: Proxy node cluster: Deploy an intelligent database proxy layer and use a load balancer (such as Nginx) to distribute requests. The number of nodes can be dynamically expanded according to the concurrency. A single node supports 100,000+ concurrent connections. AI Analysis Node: Deploys AI-driven load analysis and routing modules and feedback learning modules, and uses GPU-accelerated lightweight AI model inference with an inference latency of less than 10ms; Database cluster: includes 1 master database, 3 slave databases (1 of which is a dedicated analysis slave database), 2 cache instances (Redis) and sharded nodes. The master and slave databases synchronize data through binlog, and the cache instances are used to store hot data. Monitoring and Log Nodes: Collect data from the entire system operation process, provide a visual monitoring panel, and support anomaly alarms and log queries.

[0021] The following is a detailed description of an adaptive routing method for database access provided in this application, with reference to specific embodiments.

[0022] In one embodiment, this application provides a database access adaptive routing method. This method achieves deep parsing of SQL requests, environmental load awareness, and dynamic distribution optimization by constructing intelligent proxy logic between the application layer and the database cluster.

[0023] In one embodiment, a database access adaptive routing method is provided, such as... Figure 2 As shown, it includes the following steps: S101: Receive a database access request sent by the application layer, and perform lexical, syntactic and semantic analysis on the database access request to generate a structured request parsing report.

[0024] A database access request refers to a raw SQL command string or transaction operation command initiated by an external application and conforming to standard database protocols (such as MySQL, Oracle, etc.). In this step, lexical analysis is responsible for breaking down the input raw request string into the smallest units (tokens) with specific meanings, such as keywords, identifiers, operators, and literals. Syntax analysis, based on preset SQL syntax rules, constructs the above units into a logically rigorous syntax tree (AST) to verify the compliance of the request and extract its hierarchical structure. Semantic analysis further combines the database's metadata information (Schema) to determine the operation objects involved in the request (such as single table, multiple tables, or views), query types (such as SELECT, INSERT, etc.), and the dependencies between operations. The structured request parsing report refers to the standardized encapsulation of the above multi-dimensional parsing results into a machine-readable metadata document that contains the core features of the query (such as read / write attributes, transaction consistency preferences).

[0025] As an example, when receiving a complex aggregation query request involving multiple table joins, this step allows the system to accurately identify whether the request is read-only or write-only, and to determine the granularity of its data access. Through deep parsing, the system can transform unstructured SQL text into a semantically meaningful report, identifying the execution intent and resource requirements of the request in advance. This provides accurate data input for subsequent intelligent decision-making, effectively avoiding the routing blindness problem caused by the inability to understand SQL content in traditional static routing implementations.

[0026] S102: Based on the request parsing report, perform query feature quantization processing to generate a feature vector containing multi-dimensional query feature parameters.

[0027] Query feature quantization refers to using a pre-defined quantization algorithm or encoding method to map the abstract semantic features in the request parsing report into continuous or discrete numerical indicators that can be used for mathematical calculations. Multidimensional query feature parameters are mathematical indicators that characterize the execution characteristics of a request from different dimensions, such as: complexity scores representing logical complexity, expected time factors representing the execution time of similar historical requests, popularity factors representing the frequency of data access, and consistency weights representing the stringency of transactions. Feature vectors are mathematical expressions formed by arranging these quantized multidimensional parameters in a fixed sequence, serving as the standard input format for AI models.

[0028] This step enables the digital modeling of business logic. By constructing multi-dimensional feature vectors, the system can quantitatively assess the expected pressure of each request on computing resources (such as CPU), I / O resources, and lock resources. This digital representation allows the backend AI decision-making model to compare the resource consumption of different requests within the same mathematical space, thus laying a digital foundation for implementing differentiated routing strategies.

[0029] S103: Collects the running status data of each node in the database cluster and forms a cluster status snapshot.

[0030] A database cluster refers to a collaborative system composed of multiple database servers that play different roles (such as master, slave, shard nodes, cache instances, etc.). Operational status data refers to physical and logical metrics that characterize the load of each node in real time, specifically including but not limited to CPU utilization, I / O wait time, current concurrent connections, request queue depth, and memory usage. A cluster status snapshot is a static dataset formed by globally scanning and recording the load status of all nodes in the entire cluster at a specific point in time.

[0031] By generating real-time cluster state snapshots, the system builds comprehensive environmental awareness capabilities. This step allows routing decisions to move beyond relying solely on static distribution rules and instead monitor the health of the database cluster in real time. By sensing dynamic load differences between nodes, the system can dynamically avoid overloaded nodes, providing real-time environmental reference for achieving global load balancing at the cluster level.

[0032] S104: Input the feature vector and the cluster state snapshot into a preset AI model for routing decision-making, calculate the cost value of each candidate node through a preset optimization objective function, select the candidate node with the smallest cost value as the target routing node, and output the target routing node and execution strategy.

[0033] AI models refer to pre-trained, lightweight inference engines (such as decision trees or lightweight neural networks) capable of handling nonlinear correlation analysis. Their inference latency is typically controlled to an extremely low level (e.g., less than 10ms) to meet high concurrency requirements. The optimization objective function defines the optimal execution state the system aims to achieve, such as a cost discriminant aimed at minimizing response time or maximizing resource utilization. Cost refers to the estimated system cost of executing a request given its characteristics and specific node states. The execution strategy includes the specific distribution logic for the request, including the routing identifier information of the target routing node and the corresponding execution mode (e.g., synchronous or asynchronous execution).

[0034] In this step, the AI ​​model performs cross-dimensional correlation reasoning between request features and environmental states to find the globally optimal solution within a massive routing possibility space. Compared to traditional algorithms based on polling or fixed weights, this application achieves optimal matching of requests and resources through cost-value calculation. Through intelligent reasoning and objective function guidance from the AI ​​model, the system can accurately allocate complex analysis tasks to idle analysis nodes, effectively solving the problem of uneven workload distribution among nodes in high-concurrency scenarios and greatly improving the overall throughput of the database cluster.

[0035] S105: Based on the execution strategy, the database access request is adaptively optimized, and the optimized database access request is sent to the target routing node for execution.

[0036] Adaptive optimization refers to dynamically rewriting, injecting parameters, or batch processing the original database access request based on the hardware configuration, version characteristics, cache status, or current load environment of the target routing node. As an example, if the target node is suitable for acceleration via a specific index, optimization could involve injecting the corresponding native database index hint into the SQL statement; if similar short requests are received, optimization could involve merging them into a batch of queries to reduce network interactions. An optimized database access request refers to a request message whose physical execution form is more closely aligned with the characteristics of the target node, while maintaining the logical semantics.

[0037] This step enables location-specific request distribution and execution. Through adaptive optimization, the system can guide the database kernel to run with a better execution plan based on the real-time characteristics of the target node, further reducing the resource consumption of a single request on the target node. This ensures that the final execution effect of the routing decision achieves the expected result and further optimizes hardware performance, guaranteeing overall response speed and stability in high-concurrency scenarios.

[0038] The method provided in this application, by employing deep learning techniques based on lexical, syntactic, and semantic analysis, enables the system to generate structured request parsing reports and further quantify them into multi-dimensional feature vectors, thereby achieving accurate digital modeling of SQL request intent and expected resource consumption. By synchronously collecting the real-time operating status of each node in the cluster to form a status snapshot, and inputting the request features and cluster status together into the AI ​​model for cost calculation, the routing decision can avoid high-load nodes in real time and accurately distribute requests to the most suitable candidate nodes. Finally, combined with adaptive optimization processing, the requests are rewritten or batch-processed in a targeted manner to guide the target nodes to execute the requests in the best performance state.

[0039] In summary, this embodiment significantly reduces the surge in slow queries and node overload caused by uneven load distribution in the database cluster, maximizes the utilization efficiency of cluster resources, and significantly improves the overall throughput of the database cluster in high-concurrency scenarios.

[0040] In addition, this embodiment shields the underlying architecture of the database cluster (such as database sharding and read / write separation), and the application layer does not need to embed complex routing logic. It only needs to access the proxy layer through a standard interface, which improves development efficiency and shortens the system iteration cycle.

[0041] In one embodiment, this application provides a more detailed implementation of the process of parsing the database access request and generating a structured request parsing report in step S101. Please refer to the following description for details: S1011: Extract the query type, operation object, and filtering conditions from the database access request through the semantic analysis.

[0042] The query type refers to the expected execution behavior of the SQL command, such as whether it is a read operation (SELECT) for data retrieval or a write operation (INSERT, UPDATE, DELETE) for data modification. The operation object refers to the logical table, view, or other database entity involved in the request, and determines whether it is a single-table operation or a multi-table join operation. The filter conditions refer to the filtering logic in the SQL statement, guided by clauses such as "WHERE," used to limit the scope of data processing. As an example, the system analyzes the complexity of the filter conditions, such as whether they contain fuzzy matching or complex nested logic.

[0043] This step clarifies the physical intent of the request through in-depth deconstruction of SQL semantics. By extracting these core elements, the system can initially determine the resource consumption tendency of the request, providing the most basic classification basis for subsequent routing decisions (such as read / write separation judgment), ensuring that the system can formulate differentiated distribution paths based on different operation characteristics.

[0044] S1012: Parse the transaction context of the database access request to determine the transaction isolation level requirements and read / write dependencies.

[0045] Transaction context refers to the logical transaction environment in which the request exists, including whether the request belongs to an ongoing transaction sequence. Transaction isolation level refers to the degree of concurrency control set to ensure data consistency, such as read uncommitted, read committed, repeatable read, or serializable. Read-write dependency characterizes whether subsequent operations within the current transaction depend on the results of preceding operations.

[0046] This step, by parsing transaction attributes, enables routing decisions to identify transaction operations with strong consistency requirements and ensures that related read and write requests within the same transaction are routed to nodes that meet the corresponding isolation level requirements (such as forced routing to the primary database), effectively preventing data inconsistency or deadlock risks caused by improper routing.

[0047] S1013: Identify the access pattern of the database access request, the access pattern including request frequency, whether it is a periodic query and data access range.

[0048] Access patterns refer to the characteristics that characterize the distribution of requests in terms of time and space. Request frequency refers to the frequency of similar requests occurring within a unit of time; periodic queries refer to whether the request has a clear time pattern (such as the statistical reports generated every morning); data access range refers to the number of data record rows or partition span that the request is expected to scan.

[0049] This step, by identifying access patterns, builds a behavioral model of the request, enabling the system to predict whether the request will cause a momentary I / O spike or long-term CPU usage. This provides a more forward-looking reference dimension for AI models to predict node pressure, greatly improving the ability to identify slow or hot queries.

[0050] The detailed embodiment provided in this example further refines the extraction of query semantic elements, transaction consistency constraints, and long-term access pattern characteristics during the parsing process. This results in a request parsing report that is no longer just a simple SQL classification, but rather a comprehensive profile encompassing execution intent, consistency risks, and load patterns. Through this multi-dimensional, in-depth analysis, the system can more accurately quantify the potential pressure and compliance requirements of requests, significantly enhancing its understanding of complex business scenarios. This provides detailed data support for achieving high-precision adaptive routing, ensuring the consistency and efficiency of the database cluster when handling mixed load requests from the source.

[0051] In one embodiment, this application provides specific quantification dimensions and definition methods for the multi-dimensional query feature parameters described in step S102 above, in order to achieve accurate modeling of the execution characteristics of database access requests. Please refer to the following description for details: S1021: Query complexity score based on SQL syntax tree depth and data scan range.

[0052] Query complexity score is a metric used to quantify the computational resource consumption of an SQL statement during execution. SQL syntax tree depth reflects the nesting level and complexity of query logic (such as multi-level subqueries or multi-table JOIN operations); a greater depth generally means higher overhead in parsing and execution plan generation. Data scan range refers to the estimated number of rows or partitions to be retrieved based on query conditions, directly determining the I / O throughput pressure.

[0053] This step assesses the weight of a request through two dimensions: structure and scale. This allows the system to accurately identify heavy and light queries before the request is executed, thus providing a quantitative basis for the AI ​​model to select nodes with the appropriate computing power.

[0054] S1022: Expected execution time predicted based on historical execution data.

[0055] Expected execution time refers to a forward-looking estimate of the time required to complete the current request based on statistical patterns. The prediction process not only considers the textual features of the current SQL, but also incorporates historical execution data collected by the feedback learning module (such as the average actual execution time of SQL with the same or similar patterns under different loads).

[0056] This step represents a leap from static analysis to dynamic prediction. The system can predict the duration of request occupancy on nodes, which helps AI models avoid routing implementations that may lead to long request backlogs by optimizing the objective function, thereby improving overall response speed.

[0057] S1023: Data popularity determined based on access frequency.

[0058] Data popularity refers to a feature value that characterizes how frequently target data is accessed. It is calculated based on the recent request frequency of the table or dataset and the data update time. High-popularity data usually means that it is more likely to exist in the database node's memory buffer or front-end cache instance.

[0059] This step establishes an assessment of data interaction costs, allowing the system to prioritize routing queries for high-frequency data to nodes that have cached that data, thereby reducing disk I / O overhead and further lowering query latency.

[0060] S1024: Consistency weights characterizing the consistency level of a transaction, which includes strong consistency, eventual consistency, and latency-allowed levels.

[0061] Consistency weight is a parameter used to quantify the tolerance of a business for data real-time performance and accuracy. Strong consistency requires that requests must obtain the latest data changes; eventual consistency allows for minor synchronization differences in data over a short period of time; and latency tolerance levels are suitable for analytical scenarios with extremely low real-time requirements.

[0062] This step defines the logical boundaries of requests. It provides rigid routing constraints for the AI ​​model, ensuring that strongly consistent requests are accurately routed to the master database, while non-sensitive requests are distributed to slave databases or caches, achieving a balance between data security and system concurrency performance.

[0063] The feature quantization implementation provided in this example introduces multi-dimensional parameters covering logical complexity, historical execution patterns, data popularity, and transaction consistency levels when generating feature vectors. This elevates the system's description of each SQL request from a single statement text to a four-dimensional digital model encompassing computational requirements, expected execution time, I / O characteristics, and logical constraints. Through this comprehensive quantification representation, the AI ​​model can obtain extremely high-quality decision input. In summary, this implementation significantly improves the accuracy and robustness of routing decisions, ensuring that diverse query requests are allocated to the most suitable physical resources, and supporting the system's load balancing capabilities under high concurrency pressure from a data modeling perspective.

[0064] In one embodiment, regarding the collection of operational status data of each node in the database cluster as described in step S103 above, this application provides a more detailed collection scope and dynamic adjustment mechanism to ensure that the cluster status snapshot can reflect the real load of the underlying hardware in real time and accurately. Please refer to the following description for details: S1031: Real-time collection of runtime status data of the master database, slave database, shard nodes and cache instances in the database cluster. The collected runtime status data includes CPU utilization, IO wait time, current number of connections, request queue depth and memory usage. The collection period of runtime status data is dynamically adjusted according to the concurrency of the database access requests.

[0065] A database cluster is a collaborative system composed of multiple nodes with different functions. The master database typically handles write operations requiring strong consistency; slave databases achieve data consistency through log synchronization (such as binlog) and primarily handle read-only requests; sharded nodes are physical nodes implemented to handle large-scale data storage; and cache instances (such as Redis) are used to store frequently accessed data to reduce the pressure on the core database. As an example, the system establishes stable connections (such as JDBC or ODBC) with each type of node through a database cluster interaction module and retrieves real-time status updates based on the protocol characteristics of each node.

[0066] This step builds coverage of the entire cluster topology, enabling the routing system to move beyond monitoring a single database and gain a full-link load view. It can identify every potential performance bottleneck, including the caching layer, providing a complete spatial reference for achieving globally optimal traffic scheduling.

[0067] Operational status data refers to physical and logical indicators that quantify the current load intensity of a node from multiple dimensions. Among them, CPU utilization reflects the degree of consumption of computing resources; IO wait time characterizes the latency pressure of disk interaction during data access; the current number of connections reflects the saturation of the node at the network interaction layer; request queue depth reveals the backlog of tasks currently pending on the node; and memory utilization reflects the usage status of data cache space and sorting buffer.

[0068] By combining multi-dimensional data on computation, storage, network, and execution queuing, the system can accurately distinguish the load type of a node (such as whether it is CPU-intensive or I / O-intensive overload), thereby assisting the AI ​​model to allocate different types of query requests more rationally.

[0069] Dynamic adjustment refers to a mechanism where the system flexibly switches the data collection frequency based on the load intensity input from the front-end application layer. As an example, during high-concurrency periods (such as peak business periods), the system can automatically shorten the data collection step size, with a minimum period of 100ms, to capture instantaneous load fluctuations; while during low-concurrency periods (such as business off-peak periods), the collection period is appropriately extended to reduce the system resource consumption of monitoring itself. This step balances the timeliness of monitoring with system overhead. In extremely high-concurrency scenarios, the system can perceive real-time changes in nodes with millisecond-level precision, greatly reducing the risk of routing decision deviations caused by outdated monitoring data and ensuring that the distribution strategy can quickly respond to minor changes in cluster status.

[0070] The state acquisition embodiment provided in this example not only collects all node data, including master database, slave database, shard nodes and cache instances, in real time, but also covers multi-dimensional operating indicators from computing (CPU), I / O (IO wait) to connection resources (number of connections, queue depth) and storage (memory usage). With the addition of a dynamically adjusted acquisition cycle based on concurrency, the system is able to build a cluster state snapshot with extremely high fidelity and timeliness.

[0071] Through this deep and broad perception of the underlying hardware status, the routing decision engine gains excellent feedback loop support. In summary, this significantly improves the system's accuracy in capturing instantaneous traffic surges and node performance fluctuations, effectively preventing overload avalanche problems caused by load reporting delays on some nodes, and ensuring the overall robustness of the database cluster in high-concurrency scenarios from an environmental awareness perspective.

[0072] In one embodiment, regarding the calculation of the cost value of each candidate node through a preset optimization objective function in step S104 above, this application provides a specific mathematical calculation model and parameter definition to achieve accurate quantification of routing decisions. Please refer to the following description for details: The optimization objective function is: T = α * T_query + β * T_queue; Where T_query is the expected execution time of the query, T_queue is the waiting time of the node queue, and α and β are dynamic weights.

[0073] Where T represents the final calculated cost, which physically represents the total time or cost expected to be spent processing the database access request on a specific candidate node. T_query represents the expected query execution time, predicted based on the feature vector generated in step S102 by associating it with historical execution data of similar SQL queries, reflecting the pure computational overhead of the request under conditions of sufficient hardware resources. T_queue represents the node queue waiting time, calculated based on the request queue depth and load pressure of each node in the cluster status snapshot collected in step S103, reflecting the additional time loss incurred due to queuing before the request enters the database kernel for execution.

[0074] α and β are dynamic weighting factors used to adjust the weighting of the influence between query efficiency and environmental load. In practical applications, the system dynamically adjusts these two parameters based on business priorities. For example, in online transaction scenarios where extremely fast response is required, the value of α can be increased to prioritize the fastest execution node; while in scenarios where overall throughput and load balancing are prioritized, the value of β can be increased to prioritize avoiding nodes with severe task backlogs.

[0075] This step decouples and integrates request characteristics and environmental state through mathematical functions. The system can transform the abstract routing problem into a rigorous algebraic minimization problem. By weighted summing of execution cost and queuing cost, it can accurately quantify the adaptability of each node to the current request.

[0076] In this embodiment, by employing an optimization objective function that includes two core dimensions—expected execution time and queue waiting time—and introducing dynamically adjusted weights, the routing decision-making process can simultaneously consider both the SQL request itself and the current congestion level of the database nodes. Through this two-factor mathematical weighted calculation, the system can effectively identify nodes with excellent hardware performance but currently experiencing severe task backlogs, thereby avoiding blindly sending requests to overloaded nodes.

[0077] In summary, this embodiment ensures the scientific nature and flexibility of routing decisions from a mathematical logic perspective. It can dynamically adjust resource allocation strategies according to real-time business needs, significantly reduce the average task queuing time in high-concurrency environments, and greatly optimize the resource turnover rate of the database cluster and the overall response latency of the system.

[0078] In one embodiment, this application provides a specific optimization strategy for the adaptive optimization process of the database access request in the above steps, in order to further improve the efficiency of the database node in executing SQL instructions. Please refer to the following description for details: S1051: Identify similar SQL requests received within a preset time window, and generate a merged batch query statement based on the similar SQL requests.

[0079] A preset time window refers to a short time range (e.g., 50ms or 100ms) defined by the system to buffer and aggregate concurrent requests. Similar SQL requests refer to multiple independent SQL commands that maintain consistency in logical templates but differ only in specific parameter values. Generating merged batch query statements means rewriting multiple scattered single queries or insert operations into a single aggregate command containing multiple sets of parameters through an adaptive query optimization module.

[0080] As an example, if the system receives 100 simple point query requests for the same "user" table in a very short time (such as SELECT name FROM user WHERE id=1 up to id=100), the optimization process will merge these requests into a single query statement using the IN keyword (such as SELECT name FROM user WHERE id IN (1,2,...,100)).

[0081] This step enables the consolidation of scattered load spikes. By batch processing similar requests, the system can significantly reduce the number of network round trips (RTTs) between the application layer and the target routing node, thereby greatly reducing the connection pool pressure and SQL parsing overhead of the target database node. This allows the database to process multiple times the amount of data at the cost of a single task, greatly improving execution efficiency in high-concurrency scenarios.

[0082] S1052: Based on the configuration characteristics of the target routing node, inject the corresponding native database index hint into the database access request.

[0083] The configuration characteristics of a target routing node refer to its existing physical index structure, hardware resource preferences (such as memory priority or I / O priority), or specific version optimization features. Database native index hints (IndexHint) refer to specific syntax tags (such as USEINDEX or FORCEINDEX in MySQL) explicitly injected into SQL statements to interfere with the database query optimizer's execution path.

[0084] This step enables site-specific execution intervention. Based on the metadata information of the selected routing node, the system intelligently adds specific prompts to the SQL message, forcing the database kernel to retrieve data using the expected optimal index, rather than relying entirely on potential biases in the database's internal optimizer. Through manual guidance and adaptive rewriting, it ensures that complex queries run immediately with the optimal execution plan upon reaching the target node, avoiding slow queries and ensuring that the performance gains from routing decisions are truly translated into improved response speed.

[0085] In this embodiment, before sending requests to the target node, the system performs batch merging based on the similarity of requests and injects targeted index hints based on the configuration characteristics of the target node. This optimizes not only the total amount of traffic sent to the database cluster but also makes the micro-execution logic more aligned with the characteristics of the hardware infrastructure. Through this adaptive optimization strategy of merging first and then rewriting, the system significantly improves the value of a single database interaction without changing the business logic. It effectively reduces the parsing burden and I / O overhead of the database nodes, ensuring low latency and high throughput for the overall system in high-concurrency scenarios from the execution layer.

[0086] In one embodiment, the method provided in this application further includes a closed-loop feedback learning mechanism for realizing the self-evolution and accuracy correction of the routing strategy. The following is combined with... Figure 2 The steps described in claim 7 are explained in detail below: S106: Collect the full-link execution data corresponding to the database access request.

[0087] End-to-end execution data refers to the runtime records generated throughout the entire lifecycle, from the moment a request enters the proxy layer until the result is returned to the application layer. Specifically, it includes: the feature vector (representing the initial state of the request), the routing identifier information of the target routing node (recording the decision-making direction), the actual execution time of the database access request on the target routing node (measuring real performance), and the execution result status (such as success, failure, or timeout).

[0088] As an example, if the system routes an analytical query with a complexity score of 8 to slave database A, the end-to-end data will record in detail the actual CPU and I / O time consumed by the request on slave database A. The data collection process is executed asynchronously in the background by the feedback learning module to ensure that no additional latency overhead is incurred for real-time database access.

[0089] This step constructs a perception loop for the system to assess the effectiveness of its own decisions. By obtaining the actual performance of requests at physical nodes, it provides the system with an objective benchmark for evaluating the quality of decisions, enabling the system to identify the deviation between the prediction model and the actual physical execution, and providing high-value labeled sample data for subsequent model optimization.

[0090] S107: Construct a feedback dataset based on the full-link execution data, and update the model parameters of the AI ​​model using the gradient descent algorithm.

[0091] Feedback datasets refer to training sample sets formed by aggregating, cleaning, and standardizing large amounts of end-to-end execution data according to time or business dimensions. Gradient descent is an optimization algorithm that calculates the gradient of the loss function between predicted values ​​(such as the cost value predicted by the model) and true values ​​(such as actual execution time), and gradually adjusts the weights and bias parameters inside the AI ​​model in the opposite direction of the gradient to minimize the loss function.

[0092] In practical applications, the feedback learning module trains the feedback data offline at preset intervals (e.g., 1 hour) to optimize the decision-making logic of the AI ​​model; it also supports a real-time update mechanism, which immediately triggers model fine-tuning when the latency rate of a certain type of query significantly exceeds a preset threshold.

[0093] This step enables dynamic calibration of algorithm weights. Through closed-loop parameter updates, the model can automatically learn and adapt to performance fluctuations in the database cluster caused by hardware aging, data surges, or index changes, continuously improving the accuracy of routing decisions and ensuring that the system maintains the optimal resource scheduling strategy throughout long-term business operations.

[0094] The feedback learning implementation provided in this example constructs a feedback dataset by collecting end-to-end execution data, including feature vectors, route identifiers, actual execution time, and result status. It then uses gradient descent to iteratively optimize the AI ​​model, enabling the routing decision layer to self-correct and continuously learn. Through this closed-loop mechanism of execution-perception-feedback-optimization, the system can dynamically correct prediction biases caused by outdated historical data or business model changes. This significantly enhances the system's adaptability in complex and dynamic business environments, ensuring that the decision accuracy of the routing strategy continuously evolves with the accumulation of data, and effectively maintaining the high throughput and low latency performance of the database cluster over the long term.

[0095] To further facilitate understanding, the execution flow of the above method is explained below with reference to specific typical scenarios, including: (1) Complex analysis query scenario: The application layer sends an SQL request: "SELECT COUNT(*) FROM orderWHERE create_time>'2025-01-01' GROUP BY user_id". This request is a read-only complex aggregation query, which allows for slight delay.

[0096] ① The proxy layer parses the SQL as "read-only, aggregate query, involving large table orders, and time range filtering", and the transaction consistency requirement is eventual consistency; ② The AI-driven load analysis and routing module quantifies query characteristics: complexity score 8 / 10, expected execution time 100ms, data popularity is medium; cluster status is collected: master database CPU utilization 70%, ordinary slave database CPU utilization 65%, analysis slave database CPU utilization 30%; ③ AI model decision: route the query to the analysis slave database and add the "USE INDEX (idx_create_time)" hint; ④ Analyze the optimized SQL executed by the slave database, return the results to the proxy layer, and the proxy layer sends the results back to the application; ⑤ The feedback learning module recorded that the request execution time was 80ms, indicating low resource consumption. The model parameters were then updated to enhance the routing strategy for this type of query.

[0097] (2) Simple query scenario: The application layer sends batch SQL requests: “SELECT name FROM user WHEREid = 1”, “SELECT name FROM user WHERE id = 2”, … “SELECT name FROM user WHEREid = 100”. This type of request is a simple read request with low latency requirements.

[0098] ① The proxy layer resolves to "batch simple queries, read-only, strong consistency not required"; ② Quantitative characteristics of the AI-driven load analysis and routing module: complexity score 2 / 10, expected execution time 1ms / database, high data popularity; cluster status collection: node A has the lowest number of connections among the three slave databases (200 / 1000), and CPU utilization is 20%; ③ The adaptive query optimization module merges batch requests into "SELECT name FROM user WHERE idIN (1,2,...,100)"; ④ The AI ​​model decides to route the query to node A, which then performs a batch query and returns the results. ⑤ The feedback learning module records a total execution time of 5ms after batch processing, saving 95% of the time compared to single-line execution, and optimizing the batch processing trigger threshold of the model.

[0099] It should be understood that the sequence number of each step in the above embodiments does not imply the order of execution. The execution order of each process should be determined by its function and internal logic, and should not constitute any limitation on the implementation process of the embodiments of this application.

[0100] In one embodiment, a database access adaptive routing system is provided, which corresponds one-to-one with the time-aware intelligent data processing method in the above embodiments. For example... Figure 1 As shown, the database access adaptive routing system includes: The intelligent database proxy layer is used to receive database access requests sent by the application layer, parse the database access requests, and generate a structured request parsing report. The AI-driven load analysis and routing module is used to perform query feature quantification processing based on the request parsing report to generate a feature vector containing multi-dimensional query feature parameters; and to input the feature vector and the obtained cluster state snapshot into a preset AI model for routing decision-making, calculate the cost value of each candidate node through a preset optimization objective function, select the candidate node with the smallest cost value as the target routing node, and output the target routing node and execution strategy. The database cluster interaction module is used to collect the running status data of each node in the database cluster to form a snapshot of the cluster status. An adaptive query optimization module is used to adaptively optimize the database access request based on the execution strategy. The database cluster interaction module is also used to send the optimized database access request to the target routing node for execution.

[0101] Furthermore, the database access adaptive routing system also includes: The feedback learning module is used to collect end-to-end execution data corresponding to the database access request. The end-to-end execution data includes: the feature vector, the routing identification information of the target routing node, the actual execution time and execution result status of the database access request on the target routing node, and constructs a feedback dataset based on the end-to-end execution data, and updates the model parameters of the AI ​​model through the gradient descent algorithm.

[0102] Please continue reading. Figure 1 As shown in the system architecture diagram, Figure 1 This demonstrates the overall processing architecture and data flow logic of the database access adaptive routing system. The entire process forms a closed-loop system from request reception, intelligent decision-making, adaptive optimization to feedback learning.

[0103] The following is a detailed textual summary and explanation of the flowchart: I. Request Reception and Deep Parsing Phase Application layer: Initiates the original database access request, which contains specific SQL or transaction instructions.

[0104] Intelligent Database Proxy Layer: Receives requests as the core access point and performs "1. Deep Parsing".

[0105] The analysis dimensions cover: SQL semantics, transaction context, and access patterns.

[0106] Parsing output: After parsing is complete, the proxy layer outputs a structured parsing report to the backend.

[0107] II. Intelligent Load Analysis and Routing Decision Stage The parsed report is then fed into the AI-driven load analysis and routing module, which performs the following core tasks: Feature quantization: Execute "2.1 Quantify query features" (including complexity, execution time prediction, etc.) and complete feature vector generation.

[0108] Status awareness: Execute "2.2 Real-time collection of cluster status" to obtain the status of each node (CPU, IO, number of connections, etc.) from the interaction module.

[0109] Intelligent Decision-Making: Executes "3. AI Model Multi-Dimensional Decision-Making", the core optimization goal of which is to minimize latency and balance load.

[0110] Decision output: The module finally outputs the optimal routing example, which specifies the target node and execution strategy.

[0111] III. Adaptive Optimization and Execution Phase Adaptive Query Optimization Module: After receiving the decision implementation example, execute "4. Optimization Process".

[0112] Specific strategies include: batch processing of similar queries (merging requests) and intelligent SQL rewriting.

[0113] Database cluster interaction module: As the execution center, it is responsible for "5. Execute the optimized request".

[0114] Requests are precisely distributed to specific locations within the database cluster nodes, including the master, slave, or cache.

[0115] Results returned: The database cluster nodes return the query results to the interaction module, and the results are finally returned to the application layer via the proxy layer.

[0116] IV. End-to-End Feedback and Continuous Learning Loop To enable the strategy to evolve itself, the system constructs a feedback loop: Data Acquisition: The interaction module collects data from the entire request chain (such as execution time, resource consumption, etc.) and transmits it to the feedback learning module.

[0117] Model Iteration: The feedback learning module ensures continuous optimization by combining offline training with online fine-tuning of AI model parameters.

[0118] Policy Update: The learning outcomes are used to update the routing policy library and fed back to the AI-driven load analysis and routing module to achieve more accurate subsequent decisions.

[0119] Specific limitations regarding the adaptive routing system for database access can be found in the limitations of the adaptive routing method for database access described above, and will not be repeated here. Each module in the aforementioned adaptive routing system for database access 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 a computer device, or stored in the memory of a computer device in software form, so that the processor can call and execute the corresponding operations of each module.

[0120] In one embodiment, a computer device is provided, which may be a server, and its internal structure diagram may be as follows: Figure 3 As shown, the computer device includes a processor, memory, network interface, and database connected via a system bus. The processor provides computing and control capabilities. The memory includes non-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 stored in the non-volatile storage media. The network interface is used for communication with external terminals via a network connection. When the computer program is executed by the processor, it implements an adaptive routing method for database access.

[0121] In one embodiment, a computer-readable storage medium is provided having a computer program stored thereon, which, when executed by a processor, implements a database access adaptive routing method.

[0122] 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.

[0123] 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.

[0124] The above-described embodiments are only used to illustrate the technical embodiments of this application, and are not intended to limit it. Although this application 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 embodiments 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 embodiments to deviate from the spirit and scope of the technical embodiments of this application, and should all be included within the protection scope of this application.

Claims

1. A database access adaptive routing method, characterized in that, include: Receive database access requests sent by the application layer, and perform lexical, syntactic and semantic analysis on the database access requests to generate a structured request parsing report; Based on the request parsing report, query feature quantification is performed to generate a feature vector containing multi-dimensional query feature parameters. Collect the running status data of each node in the database cluster to form a cluster status snapshot; The feature vector and the cluster state snapshot are input into a preset AI model for routing decision-making. The cost value of each candidate node is calculated through a preset optimization objective function, and the candidate node with the smallest cost value is selected as the target routing node. The target routing node and execution strategy are then output. Based on the execution strategy, the database access request is adaptively optimized, and the optimized database access request is sent to the target routing node for execution.

2. The method according to claim 1, characterized in that, The step of parsing the database access request and generating a structured request parsing report includes: The semantic analysis is used to extract the query type, operation object, and filtering conditions from the database access request; Parse the transaction context of the database access request to determine the transaction isolation level requirements and read / write dependencies; Identify the access pattern of the database access request, including the request frequency, whether it is a periodic query, and the data access range.

3. The method according to claim 1, characterized in that, The multidimensional query feature parameters include: Query complexity score based on SQL syntax tree depth and data scan range; The expected execution time is predicted based on historical execution data; Data popularity is determined based on access frequency; And consistency weights that characterize the consistency level of a transaction, including strong consistency, eventual consistency, and latency-allowed levels.

4. The method according to claim 1, characterized in that, The collected operational status data of each node in the database cluster includes: Real-time collection of runtime status data of the master database, slave database, shard nodes and cache instances in the database cluster; The operational status data includes CPU utilization, IO wait time, current number of connections, request queue depth, and memory usage. The collection period for the operational status data is dynamically adjusted based on the concurrency of the database access requests.

5. The method according to claim 3, characterized in that, The optimization objective function is: T = α * T_query + β * T_queue; Where T_query is the expected execution time of the query, T_queue is the waiting time of the node queue, and α and β are dynamic weights.

6. The method according to claim 1, characterized in that, The adaptive optimization processing of the database access request includes: Identify similar SQL requests received within a preset time window, and generate merged batch query statements based on the similar SQL requests; Based on the configuration characteristics of the target routing node, the corresponding native database index hints are injected into the database access request.

7. The method according to any one of claims 1-6, characterized in that, The method further includes: Collect end-to-end execution data corresponding to the database access request. The end-to-end execution data includes: the feature vector, the routing identifier information of the target routing node, the actual execution time of the database access request on the target routing node, and the execution result status. A feedback dataset is constructed based on the full-link execution data, and the model parameters of the AI ​​model are updated using the gradient descent algorithm.

8. A database access adaptive routing system, characterized in that, include: The intelligent database proxy layer is used to receive database access requests sent by the application layer, parse the database access requests, and generate a structured request parsing report. The AI-driven load analysis and routing module is used to perform query feature quantification processing based on the request parsing report to generate a feature vector containing multi-dimensional query feature parameters; and to input the feature vector and the obtained cluster state snapshot into a preset AI model for routing decision-making, calculate the cost value of each candidate node through a preset optimization objective function, select the candidate node with the smallest cost value as the target routing node, and output the target routing node and execution strategy. The database cluster interaction module is used to collect the running status data of each node in the database cluster to form a snapshot of the cluster status. An adaptive query optimization module is used to adaptively optimize the database access request based on the execution strategy. The database cluster interaction module is also used to send the optimized database access request to the target routing node for execution.

9. The database access adaptive routing system according to claim 8, characterized in that, Also includes: The feedback learning module is used to collect end-to-end execution data corresponding to the database access request. The end-to-end execution data includes: the feature vector, the routing identification information of the target routing node, the actual execution time and execution result status of the database access request on the target routing node, and constructs a feedback dataset based on the end-to-end execution data, and updates the model parameters of the AI ​​model through the gradient descent algorithm.

10. A computer device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements the steps of the method as described in any one of claims 1 to 7.

Images