Dynamic grouping and connection scheduling method for database connection pool

Abstract

The invention provides a dynamic grouping and connection scheduling method for a database connection pool. According to the method, through multi-dimensional feature extraction and quantification, queried resource consumption attributes are accurately identified; dynamically dividing connection sub-pools based on clustering analysis, and realizing self-adaptive matching of query features and connection resources; in combination with packet routing, sub-pool load balancing and idle connection optimization strategies, the resource utilization rate and the response speed are remarkably improved; and through a closed-loop monitoring and dynamic adjustment mechanism, the distribution change and the system load fluctuation are adaptively inquired in real time, and the manual maintenance cost is reduced. Finally, the problems of rigidity of a fixed grouping strategy and preemption of a single resource pool are solved, query delay is reduced in a high-concurrency scene, and stability and expansibility of a database system are improved.

Description

Technical Field

[0001] This application relates to the field of databases, and more particularly to a method for dynamic grouping and connection scheduling of database connection pools. Background Technology

[0002] In database application systems, database connections are a scarce resource. Establishing a database connection involves time-consuming processes such as TCP handshakes and authentication. Frequent creation and closure of connections significantly increases system overhead and reduces response speed. Database connection pools, as core middleware, pre-create and cache a certain number of database connections, enabling connection reuse and unified management, and are a key technology for improving system performance. With the increasing complexity of internet applications, query requests exhibit significant heterogeneity: from simple single-table queries and read-only queries to complex statistical queries involving multi-table joins and subqueries, and high-concurrency read-write transaction queries. Different types of queries have vastly different resource consumption requirements for database connections (such as connection holding time and CPU / IO consumption), which places higher demands on the resource allocation and scheduling strategies of connection pools.

[0003] Some feasible database connection pool solutions rely on static configuration and fixed strategies, which leads to a lack of flexibility in grouping, an inability to fully adapt to the relationship between query and connection tasks and the needs of dynamic scenarios, and insufficient resource utilization. As a result, the efficiency, effectiveness, and flexibility of database connection configuration are poor. Therefore, a dynamic grouping and connection scheduling method for database connection pools is needed to solve these problems. Summary of the Invention

[0004] The purpose of this application is to at least address one of the aforementioned technical deficiencies, particularly the poor efficiency, effectiveness, and flexibility of database connection configuration in the prior art.

[0005] In a first aspect, this application provides a method for dynamic grouping and connection scheduling of a database connection pool, the method comprising:

[0006] Based on the structured query statement, parse the statement structure and extract a multi-dimensional feature set;

[0007] Based on the multidimensional feature set, obtain the resource consumption parameters;

[0008] Cluster analysis is used to dynamically divide the connection sub-pools and determine the scheduling characteristics of each connection sub-pool;

[0009] The scheduling feature is used to indicate the performance adaptation of statements in each connection sub-pool and the connection performance of each connection sub-pool.

[0010] Based on the resource consumption parameters, each structured query statement is allocated to each connection sub-pool, and the connection allocation operation of each structured query statement is executed in each connection sub-pool.

[0011] As an optional implementation, the method further includes:

[0012] Continuously monitor and dynamically adjust indicators, and trigger sub-pool reorganization or scheduling strategy update based on the monitoring results of the dynamic adjustment indicators;

[0013] The dynamic adjustment indicators include the distribution of resource consumption parameters corresponding to each structured query statement, sub-pool load parameters, and database operating parameters;

[0014] The sub-pool reorganization includes:

[0015] If the distribution of resource consumption parameters of a first number of structured query statements reaches a preset parameter condition, or the load parameter of any sub-pool in a connection sub-pool reaches a preset condition, or a first duration is reached, then the splitting, merging, or scaling of each connection sub-pool is triggered.

[0016] The scheduling policy update includes:

[0017] For the target connection sub-pool, the optimization parameters corresponding to the target sub-pool are determined based on the sub-pool load parameters or abnormal parameters in the database operation parameters, and the scheduling characteristics of the target sub-pool are updated.

[0018] As an optional implementation, the step of parsing the structured query statement and extracting a multi-dimensional feature set includes:

[0019] Based on the structured query statement, obtain the operation keywords, number of table names, logical operators, nesting level, database metadata, query conditions and transaction associations, and determine the query type, number of tables involved, condition complexity, data volume estimate and transaction attributes corresponding to the structured query structure;

[0020] Quantify the query type, the number of tables involved, the condition complexity, the estimated data volume, and the feature values ​​and corresponding feature scores of the transaction attributes;

[0021] The step of obtaining resource consumption parameters based on the multidimensional feature set includes:

[0022] The resource consumption parameters are obtained by weighted summing of the feature scores corresponding to the query type, the number of tables involved, the condition complexity, the data volume estimate, and the transaction attribute.

[0023] As an optional implementation, the step of dynamically dividing the connection sub-pools through cluster analysis and determining the scheduling characteristics of each connection sub-pool includes:

[0024] Based on the preset resource consumption parameter range, the basic sub-pool groups are divided;

[0025] Based on the basic sub-pool group, multiple target connection sub-pools are determined through cluster analysis of the resource consumption parameters, and the target resource consumption parameter range and the maximum number of connections for each target connection sub-pool are determined.

[0026] As an optional implementation, the cluster analysis includes:

[0027] The resource consumption parameters of a second number of the structured query statements are determined as clustering samples;

[0028] The number of clusters is determined based on the elbow rule;

[0029] The corresponding reference sample is determined based on the number of clusters;

[0030] Each of the reference samples is used as the initial cluster center. The distance between each cluster sample and each of the initial cluster centers is calculated and assigned. The cluster centers are updated until the cluster centers converge. Multiple connection sub-pools corresponding to the number of clusters are determined.

[0031] And, the method for determining the maximum number of connections in the sub-pool includes:

[0032] The maximum number of connections for each sub-pool is calculated based on the number of clustered samples corresponding to each target connection sub-pool, as well as the preset global maximum number of connections and load adjustment coefficient.

[0033] The load adjustment coefficient is determined based on the database processor utilization rate.

[0034] As an optional implementation, the step of allocating each structured query statement to each connection sub-pool according to the resource consumption parameters, and performing connection allocation operations for each structured query statement within each connection sub-pool, includes:

[0035] Based on the resource consumption parameters, each structured query statement is assigned to a target connection sub-pool that matches the target resource consumption parameter range;

[0036] If none of the target resource consumption parameter ranges of the target connection sub-pools cover the resource consumption parameters corresponding to the target statement, then the target statement is assigned to the target connection sub-pool where the lower limit of the target resource consumption parameter range exceeds a preset threshold.

[0037] Within each target connection sub-pool, idle connections are allocated based on the cumulative occupancy time of each connection.

[0038] When there are no idle connections in the corresponding target connection sub-pool, a connection whose waiting time meets the preset waiting time condition is matched according to the target resource consumption parameter range of the target connection sub-pool.

[0039] After the process corresponding to the structured query statement ends, the connection is returned to the corresponding target connection sub-pool, and the idle connection is removed according to the idle connection retention time corresponding to the target resource consumption parameter range of each target connection sub-pool.

[0040] Secondly, this application provides a dynamic grouping and connection scheduling device for a database connection pool, comprising:

[0041] The acquisition module is used to parse the structure of a structured query statement and extract a multi-dimensional feature set.

[0042] The acquisition module is further configured to acquire resource consumption parameters based on the multidimensional feature set;

[0043] The processing module is used to dynamically divide the connection sub-pools through cluster analysis and determine the scheduling characteristics of each connection sub-pool;

[0044] The scheduling feature is used to indicate the performance adaptation of statements in each connection sub-pool and the connection performance of each connection sub-pool.

[0045] The processing module is further configured to allocate each of the structured query statements to each of the connection sub-pools according to the resource consumption parameters, and to perform connection allocation operations for each of the structured query statements in each of the connection sub-pools.

[0046] As an optional implementation, the processing module is further configured to:

[0047] Continuously monitor and dynamically adjust indicators, and trigger sub-pool reorganization or scheduling strategy update based on the monitoring results of the dynamic adjustment indicators;

[0048] The dynamic adjustment indicators include the distribution of resource consumption parameters corresponding to each structured query statement, sub-pool load parameters, and database operating parameters;

[0049] The specific methods by which the processing module performs sub-pool reorganization include:

[0050] If the distribution of resource consumption parameters of a first number of structured query statements reaches a preset parameter condition, or the load parameter of any sub-pool in a connection sub-pool reaches a preset condition, or a first duration is reached, then the splitting, merging, or scaling of each connection sub-pool is triggered.

[0051] The specific methods by which the processing module performs scheduling policy updates include:

[0052] For the target connection sub-pool, the optimization parameters corresponding to the target sub-pool are determined based on the sub-pool load parameters or abnormal parameters in the database operation parameters, and the scheduling characteristics of the target sub-pool are updated.

[0053] Thirdly, this application provides a computer device including one or more processors and a memory storing computer-readable instructions that, when executed by the one or more processors, perform the steps of the method described in the first aspect.

[0054] Fourthly, this application provides a storage medium storing computer-readable instructions that, when executed by one or more processors, cause the one or more processors to perform the steps of the method described in the first aspect.

[0055] As can be seen from the above technical solutions, the embodiments of this application have the following advantages:

[0056] Based on any of the above embodiments, this application first analyzes the structured features of the query statement, extracts key dimensions such as operation type, number of tables, and condition complexity, and generates resource consumption parameters through a weighted quantization model to provide a data foundation for dynamic optimization of the connection pool. Based on this, a hybrid grouping strategy combining rule initialization and K-means clustering is adopted: at system startup, basic sub-pools are divided according to preset intervals; during operation, dynamic reorganization is triggered based on indicators such as query distribution variance and sub-pool load threshold; the number, boundaries, and scale of sub-pools are adaptively adjusted through clustering algorithms to ensure that the grouping strategy always fits the actual business scenario. At the scheduling level, queries are accurately routed to matching sub-pools through resource consumption parameters, and abnormal queries are automatically allocated to high-load pools for isolation and processing; within sub-pools, a "minimum cumulative occupied time priority" strategy is used to allocate connections, prioritizing the reuse of connection resources with high execution efficiency; differentiated waiting timeout mechanisms are set for different sub-pool types to balance response speed and the fault tolerance requirements of complex queries; after a connection is returned, idle connections are dynamically reclaimed based on the characteristics of the sub-pool to improve resource turnover efficiency. Furthermore, by monitoring key metrics such as query response time and sub-pool timeout rate in real time, parameter fine-tuning (such as expanding the sub-pool adaptation range and adjusting the maximum number of connections) or group reorganization is triggered, forming a closed-loop optimization of "feature-group-load". Ultimately, this achieves three core improvements: First, resource utilization is significantly optimized, avoiding idle fixed groups and resource contention in a single pool; second, response speed is accelerated, eliminating "small queries waiting for large queries" blocking by isolating simple queries from complex queries; and third, dynamic adaptation capabilities are enhanced, eliminating the need for manual intervention in scenarios with sudden load changes such as e-commerce promotions, reducing operation and maintenance costs and ensuring system stability. Attached Figure Description

[0057] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the 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.

[0058] Figure 1 A flowchart illustrating a dynamic grouping and connection scheduling method for a database connection pool provided in one embodiment of this application;

[0059] Figure 2 A schematic diagram of the system architecture corresponding to the dynamic grouping and connection scheduling method of the database connection pool provided in one embodiment of this application;

[0060] Figure 3 This is an internal structural diagram of a computer device provided in an embodiment of this application. Detailed Implementation

[0061] The technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, and not all embodiments. 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.

[0062] In database application systems, database connections are a scarce resource. Establishing a database connection involves time-consuming processes such as TCP handshake and authentication. Frequent creation and closure of connections significantly increases system overhead and reduces response speed. Database connection pools, as core middleware, pre-create and cache a certain number of database connections, enabling connection reuse and unified management, and are a key technology for improving system performance.

[0063] As internet applications become increasingly complex, query requests exhibit significant heterogeneity: from simple single-table queries and read-only queries to complex statistical queries involving multi-table joins and subqueries, and high-concurrency read-write transaction queries. Different types of queries place vastly different demands on database connection resources (such as connection holding time and CPU / IO consumption), which places higher demands on the resource allocation and scheduling strategies of connection pools.

[0064] Some feasible database connection pooling solutions primarily rely on static configuration and fixed strategies. For example, some specific implementation methods include:

[0065] Fixed-group connection pooling scheme: The connection pool is pre-divided into a fixed number of sub-pools (e.g., "read-only connection pool," "read-write connection pool," and "administrator connection pool"). The number of connections and configuration parameters (e.g., maximum number of connections, idle timeout) for each sub-pool are set through a static configuration file. Query requests are distributed to the corresponding sub-pool according to preset rules (e.g., SQL statements containing "SELECT" are routed to the read-only pool). Within each sub-pool, connections are allocated using a round-robin or random strategy. Typical applications include Druid's "multi-data source grouping" feature and HikariCP's "read-only routing" extension.

[0066] Static connection pooling schemes: These schemes do not divide the pool into fixed sub-pools; the entire connection pool is a single resource pool, and a global scheduling strategy is used to allocate connections. Mainstream strategies include "minimum connection count priority" (selecting the database instance with the fewest currently occupied connections), "round-robin scheduling" (allocating connections sequentially in a loop), and "random scheduling" (randomly selecting idle connections). Typical applications include the default scheduling mechanism of the C3P0 connection pool and the basic scheduling strategy of the Tomcat JDBC Pool.

[0067] Load-based static scaling solutions: Some solutions support adjusting the global maximum number of connections in the connection pool based on the database server load (such as CPU utilization and memory usage), but do not involve grouping connections. They only adapt to overall load changes by increasing or decreasing the total number of connections, such as Alibaba Druid's "dynamic scaling" function.

[0068] Therefore, the limitations of such feasible implementation methods may include the following aspects:

[0069] Grouping lacks flexibility and cannot adapt to heterogeneous query characteristics: Fixed grouping schemes divide the connection pool based only on simple rules (such as query type), without considering key characteristics such as query complexity, data volume, and transaction attributes. For example, for the same SELECT query, the resource consumption of a simple single-table query (such as "SELECT name FROM user WHERE id=1") is very different from that of a complex multi-table join statistical query (such as "SELECT COUNT (*) FROM order JOIN product ON order.pid=product.idGROUP BY product.category"), yet they are assigned to the same "read-only connection pool," causing the connection used by the complex query to be blocked by the simple query, resulting in an imbalance in resource allocation.

[0070] Scheduling strategies ignore the matching degree between queries and connections: Static scheduling schemes (round-robin, random) only focus on the idle state of connections or the overall database load, without allocating appropriate connection resources based on query characteristics. For example, high-concurrency simple queries and low-concurrency complex queries compete for the same batch of connections, which can easily lead to a situation where "small queries wait for large queries," resulting in increased system response latency.

[0071] Unable to dynamically adapt to changes in query distribution: The grouping rules, connection number configuration, and scheduling strategy of the existing solution are all statically set. When changes in business scenarios cause changes in the distribution of query characteristics (such as during e-commerce promotions, when the proportion of order read and write queries increases from 30% to 70%), manual modification of the configuration and system restart are required. It cannot be dynamically adjusted in real time and has poor adaptability.

[0072] Low resource utilization: In the fixed grouping scheme, some sub-pools may run out of connections due to a sudden increase in query volume, while the connections of other sub-pools are idle but cannot be reused across groups; the single resource pool scheme lacks group isolation, which leads to resource contention between different types of queries, resulting in insufficient overall resource utilization.

[0073] Therefore, this application proposes a method based on multi-dimensional feature analysis of query statements to accurately identify the resource consumption attributes of queries, realize dynamic grouping of connection pools, and adjust the number, size, and grouping rules of sub-pools in real time according to the distribution of query features and system load. An intelligent scheduling strategy adapted to dynamic grouping is designed to achieve accurate matching of "query features - connection sub-pools," avoiding resource contention and blocking. This improves the resource utilization of database connections, system response speed, and scenario adaptability, and reduces query latency in high-concurrency scenarios.

[0074] In summary, the technical concept of this application lies in first analyzing the structured features of the query statement, extracting key dimensions such as operation type, number of tables, and condition complexity, and generating resource consumption parameters through a weighted quantization model to provide a data foundation for dynamic optimization of the connection pool. Based on this, a hybrid grouping strategy combining rule initialization and K-means clustering is adopted: at system startup, basic sub-pools are divided according to preset intervals; during operation, dynamic reorganization is triggered based on indicators such as query distribution variance and sub-pool load threshold; and the number, boundaries, and scale of sub-pools are adaptively adjusted through clustering algorithms to ensure that the grouping strategy always conforms to the actual business scenario. At the scheduling level, queries are precisely routed to matching sub-pools based on resource consumption parameters, and abnormal queries are automatically assigned to high-load pools for isolation and processing. Within each sub-pool, a "minimum cumulative occupancy time priority" strategy is used to allocate connections, prioritizing the reuse of high-efficiency connection resources. Differentiated waiting timeout mechanisms are set for different sub-pool types (e.g., 50ms for low-load sub-pools, 500ms for high-load sub-pools) to balance response speed with the fault tolerance requirements of complex queries. After a connection is returned, idle connections are dynamically reclaimed based on sub-pool characteristics (reclaimed after 30 seconds for low-load pools, and after 120 seconds for high-load pools), improving resource turnover efficiency. Furthermore, by monitoring key metrics such as query response time and sub-pool timeout rate in real time, parameter fine-tuning (e.g., expanding the sub-pool adaptation range, adjusting the maximum number of connections) or group reorganization is triggered, forming a closed-loop optimization of "feature-grouping-load". Ultimately, three core improvements are achieved: First, resource utilization is significantly optimized, avoiding idleness in fixed groups and resource contention in a single pool; second, response speed is accelerated, eliminating "small queries waiting for large queries" blockage by isolating simple queries from complex queries; and third, dynamic adaptation capabilities are enhanced, eliminating the need for manual intervention in scenarios with sudden load changes such as e-commerce promotions, reducing operation and maintenance costs and ensuring system stability.

[0075] The methods provided in this application will be described in detail below based on the corresponding implementation methods in some practical application scenarios.

[0076] Figure 1 This is a flowchart illustrating a dynamic grouping and connection scheduling method for a database connection pool provided in one embodiment of this application, as shown below. Figure 1 As shown, this application provides a method for dynamic grouping and connection scheduling of a database connection pool, the method comprising:

[0077] S101. Based on the structured query statement, parse the statement structure and extract a multi-dimensional feature set;

[0078] In the database field, structured query statements can include operation keywords, number of table names, logical operators, nesting levels, database metadata, query conditions, and transaction relationships.

[0079] Therefore, based on the structured query statement, the operation keywords, number of table names, logical operators, nesting level, database metadata, query conditions and transaction associations can be obtained, and the query type, number of tables involved, condition complexity, data volume estimate and transaction attributes corresponding to the structured query structure can be determined.

[0080] The feature values ​​and corresponding feature scores of the query type, the number of tables involved, the condition complexity, the data volume estimate, and the transaction attributes are quantified to obtain a multidimensional feature set corresponding to the structured query statement.

[0081] In the following implementations, examples from specific application scenarios will be used to illustrate the form and function of structured query statements in more detail.

[0082] S102. Obtain resource consumption parameters based on the multidimensional feature set;

[0083] Resource consumption parameters can be determined based on the expected functions and structure of the structured query statement. The resource consumption parameters are obtained by weighted summation of the feature scores corresponding to the query type, the number of tables involved, the condition complexity, the estimated data volume, and the transaction attributes. The specific implementation method will then be explained in conjunction with the specific application scenario.

[0084] Therefore, five-dimensional features are extracted from the query statement to comprehensively cover the resource consumption attributes of the query; qualitative features are transformed into feature scores through quantification rules, and resource consumption parameters are generated by weighted summation. This design unifies the complex attributes of heterogeneous queries into computable numerical indicators, provides standardized input for dynamic grouping, ensures the objectivity of cluster analysis and the accuracy of grouping results, thereby supporting fine-grained scheduling of connection pool resources.

[0085] S103. Dynamically divide the connection sub-pools through cluster analysis and determine the scheduling characteristics of each connection sub-pool;

[0086] The scheduling feature is used to indicate the performance adaptation of statements in each connection sub-pool and the connection performance of each connection sub-pool.

[0087] As an optional implementation, the step of dynamically dividing the connection sub-pools through cluster analysis and determining the scheduling characteristics of each connection sub-pool includes:

[0088] Based on the preset resource consumption parameter range, the basic sub-pool groups are divided;

[0089] Based on the basic sub-pool group, multiple target connection sub-pools are determined through cluster analysis of the resource consumption parameters, and the target resource consumption parameter range and the maximum number of connections for each target connection sub-pool are determined.

[0090] First, basic sub-pools are divided according to preset intervals. Then, cluster analysis is used to optimize them into target connection sub-pools, and their resource consumption intervals and maximum number of connections are determined. By combining rule initialization and data-driven clustering adjustments, basic availability at system startup is ensured, while the group boundaries and scale are dynamically optimized based on actual query distribution, avoiding resource fragmentation. This improves the flexibility and scientific nature of the grouping strategy, adapting to the different resource requirements of different types of queries.

[0091] As an optional implementation, the cluster analysis includes:

[0092] The resource consumption parameters of a second number of the structured query statements are determined as clustering samples;

[0093] The number of clusters is determined based on the elbow rule;

[0094] The corresponding reference sample is determined based on the number of clusters;

[0095] Each of the reference samples is used as the initial cluster center. The distance between each cluster sample and each of the initial cluster centers is calculated and assigned. The cluster centers are updated until the cluster centers converge. Multiple connection sub-pools corresponding to the number of clusters are determined.

[0096] And, the method for determining the maximum number of connections in the sub-pool includes:

[0097] The maximum number of connections for each sub-pool is calculated based on the number of clustered samples corresponding to each target connection sub-pool, as well as the preset global maximum number of connections and load adjustment coefficient.

[0098] The load adjustment coefficient is determined based on the database processor utilization rate.

[0099] Therefore, the elbow rule is used to determine the optimal number of clusters, ensuring a reasonable number of sub-pools. By iteratively updating the centroids of clustered samples, a stable sub-pool partitioning interval is formed after convergence. The maximum number of connections in a sub-pool is dynamically calculated based on the proportion of clustered samples, the global connection limit, and real-time CPU load, allowing connection resources to be flexibly allocated according to the query ratio and system status. This design balances group stability and load sensitivity, improving the rationality of resource allocation while reducing computational overhead.

[0100] S104. Based on the resource consumption parameters, each structured query statement is allocated to each connection sub-pool, and the connection allocation operation of each structured query statement is executed in each connection sub-pool.

[0101] In a specific implementation, this step may include:

[0102] Based on the resource consumption parameters, each structured query statement is assigned to a target connection sub-pool that matches the target resource consumption parameter range;

[0103] If none of the target resource consumption parameter ranges of the target connection sub-pools cover the resource consumption parameters corresponding to the target statement, then the target statement is assigned to the target connection sub-pool where the lower limit of the target resource consumption parameter range exceeds a preset threshold.

[0104] Within each target connection sub-pool, idle connections are allocated based on the cumulative occupancy time of each connection.

[0105] When there are no idle connections in the corresponding target connection sub-pool, a connection whose waiting time meets the preset waiting time condition is matched according to the target resource consumption parameter range of the target connection sub-pool.

[0106] After the process corresponding to the structured query statement ends, the connection is returned to the corresponding target connection sub-pool, and the idle connection is removed according to the idle connection retention time corresponding to the target resource consumption parameter range of each target connection sub-pool.

[0107] Queries are routed to target sub-pools matching resource consumption ranges, with non-routine queries adaptively assigned to high-load sub-pools. Within each sub-pool, connections with shorter cumulative occupancy times are prioritized to improve the reuse rate of short queries. A differentiated wait timeout mechanism is implemented (50ms for low-load sub-pools and 500ms for high-load sub-pools) to balance response speed and fault tolerance. After a connection is returned, an idle retention period is set according to the sub-pool type to optimize resource reclamation efficiency. This achieves three-level scheduling optimization, reducing query waiting latency and avoiding connection idle waste.

[0108] This implementation extracts multi-dimensional features by parsing structured query statements, accurately characterizing query complexity and resource requirements. Based on these multi-dimensional features, it calculates resource consumption parameters, providing a quantitative basis for dynamic grouping of the connection pool. Cluster analysis is used to divide connection sub-pools and determine scheduling characteristics, ensuring precise matching between sub-pool resources and query characteristics. Queries are allocated to suitable sub-pools for connection operations based on resource consumption parameters, achieving resource isolation and load balancing. This solves the resource allocation imbalance problem caused by fixed grouping, prevents simple queries from being blocked by complex queries, and improves connection utilization and system response efficiency.

[0109] Furthermore, this application also provides an implementation method for dynamic scheduling, specifically, the method further includes:

[0110] Continuously monitor and dynamically adjust indicators, and trigger sub-pool reorganization or scheduling strategy update based on the monitoring results of the dynamic adjustment indicators;

[0111] The dynamic adjustment indicators include the distribution of resource consumption parameters corresponding to each structured query statement, sub-pool load parameters, and database operating parameters;

[0112] The sub-pool reorganization includes:

[0113] If the distribution of resource consumption parameters of a first number of structured query statements reaches a preset parameter condition, or the load parameter of any sub-pool in a connection sub-pool reaches a preset condition, or a first duration is reached, then the splitting, merging, or scaling of each connection sub-pool is triggered.

[0114] The scheduling policy update includes:

[0115] For the target connection sub-pool, the optimization parameters corresponding to the target sub-pool are determined based on the sub-pool load parameters or abnormal parameters in the database operation parameters, and the scheduling characteristics of the target sub-pool are updated.

[0116] In some specific implementations, the monitoring result triggering conditions include: the average response time of the sub-pool exceeds the threshold range corresponding to its type; the fluctuation of the sub-pool connection utilization rate exceeds the dispersion threshold; the sub-pool waiting timeout ratio exceeds the timeout rate threshold; and the sub-pool idle connection ratio exceeds the idle threshold range.

[0117] When the average response time exceeds the limit, gradually increase the upper limit of the number of sub-pool connections proportionally; when the waiting timeout ratio exceeds the limit, increase the waiting time threshold for the corresponding sub-pool type; when the idle connection ratio exceeds the limit, gradually decrease the upper limit of the number of sub-pool connections proportionally; only one parameter is adjusted at a time, and the effect is verified and the configuration is fixed after adjustment.

[0118] This implementation continuously monitors dynamic indicators such as query distribution, sub-pool load, and database status, triggering sub-pool reorganization or strategy updates based on preset conditions. When query distribution changes abruptly, sub-pool load becomes abnormal, or a periodic threshold is reached, the sub-pool structure is automatically adjusted (split / merge / size change); sub-pool scheduling characteristics are optimized for abnormal parameters to adapt to business changes in real time. This forms a closed-loop adjustment mechanism, reducing the need for manual intervention, improving the system's adaptability in scenarios with fluctuating traffic (such as e-commerce promotions), and ensuring the continuous effectiveness of resource allocation.

[0119] Please see Figure 2 , Figure 2 This is a schematic diagram of the system architecture corresponding to the dynamic grouping and connection scheduling method of the database connection pool provided in one embodiment of this application. The following will describe the various implementation methods of this application in conjunction with specific application scenarios, through the distribution mode of the system architecture and the data flow and control flow.

[0120] This application system includes six core architectures and auxiliary architectures, among which the core architectures include:

[0121] 1. Query statement parsing module: Receives SQL query requests from the application layer, performs lexical and syntactic analysis on the SQL statements, generates a structured syntax tree, and provides a foundation for feature extraction.

[0122] 2. Feature Extraction and Quantization Module: Extracts multi-dimensional features of the query statement from the syntax tree and transforms the features into calculable numerical indicators through a quantization model.

[0123] 3. Connection Pool Dynamic Grouping Module: Based on quantized query features and load monitoring data, the module dynamically divides connection sub-pools using a clustering algorithm to determine the connection scale and adaptation characteristics of each sub-pool.

[0124] 4. Load monitoring module: Real-time collection of load (such as connection utilization, average occupancy time) and database server status (such as CPU utilization, IO wait time) of each connection sub-pool.

[0125] 5. Connection scheduling module: Based on the quantification results of the query characteristics, the module routes the query request to the appropriate connection sub-pool and distributes connections within the sub-pool using a load balancing strategy.

[0126] 6. Dynamic Adjustment Module: Based on changes in query feature distribution and load data, trigger the splitting, merging, or scaling of connection sub-pools, and update grouping rules and scheduling strategies.

[0127] Feature extraction (implementation of query parsing module, feature extraction and quantization module):

[0128] This application selects features from 5 key dimensions, covering core attributes such as query type, complexity, and resource consumption. The extraction method for each feature is as follows:

[0129] Query type (T):

[0130] Keywords are identified from the SQL syntax tree and categorized into 5 types:

[0131] T1: Read-only simple query (containing only SELECT, no JOIN or subqueries, such as "SELECT age FROM userWHERE id=100");

[0132] T2: Read-only complex queries (SELECT + JOIN / subquery / aggregate functions, such as "SELECT AVG(price) FROM product JOIN order ON product.id=order.pid");

[0133] T3: Single table write operation (INSERT / UPDATE / DELETE, involving only 1 table, such as "UPDATE user SETname='test' WHERE id=100");

[0134] T4: Multi-table write operations (INSERT / UPDATE / DELETE+JOIN, involving 2 or more tables, such as "DELETE o FROM order o JOIN user u ON o.uid=u.id WHERE u.status=0");

[0135] T5: DDL / DCL operations (CREATE, ALTER, GRANT, etc., such as "CREATE INDEX idx_user_id ONuser (id)").

[0136] Number of tables involved (N): Counts the number of duplicate table names in the FROM and JOIN clauses of the SQL statement. The value range is 1, 2, 3, or ≥4.

[0137] Conditional complexity (C): The number of logical operators and nesting levels used in calculations based on the WHERE clause and JOIN conditions.

[0138] Unconditional (C0): No WHERE clause, no JOIN condition;

[0139] Simple condition (C1): 1 condition (e.g., "WHERE id=100"), no logical operators (AND / OR / NOT).

[0140] Medium condition (C2): 2-3 conditions, including 1-2 logical operators, with no nesting;

[0141] Complex conditions (C3): ≥4 conditions or containing nested logic (e.g., "WHERE (id>100 AND status=1)OR (id<50 AND status=2)");

[0142] Super complex conditions (C4): containing subquery conditions (such as "WHERE id IN (SELECT uid FROM orderWHERE amount>1000)").

[0143] Data volume estimation (D): Based on database metadata (number of table rows, index distribution) and query conditions, estimate the amount of data involved in the query:

[0144] D1: ≤1000 records (e.g., querying primary key, unique index);

[0145] D2: 1001-10000 records (e.g., querying the range of non-indexed fields).

[0146] D3: 10001-100000 records (e.g., fuzzy search without an index);

[0147] D4: ≥100001 records (e.g., full table scan, large table aggregation query).

[0148] Transaction attribute (Tr): Identifies whether the query is associated with a transaction and the transaction type.

[0149] Tr0: No transaction (independent SQL statement, no BEGIN / COMMIT);

[0150] Tr1: Read-only transaction (the transaction contains only SELECT operations);

[0151] Tr2: Read-write transaction (the transaction contains write operations or mixed operations).

[0152] 2. Feature Quantization Model (Implementation)

[0153] To facilitate grouped calculations, the qualitative features mentioned above are converted into quantitative scores, using a weighted summation model of "feature score × weight", as shown in the following formula: Total quantitative score S = (T_score × 0.25) + (N_score × 0.15) + (C_score × 0.3) + (D_score × 0.2) + (Tr_score × 0.1)

[0154] The score mapping table for each feature is shown in Table 1:

[0155] Table 1

[0156]

[0157] 3. Quantification Example

[0158] Taking three typical queries from an e-commerce system as examples, the quantification process is demonstrated:

[0159] Query 1 (Product Details Query): SELECT name, price, stock FROM product WHERE id=500

[0160] Feature extraction: T=T1 (read-only simple query), N=1, C=C1 (single condition), D=D1 (primary key query, ≤1000 records), Tr=Tr0 (no transaction).

[0161] Score calculation: (1×0.25)+(1×0.15)+(2×0.3)+(1×0.2)+(1×0.1) = 0.25+0.15+0.6+0.2+0.1 = 1.3 points

[0162] Query 2 (Order Status Update): BEGIN TRANSACTION; UPDATE order SET status=2WHERE order_no='O20231001' AND uid=10086; COMMIT;

[0163] Feature extraction: T=T3 (single table write operation), N=1, C=C2 (2 conditions, 1 AND), D=D1 (unique index of order number, ≤1000 records), Tr=Tr2 (read and write transactions).

[0164] Score calculation: (2×0.25)+(1×0.15)+(3×0.3)+(1×0.2)+(3×0.1) = 0.5+0.15+0.9+0.2+0.3 = 2.05 points

[0165] Query 3 (Monthly Sales Statistics): SELECT p.category, SUM(o.amount) AS total FROM product p JOIN order o ON p.id=o.pid WHERE o.create_time BETWEEN '2023-10-01' AND '2023-10-31' GROUP BY p.category;

[0166] Feature extraction: T=T2 (read-only complex query), N=2, C=C2 (1 JOIN condition + 1 range condition), D=D3 (monthly order data, 10,000-100,000 records), Tr=Tr0 (no transactions).

[0167] Score calculation: (3×0.25)+(2×0.15)+(3×0.3)+(3×0.2)+(1×0.1) = 0.75+0.3+0.9+0.6+0.1 = 2.65 points

[0168] Connection pool dynamic grouping implementation:

[0169] 1. Core logic of grouping

[0170] Based on the total quantized score S of the query features, a hybrid algorithm of "rule + K-means clustering" is used to dynamically divide the connection sub-pools. The core idea is to map queries with similar scores and similar resource consumption attributes to the same sub-pool, so as to achieve "one type of query corresponds to one sub-pool".

[0171] 2. Grouping Implementation Steps

[0172] Step 1: Initialize the group (during system startup)

[0173] Three basic sub-pools are preset, divided based on an initial threshold of quantization scores:

[0174] Low-load sub-pool (S∈[1.0, 1.8)): Adapted for simple queries (such as query 1), initial number of connections = number of core threads × 2;

[0175] Medium-load sub-pool (S∈[1.8, 3.0)): Adapted for queries of medium complexity (such as query 2, query 3), initial number of connections = number of core threads × 3;

[0176] High-load sub-pool (S∈[3.0, 5.0]): Adapted to complex write operations and DDL operations, initial number of connections = number of core threads × 1 (low concurrency, high resource consumption).

[0177] The core thread count is the number of CPU cores of the application server (e.g., for a 16-core server, the core thread count = 16).

[0178] Step 2: Dynamic Grouping Triggering Conditions

[0179] Grouping adjustments (splits, merges, size changes) are triggered when any of the following conditions are met:

[0180] Scheduled trigger: Group assessment is performed every 5 minutes;

[0181] Load trigger: The connection utilization rate (current active connections / maximum number of connections) of a certain sub-pool exceeds 80% or falls below 20% for three consecutive times (each time with a 10-second interval);

[0182] Feature distribution trigger: The variance of the quantization score of the most recent 1000 queries is ≥0.5 (indicating a significant change in the query feature distribution).

[0183] Step 3: K-means clustering (during adjustment)

[0184] Sample data: The quantitative scores S of the most recent 1000 queries are collected to form a sample set {X1, X2, ...,X1000};

[0185] Clustering process:

[0186] 1. Determine the number of clusters K: Calculate the optimal K value based on the Elbow Method (range 2-5, to avoid excessive grouping leading to resource dispersion);

[0187] 2. Initialize cluster centers: Randomly select K samples as initial centers (e.g., when K=4, select samples with S=1.2, 1.9, 2.8, and 4.5).

[0188] 3. Iterative clustering: Calculate the Euclidean distance between each sample and each center, and assign the sample to the nearest cluster; update the center of each cluster (take the average of all samples in the cluster); repeat the iteration until the center is stable (change ≤ 0.01);

[0189] 4. Determine sub-pool rules: Each cluster corresponds to a connection sub-pool. The score range of the cluster is the fitting feature of the sub-pool (e.g., if the cluster center is 1.3 and the sample range is [1.0, 1.6), then the sub-pool is suitable for queries S∈[1.0, 1.6).

[0190] Step 4: Dynamically adjust the size of the sub-pool

[0191] The maximum number of connections for each sub-pool is calculated using the following formula: Maximum number of connections for sub-pool M = (Percentage of queries for this sub-pool × Global maximum number of connections) × Load factor

[0192] Sub-pool query percentage: The proportion of the most recent 1000 queries allocated to this sub-pool;

[0193] Global maximum number of connections: System preset limit (e.g., 200);

[0194] Load factor: Calculated based on database server CPU utilization (1.2 when CPU ≤ 50%, 1.0 when 50%-80%, and 0.8 when ≥ 80%).

[0195] 3. Grouping Example (E-commerce Promotion Scenario)

[0196] When the system starts, it initializes 3 sub-pools. During the promotional period (October 1st, 00:00), the distribution of query characteristics changed as follows: the proportion of order update queries (T3 type) increased from 20% to 50%, with scores concentrated between 1.9 and 2.2; the proportion of product queries (T1 type) decreased from 60% to 30%, with scores concentrated between 1.0 and 1.3; the proportion of sales statistics queries (T2 type) was 10%, with scores between 2.5 and 2.8; and the proportion of DDL operations was 10%, with scores between 4.5 and 5.0.

[0197] After triggering the grouping adjustment, K-means clustering yielded the optimal K=4, resulting in the final grouping:

[0198] Sub-pool 1: Adapts to S∈[1.0,1.4) (product query), query percentage 30%, load factor 1.0, maximum number of connections = 30%×200×1.0=60;

[0199] Sub-pool 2: Adapts to S∈[1.4,2.3) (order update query), query percentage 50%, load factor 1.0, maximum number of connections = 50%×200×1.0=100;

[0200] Sub-pool 3: Adapts to S∈[2.3,3.0) (sales statistics query), query percentage 10%, load factor 1.0, maximum number of connections = 10%×200×1.0=20;

[0201] Sub-pool 4: Adapts to S∈[4.5,5.0] (DDL operations), with a query rate of 10%, a load factor of 0.8, and a maximum number of connections = 10%×200×0.8=16.

[0202] Connection scheduling strategy implementation:

[0203] 1. Core scheduling logic

[0204] A three-tiered scheduling strategy of "group routing + sub-pool load balancing + connection reuse" is adopted to ensure that query requests are quickly matched with suitable connections and to avoid resource contention.

[0205] 2. Specific scheduling steps

[0206] Step 1: Packet Routing

[0207] After receiving the query request, the score S is calculated through the feature extraction and quantization module;

[0208] Iterate through all connection sub-pools, find the adaptation score range where S is located, and determine the target sub-pool (e.g., if S = 1.3 points, route to sub-pool 1).

[0209] If S exceeds all sub-pool ranges (e.g., due to abnormal queries), it will be routed to the high-load sub-pool.

[0210] Step 2: Load balancing within sub-pools

[0211] Connections within the target sub-pool are allocated using a "minimum occupied time priority + idle timeout priority" strategy.

[0212] Maintain the "most recent usage time" and "cumulative usage time" for each connection;

[0213] Prioritize idle connections with the shortest cumulative usage time (adapt to short queries and improve reusability);

[0214] If there are no free connections in the sub-pool, determine whether to allow waiting:

[0215] Low / Medium Load Sub-pool: Waiting time ≤ 50ms, if timeout occurs, return "Connection Busy";

[0216] High-load sub-pool: Waiting time ≤ 500ms (higher tolerance for complex queries), if timeout occurs, "connection busy" will be returned;

[0217] Step 3: Connection Return and Reuse

[0218] After the query is completed, the connection is returned to the original allocation sub-pool and marked as "idle".

[0219] The timeout for idle connections is set according to the sub-pool type: low-load sub-pool (30 seconds, high reuse rate), medium-load sub-pool (60 seconds), and high-load sub-pool (120 seconds, to avoid frequent creation). Because the low-load sub-pool is suitable for simple queries (such as single-table read-only queries), which are characterized by high concurrency and short occupancy time (usually ≤100ms), the connection reuse frequency is extremely high. The short timeout design of 30 seconds can quickly release long-term idle connections (such as idle connections after a sudden traffic peak), avoid connection resources being idle and occupy, and ensure that new high-concurrency queries can quickly obtain connections.

[0220] If the connection idle time exceeds the threshold, it will be automatically closed and removed from the sub-pool to release resources.

[0221] 3. Scheduling Example (Continuing from the grouping example above)

[0222] Scenario 1: User queries product details (S=1.3 points)

[0223] Routing to sub-pool 1 (adapting to [1.0, 1.4));

[0224] Sub-pool 1 currently has 40 idle connections. Select the connection with the shortest cumulative usage time (cumulative usage of 1.2 seconds).

[0225] The query was executed (taking 50ms), the connection was returned to sub-pool 1, and marked as idle.

[0226] Scenario 2: User submits an order (S=2.0 points)

[0227] Routing to sub-pool 2 (adapting to [1.4, 2.3));

[0228] Sub-pool 2 currently has no idle connections. Wait 30ms for a connection to be returned and then allocate that connection.

[0229] Execute the transaction (takes 200ms), and return the connection to sub-pool 2.

[0230] Scenario 3: Administrator performs DDL operation (S=4.8 points)

[0231] Routing to sub-pool 4 (adapted to [4.5, 5.0]);

[0232] Sub-pool 4 has 3 free connections; allocate 1 of them.

[0233] - Execute DDL (takes 3 seconds), connection returned to sub-pool 4.

[0234] Dynamic adjustment module implementation:

[0235] The dynamic adjustment module is the core of maintaining the effectiveness of grouping and scheduling, achieving closed-loop optimization of "query features - grouping - load":

[0236] 1. Data Acquisition: Real-time acquisition of query feature data (score distribution is statistically analyzed every 100 queries), sub-pool load data (connection utilization, average wait time), and database status data (CPU / IO / memory utilization);

[0237] 2. Rule Evaluation: Periodically check the adaptability of the current grouping rules (e.g., if the average query response time of a certain sub-pool exceeds 500ms, it indicates that the grouping rules need to be adjusted).

[0238] The core evaluation indicator system is shown in Table 2:

[0239] Table 2

[0240]

[0241] 3. Policy Update: If the evaluation fails, trigger group adjustment (re-clustering) or scheduling policy optimization (such as adjusting the maximum number of connections in the sub-pool or the waiting time threshold).

[0242] Scheduling strategy optimization trigger condition: Only one sub-pool has ≤2 metrics failing.

[0243] Parameter optimization mapping table: Based on the failed indicators, the corresponding optimization parameters and adjustment logic are identified, as shown in Table 3.

[0244] Table 3

[0245] Failure to meet the target Optimize parameters Adjust logic Sub-pool average response time (RT) is too high Maximum number of connections in sub-pool Each adjustment increases the value by 10% (e.g., from 60 to 66), with the maximum value being 1.5 times the original maximum. High Timeout Rate (OTR) Sub-pool query wait time threshold Low / Medium Load Subpool: 50ms → 70ms (maximum 100ms); High Load Subpool: 500ms → 600ms (maximum 800ms) High percentage of idle connections (IR) Maximum number of connections in sub-pool Each adjustment reduces the limit by 15% (e.g., from 60 to 51), with the lower limit being the number of cores and threads × 1 (e.g., from 16 cores to 16). Excessive fluctuation in connection utilization (CV) Maximum cross-group borrowing ratio Adjusted from 30% to 20% (to reduce the impact of cross-group borrowing on the load). High matching deviation rate (DR) Sub-pool adaptation score range Expand the interval to ±0.1 (e.g., from [1.4, 2.3) to [1.3, 2.4)) to avoid over-selection.

[0246] 2) Parameter adjustment process:

[0247] - Single-parameter incremental adjustment: Only one parameter is optimized at a time (sorted by "impact priority": maximum number of connections → waiting time threshold → adaptation interval → cross-group borrowing ratio), avoiding uncontrollable effects caused by adjusting multiple parameters;

[0248] - Effect verification: Observe for 5 minutes after adjustment. If the corresponding non-compliance indicator meets the standard, the adjustment result is retained; if it does not meet the standard, continue to adjust according to the same logic (up to 3 times). If it still does not meet the standard, "Grouping rule adjustment" is triggered.

[0249] - Parameter solidification: After the adjusted indicators have been stable and met for more than 10 minutes, the new parameters will be solidified as the default configuration of the current sub-pool.

[0250] 4. Smooth switching: When a new grouping rule or scheduling strategy takes effect, a "gray-scale switching" approach is adopted (first route 10% of queries to the new rule, and gradually increase it to 100% after no abnormalities are found) to avoid system jitter.

[0251] Therefore, the key points of this application are:

[0252] Methods for extracting and quantifying multi-dimensional features of query statements: Five core features are extracted: query type, number of tables involved, condition complexity, data volume estimation, and transaction attributes. The features are then converted into quantitative scores through a weighted summation model, providing a basis for dynamic grouping.

[0253] The dynamic grouping triggering mechanism and clustering algorithm of the connection pool: Based on three triggering conditions, namely timing, load and feature distribution, the K-means clustering algorithm is used to realize the dynamic division of connection sub-pools and adapt to changes in query features; the size of the sub-pool is dynamically calculated by "query ratio × global connection number × load coefficient".

[0254] A three-level scheduling strategy based on grouping and load is adopted: first, routing to the appropriate sub-pool is performed according to the quantized score; then, load balancing within the sub-pool is achieved through "minimum occupancy time priority"; and finally, connection reuse rate is improved through cross-group temporary borrowing and differentiated idle timeout mechanisms.

[0255] The closed-loop dynamic adjustment mechanism of "feature-grouping-load" is as follows: by collecting multi-dimensional data in real time and regularly evaluating the grouping and scheduling effects, the grouping rules, sub-pool size and scheduling parameters are smoothly updated to ensure that the system continuously adapts to changes in business scenarios.

[0256] Based on real-world application scenarios, this application achieves the following improvements:

[0257] Significantly improved resource utilization: Dynamic grouping enables precise matching of connection resources with query features, avoiding resource idleness in fixed groups and the problem of contention in a single resource pool; the cross-group temporary borrowing mechanism further improves the reuse rate of idle connections. According to tests, the resource utilization rate is 30%-50% higher than that of the existing fixed grouping scheme.

[0258] The system response speed is greatly improved: simple queries and complex queries are isolated in different sub-pools to avoid the blocking scenario of "small queries waiting for large queries"; the load balancing strategy in the sub-pool prioritizes the allocation of high-reusability connections, and the average query response time is reduced by 20%-40% compared with the existing static scheduling scheme (the effect is more significant in high-concurrency scenarios).

[0259] Highly adaptable to various scenarios: No manual configuration modifications are required; it can dynamically adapt to changes in query feature distribution in real time (such as e-commerce promotions, daily operations, and other different scenarios), reducing system maintenance costs and restart downtime.

[0260] Greater stability: Sub-pool isolation reduces the impact of complex queries on the overall connection pool, preventing a single query failure from exhausting all global connections; the dynamic adjustment of the module's smooth switching mechanism reduces system jitter and improves stability in high-concurrency scenarios.

[0261] This application also provides a dynamic grouping and connection scheduling device for a database connection pool, comprising:

[0262] The acquisition module is used to parse the structure of a structured query statement and extract a multi-dimensional feature set.

[0263] The acquisition module is further configured to acquire resource consumption parameters based on the multidimensional feature set;

[0264] The processing module is used to dynamically divide the connection sub-pools through cluster analysis and determine the scheduling characteristics of each connection sub-pool;

[0265] The scheduling feature is used to indicate the performance adaptation of statements in each connection sub-pool and the connection performance of each connection sub-pool.

[0266] The processing module is further configured to allocate each of the structured query statements to each of the connection sub-pools according to the resource consumption parameters, and to perform connection allocation operations for each of the structured query statements in each of the connection sub-pools.

[0267] This implementation extracts multi-dimensional features by parsing structured query statements, accurately characterizing query complexity and resource requirements. Based on these multi-dimensional features, it calculates resource consumption parameters, providing a quantitative basis for dynamic grouping of the connection pool. Cluster analysis is used to divide connection sub-pools and determine scheduling characteristics, ensuring precise matching between sub-pool resources and query characteristics. Queries are allocated to suitable sub-pools for connection operations based on resource consumption parameters, achieving resource isolation and load balancing. This solves the resource allocation imbalance problem caused by fixed grouping, prevents simple queries from being blocked by complex queries, and improves connection utilization and system response efficiency.

[0268] As an optional implementation, the processing module is further configured to:

[0269] Continuously monitor and dynamically adjust indicators, and trigger sub-pool reorganization or scheduling strategy update based on the monitoring results of the dynamic adjustment indicators;

[0270] The dynamic adjustment indicators include the distribution of resource consumption parameters corresponding to each structured query statement, sub-pool load parameters, and database operating parameters;

[0271] The specific methods by which the processing module performs sub-pool reorganization include:

[0272] If the distribution of resource consumption parameters of a first number of structured query statements reaches a preset parameter condition, or the load parameter of any sub-pool in a connection sub-pool reaches a preset condition, or a first duration is reached, then the splitting, merging, or scaling of each connection sub-pool is triggered.

[0273] The specific methods by which the processing module performs scheduling policy updates include:

[0274] For the target connection sub-pool, the optimization parameters corresponding to the target sub-pool are determined based on the sub-pool load parameters or abnormal parameters in the database operation parameters, and the scheduling characteristics of the target sub-pool are updated.

[0275] This implementation continuously monitors dynamic indicators such as query distribution, sub-pool load, and database status, triggering sub-pool reorganization or strategy updates based on preset conditions. When query distribution changes abruptly, sub-pool load becomes abnormal, or a periodic threshold is reached, the sub-pool structure is automatically adjusted (split / merge / size change); sub-pool scheduling characteristics are optimized for abnormal parameters to adapt to business changes in real time. This forms a closed-loop adjustment mechanism, reducing the need for manual intervention, improving the system's adaptability in scenarios with fluctuating traffic (such as e-commerce promotions), and ensuring the continuous effectiveness of resource allocation.

[0276] As an optional implementation, the specific method by which the acquisition module parses the structured query statement and extracts a multi-dimensional feature set includes:

[0277] Based on the structured query statement, obtain the operation keywords, number of table names, logical operators, nesting level, database metadata, query conditions and transaction associations, and determine the query type, number of tables involved, condition complexity, data volume estimate and transaction attributes corresponding to the structured query structure;

[0278] Quantify the query type, the number of tables involved, the condition complexity, the estimated data volume, and the feature values ​​and corresponding feature scores of the transaction attributes;

[0279] The acquisition module obtains resource consumption parameters based on the multidimensional feature set using the following specific methods:

[0280] The resource consumption parameters are obtained by weighted summing of the feature scores corresponding to the query type, the number of tables involved, the condition complexity, the data volume estimate, and the transaction attribute.

[0281] This implementation extracts five-dimensional features from the query statement, including operation type, number of tables, and condition complexity, comprehensively covering the resource consumption attributes of the query. Qualitative features are transformed into feature scores through quantification rules, and resource consumption parameters are generated by weighted summation. This design unifies the complex attributes of heterogeneous queries into calculable numerical indicators, providing standardized input for dynamic grouping, ensuring the objectivity of cluster analysis and the accuracy of grouping results, thereby supporting fine-grained scheduling of connection pool resources.

[0282] As an optional implementation, the processing module dynamically divides the connection sub-pools through cluster analysis and determines the scheduling characteristics of each connection sub-pool in the following specific ways:

[0283] Based on the preset resource consumption parameter range, the basic sub-pool groups are divided;

[0284] Based on the basic sub-pool group, multiple target connection sub-pools are determined through cluster analysis of the resource consumption parameters, and the target resource consumption parameter range and the maximum number of connections for each target connection sub-pool are determined.

[0285] This implementation first divides the system into basic sub-pools according to preset intervals, then optimizes them into target connection sub-pools through cluster analysis, and determines their resource consumption intervals and maximum number of connections. By combining rule initialization and data-driven clustering adjustments, it ensures basic availability at system startup while dynamically optimizing group boundaries and scale based on actual query distribution, avoiding resource fragmentation. This enhances the flexibility and scientific rigor of the grouping strategy, adapting to the varying resource requirements of different query types.

[0286] As an optional implementation, the specific method by which the processing module performs cluster analysis includes:

[0287] The resource consumption parameters of a second number of the structured query statements are determined as clustering samples;

[0288] The number of clusters is determined based on the elbow rule;

[0289] The corresponding reference sample is determined based on the number of clusters;

[0290] Each of the reference samples is used as the initial cluster center. The distance between each cluster sample and each of the initial cluster centers is calculated and assigned. The cluster centers are updated until the cluster centers converge. Multiple connection sub-pools corresponding to the number of clusters are determined.

[0291] And, the specific method by which the processing module determines the maximum number of connections in the sub-pool includes:

[0292] The maximum number of connections for each sub-pool is calculated based on the number of clustered samples corresponding to each target connection sub-pool, as well as the preset global maximum number of connections and load adjustment coefficient.

[0293] The load adjustment coefficient is determined based on the database processor utilization rate.

[0294] This implementation uses the elbow rule to determine the optimal number of clusters, ensuring a reasonable number of sub-pools. It updates the centroids by iteratively updating cluster samples, forming stable sub-pool partitioning intervals after convergence. The maximum number of connections in a sub-pool is dynamically calculated based on the proportion of cluster samples, the global connection limit, and real-time CPU load, allowing connection resources to be flexibly allocated according to query ratios and system status. This design balances group stability and load sensitivity, improving the rationality of resource allocation while reducing computational overhead.

[0295] As an optional implementation, the specific method by which the processing module allocates each structured query statement to each connection sub-pool according to the resource consumption parameters, and executes the connection allocation operation of each structured query statement in each connection sub-pool, includes:

[0296] Based on the resource consumption parameters, each structured query statement is assigned to a target connection sub-pool that matches the target resource consumption parameter range;

[0297] If none of the target resource consumption parameter ranges of the target connection sub-pools cover the resource consumption parameters corresponding to the target statement, then the target statement is assigned to the target connection sub-pool where the lower limit of the target resource consumption parameter range exceeds a preset threshold.

[0298] Within each target connection sub-pool, idle connections are allocated based on the cumulative occupancy time of each connection.

[0299] When there are no idle connections in the corresponding target connection sub-pool, a connection whose waiting time meets the preset waiting time condition is matched according to the target resource consumption parameter range of the target connection sub-pool.

[0300] After the process corresponding to the structured query statement ends, the connection is returned to the corresponding target connection sub-pool, and the idle connection is removed according to the idle connection retention time corresponding to the target resource consumption parameter range of each target connection sub-pool.

[0301] This implementation routes queries to target sub-pools matching resource consumption ranges, adaptively allocating non-routine queries to high-load sub-pools. Within each sub-pool, connections with shorter cumulative occupancy times are prioritized to improve short query reuse rates. A differentiated waiting timeout mechanism is implemented (50ms for low-load sub-pools, 500ms for high-load sub-pools) to balance response speed and fault tolerance. After a connection is returned, an idle retention period is set according to the sub-pool type to optimize resource reclamation efficiency. This achieves three-level scheduling optimization, reducing query waiting latency and avoiding connection idle waste.

[0302] It should be noted that the division of the various modules in the above device is merely a logical functional division. In actual implementation, they can be fully or partially integrated into a single physical entity, or they can be physically separated. Furthermore, these modules can be implemented entirely in software via processing element calls; they can be fully implemented in hardware; or some modules can be implemented by processing element calls to software, while others are implemented in hardware. For example, a processing module can be a separate processing element, or it can be integrated into a chip within the device. Alternatively, it can be stored as program code in the device's memory, and its functions can be called and executed by a processing element. The implementation of other modules is similar. Moreover, these modules can be fully or partially integrated together, or they can be implemented independently. The processing element here can be an integrated circuit with signal processing capabilities. During implementation, each step of the above method or each of the above modules can be completed through integrated logic circuits in the hardware of the processor element or through software instructions.

[0303] Indicatively, such as Figure 3 As shown, Figure 3 This is a schematic diagram of the internal structure of a computer device 300 provided in an embodiment of this application. The computer device 300 can be provided as a server. (Refer to...) Figure 3The computer device 300 includes a processing component 302, which further includes one or more processors, and memory resources represented by memory 301 for storing instructions, such as application programs, that can be executed by the processing component 302. The application programs stored in memory 301 may include one or more modules, each corresponding to a set of instructions. Furthermore, the processing component 302 is configured to execute instructions to perform the methods of any of the embodiments described above.

[0304] The computer device 300 may also include a power supply component 303 configured to perform power management of the computer device 300, a wired or wireless network interface 304 configured to connect the computer device 300 to a network, and an input / output (I / O) interface 305. The computer device 300 may operate on an operating system stored in memory 301, such as Windows Server™, Mac OS X™, Unix™, Linux™, Free BSD™, or similar.

[0305] Those skilled in the art will understand that Figure 3 The structure shown is merely a block diagram of a portion of the structure related to the present application and does not constitute a limitation on the computer device to which the present application is applied. Specific computer devices may include more or fewer components than those shown in the figure, or combine certain components, or have different component arrangements.

[0306] This application provides a storage medium storing computer-readable instructions that, when executed by one or more processors, cause the one or more processors to perform the method provided in any embodiment.

[0307] Finally, it should be noted that in this document, relational terms such as "first" and "second" are used only to distinguish one entity or operation from another, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Furthermore, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitations, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0308] The various embodiments in this specification are described in a progressive manner. Each embodiment focuses on the differences from other embodiments. The various embodiments can be combined as needed, and the same or similar parts can be referred to each other.

[0309] The above description of the disclosed embodiments enables those skilled in the art to make or use this application. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of this application. Therefore, this application is not to be limited to the embodiments shown herein, but is to be accorded the widest scope consistent with the principles and novel features disclosed herein.

Claims

1. A method for dynamic grouping and connection scheduling of database connection pools, characterized in that, The method comprises the following steps: According to the structured query statement, the statement structure is parsed and a multi-dimensional feature set is extracted; According to the multi-dimensional feature set, a resource consumption parameter is obtained; Through clustering analysis, the connection sub-pool is dynamically divided, and the scheduling characteristics of each connection sub-pool are determined; The scheduling characteristics are used to indicate the statement performance adaptation of each connection sub-pool and the connection performance of each connection sub-pool; According to the resource consumption parameter, each structured query statement is distributed to each connection sub-pool, and a connection distribution operation of each structured query statement is performed in each connection sub-pool.

2. The method of claim 1, wherein, The method further comprises the following steps: Continuously monitor the dynamic adjustment indicators, and trigger sub-pool reorganization or scheduling strategy update according to the monitoring results of the dynamic adjustment indicators; The dynamic adjustment indicators include the distribution of the resource consumption parameters corresponding to each structured query statement, sub-pool load parameters, and database running parameters; The sub-pool reorganization comprises the following steps: If the resource consumption parameter distribution of the first number of structured query statements meets the preset parameter condition, or the sub-pool load parameter condition in any connection sub-pool meets the preset condition, or a first time length is reached, the splitting, merging, or size changing of each connection sub-pool is triggered; The scheduling strategy update comprises the following steps: For a target connection sub-pool, according to the abnormal parameters in the sub-pool load parameters or the database running parameters, the optimization parameters corresponding to the target sub-pool are determined, and the scheduling characteristics of the target sub-pool are updated.

3. The method of claim 1, wherein, According to the structured query statement, the statement structure is parsed and a multi-dimensional feature set is extracted, which comprises the following steps: According to the structured query statement, the operation keyword, the number of table names, the logical operator, the nesting level, the database metadata, the query condition, and the transaction association are obtained, and the query type, the number of involved tables, the condition complexity, the data volume estimation, and the transaction attribute corresponding to the structured query structure are determined; The feature values and corresponding feature scores of the query type, the number of involved tables, the condition complexity, the data volume estimation, and the transaction attribute are quantified; According to the multi-dimensional feature set, the resource consumption parameter is obtained, which comprises the following steps: The feature scores corresponding to the query type, the number of involved tables, the condition complexity, the data volume estimation, and the transaction attribute are weighted and summed to obtain the resource consumption parameter.

4. The method of claim 1, wherein, Through clustering analysis, the connection sub-pool is dynamically divided, and the scheduling characteristics of each connection sub-pool are determined, which comprises the following steps: According to the preset resource consumption parameter interval, a basic sub-pool group is divided; According to the basic sub-pool group, the resource consumption parameter is analyzed by clustering to determine a plurality of target connection sub-pools, and the target resource consumption parameter interval and the maximum connection number of each target connection sub-pool are determined.

5. The method of claim 4, wherein, The clustering analysis comprises the following steps: The resource consumption parameters of the second number of structured query statements are determined as clustering samples; The elbow rule is used to determine the number of clusters; According to the number of clusters, the corresponding reference samples are determined; The reference samples are taken as initial clustering centers, distances between the clustering samples and the initial clustering centers are calculated and distribution is performed, clustering centers are updated until the clustering centers converge, and a plurality of connector pools corresponding to the number of clusters are determined; And the determination manner of the maximum number of connections of the sub-pool comprises: According to the number of clustering samples corresponding to each target connector pool, and a preset global maximum number of connections and a load adjustment coefficient, the maximum number of connections of the sub-pool is calculated and obtained; Wherein, the load adjustment coefficient is determined according to the database processor usage rate.

6. The method of claim 4, wherein, The connection distribution operation of each structured query statement in each target connector pool comprises: According to the resource consumption parameter, each structured query statement is distributed to a target connector pool matched with the target resource consumption parameter interval; If the target resource consumption parameter interval of each target connector pool does not cover the resource consumption parameter corresponding to the target statement, the target statement is distributed to the target connector pool whose lower limit of the target resource consumption parameter interval exceeds a preset threshold; In each target connector pool, idle connections are distributed according to the cumulative occupation time of each connection; When there is no idle connection in the corresponding target connector pool, a connection with a waiting time meeting a preset waiting time condition is matched according to the target resource consumption parameter interval of the target connector pool; When the process corresponding to the structured query statement is ended, the connection is returned to the corresponding target connector pool, and idle connections are removed according to the idle connection retention time corresponding to the target resource consumption parameter interval of each target connector pool.

7. A device for dynamic grouping and connection scheduling of database connection pools, characterized in that Comprise: The acquisition module is configured to parse a statement structure and extract a multi-dimensional feature set according to a structured query statement; The acquisition module is further configured to acquire a resource consumption parameter according to the multi-dimensional feature set; The processing module is configured to dynamically divide connector pools by clustering analysis, and determine scheduling features of each connector pool; The scheduling features are used to indicate statement performance adaptation of each connector pool and connection performance of each connector pool. The processing module is further configured to distribute each structured query statement to each connector pool according to the resource consumption parameter, and perform a connection distribution operation of each structured query statement in each connector pool.

8. The apparatus of claim 7, wherein, The processing module is further configured to: Continuously monitor dynamic adjustment indicators, and trigger sub-pool reorganization or scheduling strategy update according to a monitoring result of the dynamic adjustment indicators; The dynamic adjustment indicators comprise a distribution of the resource consumption parameter corresponding to each structured query statement, a sub-pool load parameter, and a database running parameter. The specific manner in which the processing module performs sub-pool reorganization comprises: If the resource consumption parameter distribution of the first number of structured query statements meets a preset parameter condition, or the sub-pool load parameter in any connector pool meets a preset condition, or a first time length is reached, splitting, merging, or scale changing of each connector pool is triggered. The processing module performs the specific manner of updating the scheduling strategy, which comprises: For the target connection sub-pool, according to the abnormal parameter in the sub-pool load parameter or the database running parameter, the optimization parameter corresponding to the target sub-pool is determined, and the scheduling feature of the target sub-pool is updated.

9. A computer device, comprising: The storage medium stores computer readable instructions, and the computer readable instructions are executed by one or more processors to make the one or more processors execute the steps of the method according to any one of claims 1-6.

10. A storage medium, characterized by The storage medium stores computer readable instructions, and the computer readable instructions are executed by one or more processors to make the one or more processors execute the steps of the method according to any one of claims 1-6.

Images