Distributed database query optimization method and system based on multi-agent cooperation
Patent Information
- Application Number
- CN202611214222.2
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2026-08-11
- Publication Date
- 2026-11-03
AI Technical Summary
Existing centralized query optimizers cannot obtain the real runtime status of each node in a distributed database system in real time. This results in the generated execution plan being based on outdated or inaccurate cost estimates, leading to excessively long execution times and an inability to adapt to dynamic changes such as node load fluctuations, data skew, and hotspot sharding.
A multi-agent collaborative approach is adopted. By constructing a weighted task hypergraph, collecting the real-time state vector of each node, generating candidate local solutions, and calculating the total cost through a global coordinating agent, the optimal global execution plan is selected. This plan includes multiple execution stages and checkpoints, and the weight coefficients are dynamically adjusted to optimize the query optimization process.
It improves the accuracy and efficiency of query optimization, reduces network transmission and temporary table overhead, avoids a single slow node dragging down the entire query, and adapts to performance bottlenecks in high-concurrency and large-data-volume scenarios.
Smart Images
Figure CN123030633A_ABST
Abstract
Description
Technical Field
[0001] This invention relates to the field of data query technology, specifically to a distributed database query optimization method and system based on multi-agent collaboration. Background Technology
[0002] Distributed database systems are widely used in scenarios involving large amounts of data and high concurrency. A single query typically requires multiple nodes to collaborate on scanning, filtering, joining, aggregation, and result summarization. The quality of query optimization directly determines execution efficiency and system stability.
[0003] Currently, mainstream distributed databases employ centralized query optimizers, whose workflow involves generating a global execution plan on a single coordinating node based on data statistics (such as table row count, index cardinality, column value distribution, etc.) and fixed rules. This approach can meet requirements when data scale is controllable and cluster status is stable. However, in actual production environments, distributed clusters face the following dynamic changes: node load fluctuations, data skew and hotspot sharding, and resource contention caused by multiple concurrent queries.
[0004] Centralized optimizers cannot obtain the real-time runtime status of each node, and the execution plans they generate are often based on outdated or inaccurate cost estimates, resulting in large cost estimation biases. While the plan may appear optimal at the time of generation, it can take excessively long to execute due to node queuing, network congestion, and other factors. Therefore, a distributed database query optimization method and system based on multi-agent collaboration is needed to address these problems. Summary of the Invention
[0005] In view of the shortcomings of the existing technology, the purpose of this invention is to provide a distributed database query optimization method and system based on multi-agent cooperation, so as to solve the problems existing in the above-mentioned background technology.
[0006] This invention is implemented as follows: a distributed database query optimization method based on multi-agent cooperation, the method comprising the following steps:
[0007] Receive and parse user query statements, generate logical query plans, construct a weighted task hypergraph H=(V,E), where vertices V represent atomic operations and hyperedges E represent data dependencies or resource competition relationships, and generate initial task allocation anchor points.
[0008] Collect the real-time status vector S of each computing node in the distributed database. Vector S includes CPU load, I / O pressure, network latency, cache hit rate, and task queue depth.
[0009] The local optimization agent at each node generates candidate local solutions based on the subtasks assigned to the node in the task hypergraph H, the vector S, and the local data distribution. The candidate local solutions include the expected cost Cn, the lower bound of the cost LB, the upper bound of the cost UB, and the data flow signature.
[0010] All candidate local solutions are collected by a global coordinating agent, and the total cost Ct of each global candidate plan is calculated as follows: Ct = α·ΣCn + β·max(Cn) + γ·ΣSy + δ·ln(1 + ΣH), where Cn is the expected cost of the local solution at each node, ΣCn is the sum of the costs of all nodes, max(Cn) is the maximum cost of a single node, ΣSy is the sum of the costs of all collaboration losses, and ΣH is the sum of historical penalty terms; α, β, γ and δ are dynamic weight coefficients.
[0011] The optimal global execution plan is selected based on the total cost. This plan includes multiple execution phases and checkpoints between phases. The global execution plan is then distributed to each node for execution.
[0012] As a further aspect of the present invention, the step of constructing a weighted task hypergraph H specifically includes:
[0013] Extract all indivisible atomic database operations from the logical query plan, each operation corresponding to a vertex V in the hypergraph;
[0014] Identify data dependencies or resource contention relationships between vertices and connect vertices with the following relationships using hyperedges E: data flow transmission, sequential execution on the same node, and contention for locks or buffer resources on the same node;
[0015] Each hyperedge E is assigned an initial weight, which is calculated based on cardinality estimation, selectivity and operation complexity from database statistics. The weight is used to represent the amount of data transfer or the intensity of computational competition between the operations connected by the hyperedge.
[0016] Based on the topology of the hypergraph H and the data sharding locations involved in each vertex, an initial task assignment anchor point is generated, which assigns a recommended group of computing nodes to each vertex.
[0017] As a further aspect of the present invention, the step of generating candidate local solutions specifically includes:
[0018] Read the subtasks assigned to this node in the task hypergraph H, combine the real-time state vector S of this node with the local data distribution, and search in the local execution plan space to generate at least one candidate execution path;
[0019] For each candidate execution path, calculate the expected cost Cn, calculate the lower bound of the cost LB based on the best case, and calculate the upper bound of the cost UB based on the worst case.
[0020] The data characteristics of the intermediate results generated by the candidate execution paths are analyzed to form a data stream signature. The signature includes whether the data is ordered, the hash uniformity of the distribution key, and the data compression ratio. The signature will be passed to the global coordinating agent to calculate the cooperation loss cost.
[0021] As a further aspect of the present invention, the step of calculating the total cost of each global candidate plan specifically includes:
[0022] Collect all candidate local solutions and extract the expected cost Cn, lower bound LB, upper bound UB, and data stream signature for each solution;
[0023] Identify the collaborative relationships and conflicts between different local solutions, construct a game matrix M, where rows of the matrix correspond to different query subtasks, columns correspond to the local solutions available for each subtask, and matrix elements represent the collaborative loss cost Sy resulting from combining the local solutions. The collaborative loss cost is calculated based on whether the data stream signatures of two adjacent subtasks match.
[0024] Calculate the total cost Ct for each global candidate plan. The historical penalty term is obtained by summing the ratio of the actual time taken by the nodes involved in this plan to the expected time taken when performing similar operations in the past.
[0025] As a further aspect of the present invention, the method also dynamically adjusts the weight coefficients α, β, γ and δ according to the overall load status of the current distributed database cluster: when the overall system load is higher than a preset value one, the weights of β and δ are increased to suppress node overload and repeated historical failure modes; when the overall system load is lower than a preset value two and the network latency is higher than a preset value three, the weight of γ is increased to prioritize reducing cross-node data transmission.
[0026] As a further aspect of the present invention, the method also includes node trust assessment, the specific steps of which are as follows:
[0027] Maintain a trust score T for each computing node, with an initial value of 1.0;
[0028] After completing a global execution plan, the actual time spent on each subtask allocated to the node in the plan is compared with the expected cost. If the ratio of the actual time spent to the expected cost exceeds the first threshold, the trust level of the node is updated exponentially: new T = old Trust × e^(-λ•(actual time spent / expected cost-1)), where λ is the decay coefficient.
[0029] When selecting candidate local solutions, the global coordinating agent prioritizes solutions from high-trust nodes and multiplies the upper bound of the cost UB of low-trust nodes by a penalty factor greater than 1 before participating in the calculation of the total cost Ct.
[0030] Another objective of this invention is to provide a distributed database query optimization system based on multi-agent cooperation, the system comprising:
[0031] The task hypergraph module is used to receive and parse user query statements, generate logical query plans, construct a weighted task hypergraph H=(V,E), where vertices V represent atomic operations, hyperedges E represent data dependencies or resource competition relationships, and generate initial task allocation anchor points.
[0032] The state vector module is used to collect the real-time state vector S of each computing node in the distributed database. Vector S includes CPU load, I / O pressure, network latency, cache hit rate and task queue depth.
[0033] The local scheme module is used to generate candidate local schemes by the local optimization agent on each node based on the subtasks assigned to the node in the task hypergraph H, the vector S, and the local data distribution. The candidate local schemes include the expected cost Cn, the lower bound of the cost LB, the upper bound of the cost UB, and the data flow signature.
[0034] The total cost module is used to collect all candidate local solutions through a global coordinating agent and calculate the total cost Ct for each global candidate plan, Ct=α·ΣCn+β·max(Cn)+γ·ΣSy+δ·ln(1+ΣH), where Cn is the expected cost of the local solution at each node, ΣCn is the sum of the costs of all nodes, max(Cn) is the maximum cost of a single node; ΣSy is the sum of the costs of all collaboration losses, ΣH is the sum of historical penalty terms; α, β, γ and δ are dynamic weight coefficients;
[0035] The execution plan module is used to select the optimal global execution plan based on the total cost. This plan includes multiple execution phases and checkpoints between phases, and distributes the global execution plan to each node for execution.
[0036] Compared with the prior art, the beneficial effects of the present invention are:
[0037] By constructing a weighted task hypergraph, the system accurately captures data dependencies and resource competition among operations, making optimization more closely reflect the actual execution characteristics of the query. Local agents at each node generate candidate solutions with uncertainty intervals (LB, UB) using real-time state vectors S, overcoming the shortcomings of traditional optimizers that rely on outdated statistics. The total cost formula introduces max(Cn) to prevent a single slow node from dragging down the entire query, and ΣSy drives data flow signature matching, reducing unnecessary cross-node data redistribution and sorting, and lowering network transmission and temporary table overhead. The optimization task is distributed across multiple local agents to complete in parallel; the global coordinating agent only needs to combine and evaluate candidate solutions, rather than searching the entire plan space from scratch. This avoids the performance bottleneck of traditional centralized optimizers in large query or high-concurrency scenarios. Attached Figure Description
[0038] Figure 1 This is a flowchart of a distributed database query optimization method based on multi-agent collaboration.
[0039] Figure 2 This is a flowchart illustrating the construction of a task hypergraph in a distributed database query optimization method based on multi-agent collaboration.
[0040] Figure 3 This is a flowchart for generating candidate local solutions in a distributed database query optimization method based on multi-agent cooperation.
[0041] Figure 4 This is a flowchart illustrating the calculation of the total cost in a distributed database query optimization method based on multi-agent cooperation.
[0042] Figure 5 This is a flowchart illustrating node trust evaluation in a distributed database query optimization method based on multi-agent collaboration.
[0043] Figure 6 This is a block diagram of a distributed database query optimization system based on multi-agent collaboration. Detailed Implementation
[0044] To make the objectives, technical solutions, and advantages of this invention clearer, the invention will be further described in detail below with reference to the accompanying drawings and specific embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the invention.
[0045] The specific implementation of the present invention will be described in detail below with reference to specific embodiments.
[0046] like Figure 1 As shown, this embodiment of the invention provides a distributed database query optimization method based on multi-agent cooperation, the method comprising the following steps:
[0047] S100 receives and parses the user query statement, generates a logical query plan, constructs a weighted task hypergraph H=(V,E), where vertices V represent atomic operations, hyperedges E represent data dependencies or resource competition relationships, and generates initial task allocation anchor points.
[0048] In this invention, traditional optimizers only generate a logical plan tree, ignoring complex dependencies and resource contention between operations. This invention introduces a hypergraph structure, decomposing the query into indivisible atomic operations (such as table scans, predicate filtering, joins, etc.) as vertices V, and then connecting multiple vertices with data flow transmission, resource contention, or sequential dependencies using hyperedges E. The weights of the hyperedges are estimated based on statistical information, reflecting the amount of data transmitted or the intensity of contention. The initial task allocation anchor point recommends an execution node group for each vertex based on the data sharding position, providing a reference for subsequent local agents but without imposing constraints.
[0049] S200 collects the real-time status vector S of each computing node in the distributed database. Vector S includes CPU load, I / O pressure, network latency, cache hit rate, and task queue depth.
[0050] In this embodiment of the invention, each node periodically or on demand acquires multi-dimensional states and normalizes them into a vector S. Here, CPU load refers to the normalized run queue length, I / O pressure is the average disk wait time, network latency is the round-trip latency of inter-node communication, cache hit rate refers to the table or index data stored on the node, and task queue depth represents the number of subtasks waiting to be executed. This information is encapsulated and provided to the local optimization agent of the node, and also reported to the global coordination agent for decision-making.
[0051] S300 generates candidate local solutions by using the local optimization agent on each node based on the subtasks assigned to the node in the task hypergraph H, the vector S, and the local data distribution. The candidate local solutions include the expected cost Cn, the lower bound of the cost LB, the upper bound of the cost UB, and the data stream signature.
[0052] In this embodiment of the invention, after each local agent receives the subtask assigned to its node in the hypergraph, it searches for possible paths in its local execution plan space by combining the state S (e.g., avoiding full table scans if I / O is currently busy) and the local data distribution (e.g., whether hot data blocks of a certain index are in the cache). It outputs not only an expected cost Cn, but also a lower bound LB (best case, such as all data being in memory and no contention) and an upper bound UB (worst case, such as I / O thrashing), to express the uncertainty of the solution. Simultaneously, it generates data stream signatures for evaluating cooperation losses during global coordination.
[0053] S400 collects all candidate local solutions through a global coordinating agent and calculates the total cost Ct for each global candidate plan, Ct=α·ΣCn+β·max(Cn)+γ·ΣSy+δ·ln(1+ΣH), where Cn is the expected cost of the local solution at each node, ΣCn is the sum of the costs of all nodes, max(Cn) is the maximum cost of a single node, ΣSy is the sum of the costs of all collaboration losses, ΣH is the sum of historical penalty terms, and α, β, γ and δ are dynamic weight coefficients.
[0054] In this embodiment of the invention, the global coordinating agent gathers all local solutions and calculates the total cost for each combination (i.e., the global candidate plan). In the formula, ΣCn reflects the overall resource consumption, and max(Cn) is a key term ignored by traditional linear formulas, ensuring the total query time is limited by the slowest node; ΣSy is the sum of collaboration loss costs, determined by the matching degree of data stream signatures of adjacent subtasks. The coefficients α, β, γ, and δ are dynamically adjusted according to the overall cluster load, balancing local efficiency, overall bottlenecks, collaboration overhead, and historical experience.
[0055] S500 selects the optimal global execution plan based on the total cost. This plan includes multiple execution phases and checkpoints between phases, and then distributes the global execution plan to each node for execution.
[0056] In this embodiment of the invention, the global candidate plan with the smallest Ct is selected as the final execution plan. Unlike traditional plans, this plan is divided into multiple execution phases, with checkpoints set between phases. At each checkpoint, the node status can be reassessed. This design makes the plan flexible and able to cope with sudden changes in node load or network fluctuations during runtime. Finally, the plan is distributed to each execution node, and the nodes execute according to the assigned subtasks and their order.
[0057] like Figure 2 As shown, in a preferred embodiment of the present invention, the step of constructing a weighted task hypergraph H specifically includes:
[0058] S101, extract all indivisible atomic database operations from the logical query plan, each operation corresponding to a vertex V in the hypergraph;
[0059] S102, Identify the data dependencies or resource competition relationships between vertices, and connect vertices with the following relationships with hyperedges E: data flow transmission, sequential execution on the same node, competition for lock or buffer resources of the same node;
[0060] S103, assign an initial weight to each hyperedge E. The weight is calculated based on the cardinality estimate, selection rate and operation complexity in the database statistics, and is used to represent the amount of data transmission or the intensity of computational competition between the operations connected by the hyperedge.
[0061] S104. Based on the topology of the hypergraph H and the data sharding positions involved in each vertex, an initial task assignment anchor point is generated, wherein the anchor point assigns a recommended group of computing nodes to each vertex.
[0062] In this embodiment of the invention, a hyperedge can connect two or more vertices. Data flow transmission means that the output of one operation is the input of another operation; sequential execution on the same node means that two operations must share temporary results or lock resources on the same node (such as local aggregation followed by local sorting); competition for locks or buffer resources on the same node means that multiple operations may simultaneously request read locks on the same data fragment or share a memory buffer pool. Expressing these relationships with hyperedges allows global optimization to identify which operations should be scheduled to the same node as much as possible to reduce network transmission, or conversely, which should be distributed to reduce resource contention. Example of weight calculation: For hyperedges that transmit data streams, weight = number of data rows entering the source operation × selection rate of the source operation × computational complexity coefficient of the source operation. The number of data rows is obtained through cardinality estimation from database statistics. The selection rate represents the proportion of data remaining after filtering by the operation, with a value range of (0,1]. The complexity coefficient is a factor greater than or equal to 1. For example, the complexity coefficient of a full table scan is 1, that of an index scan is 0.8 (due to fewer I / O operations), that of a hash join is 1.2, and that of a nested loop join is 2.0. For hyperedges with resource contention, weight = Σ(DVi × CPij), where DVi represents the amount of data that operation i is expected to process on this node, based on cardinality estimation and selection rate. The probability of resource conflict between operation i and operation j is calculated using the following steps: CPij represents the probability of resource conflict between operation i and operation j, estimated based on operation type (e.g., high probability of conflict for both write operations, medium probability for read-write operations, and low probability for read-only operations) and concurrent access patterns in statistics. Next, based on the topology of the hypergraph H and the data shard locations of each vertex, an initial task allocation anchor is generated. This anchor is not a mandatory plan but a suggestion. For example, scanning order shards is recommended to be performed on the node containing that shard; if a join operation involves a small table, it is recommended to broadcast the small table to all nodes. Anchors are generated using simple heuristic rules to reduce the search space required by local agents and improve optimization efficiency.
[0063] like Figure 3 As shown, in a preferred embodiment of the present invention, the step of generating candidate local solutions specifically includes:
[0064] S301, Read the subtasks assigned to this node in the task hypergraph H, combine the real-time state vector S of this node with the local data distribution, search in the local execution plan space, and generate at least one candidate execution path;
[0065] S302, for each candidate execution path, calculate the expected cost Cn, calculate the lower bound of the cost LB based on the best case, and calculate the upper bound of the cost UB based on the worst case;
[0066] S303, Analyze the data characteristics of the intermediate results generated by the candidate execution path to form a data stream signature. The signature includes whether the data is ordered, the hash uniformity of the distribution key, and the data compression ratio. The signature will be passed to the global coordination agent to calculate the cooperation loss cost.
[0067] In this embodiment of the invention, the subtask obtained by the local agent may be a vertex or a group of vertices connected by hyperedges. It utilizes the local real-time state S and data distribution (e.g., leaf nodes of a certain index are frequently accessed recently, resulting in a high cache hit rate) to locally enumerate possible execution methods (e.g., index scan, full table scan, bitmap scan, etc.) and generate one or more candidate paths. The expected cost Cn is estimated based on the current state and statistical information. LB assumes all data is in memory, there is no lock contention, and no I / O wait; UB assumes the worst-case scenario (e.g., multiple page swaps, network retransmissions). Data stream signatures play an important role; for example, if the output of a sorting operation is ordered, downstream sorting can be omitted if needed (cooperation benefit). The hash uniformity of the distribution key indicates whether data is prone to skew if hash partitioned by a certain column; if the distribution key of the output after local aggregation is already uniform, the cost of downstream repartitioning is small. The compression ratio helps estimate network throughput. These signatures are passed to the global coordinating agent in the form of lightweight metadata.
[0068] like Figure 4 As shown, in a preferred embodiment of the present invention, the step of calculating the total cost of each global candidate plan specifically includes:
[0069] S401, collect all candidate local solutions, and extract the expected cost Cn, lower bound LB, upper bound UB, and data flow signature for each solution;
[0070] S402, identify the cooperation relationship and conflict between different local solutions, construct the game matrix M, the rows of the matrix correspond to different query sub-tasks, the columns correspond to the local solutions that can be selected for the sub-task, and the matrix elements are the cooperation loss cost Sy generated after combining the local solutions. The cooperation loss cost is calculated based on whether the data stream signatures of two adjacent sub-tasks match.
[0071] S403, calculate the total cost Ct for each global candidate plan. The historical penalty term is obtained by accumulating the ratio of the actual time taken by the nodes involved in this plan to the expected time taken when performing similar operations in the past.
[0072] In this embodiment of the invention, the global coordinating agent summarizes the information reported by all local agents. Each subtask may have multiple candidate solutions, thus requiring combination. The rows of the game matrix M are subtasks (such as task A, task B), and the columns are the possible solutions for each subtask. For a cell M[i][j] in the matrix, when subtask i selects solution j, the cell is not independent but needs to be combined with the solutions of adjacent subtasks to determine Sy. Sy is calculated based on the matching degree of the data stream signatures of the two subtasks. For example, if the output signature of subtask A (scanning) shows that the data is ordered by customer_id, and subtask B (connection) needs to be merged and connected according to customer_id, then Sy is negative (indicating that sorting can be avoided). If there is no match, such as A output being unordered while B needs to be ordered, then Sy is positive (indicating that additional sorting or redistribution is required). The matrix is used to enumerate all combinations. For each combination of schemes (i.e. each global candidate plan), ΣH is calculated as follows: query each node involved in the plan, query the execution history table to record the actual time / expected cost ratio when the node performs the same type of operation (such as hash join), and sum the comparison values to obtain the result.
[0073] In a preferred embodiment of the present invention, the method also dynamically adjusts the weight coefficients α, β, γ, and δ according to the overall load status of the current distributed database cluster: when the overall system load is higher than a preset value one, for example, when the average CPU utilization exceeds 80% or the task queue depth exceeds 5, the weights of β and δ are increased to suppress node overload and repeated historical failure modes; when the overall system load is lower than a preset value two and the network latency is higher than a preset value three, the weight of γ is increased to encourage the optimizer to prioritize the data stream signature matching scheme and reduce unnecessary network transmission.
[0074] like Figure 5 As shown in the preferred embodiment of the present invention, the method further includes node trust assessment, the specific steps of which are as follows:
[0075] S601, maintain a trust score T for each computing node, with an initial value of 1.0;
[0076] S602, after completing a global execution plan, compare the actual time spent on each subtask allocated to the node with the expected cost. If the ratio of the actual time spent to the expected cost exceeds the first threshold, then the trust level of the node is updated exponentially: new T = old Trust × e^(-λ•(actual time spent / expected cost-1)), where λ is the decay coefficient.
[0077] In S603, when selecting candidate local solutions, the global coordinating agent prioritizes solutions from high-trust nodes and multiplies the upper bound of the cost UB of low-trust nodes by a penalty factor greater than 1 before participating in the calculation of the total cost Ct.
[0078] In this embodiment of the invention, trust level is a quantification of the reliability of a node's future behavior. Initially, all nodes have the same trust level. λ is a decay coefficient (e.g., 0.5). If the actual execution time is much greater than expected, the exponential term causes the trust level to drop rapidly. Even if the cost-benefit ratio (UB) of a low-trust node's plan appears low, anomalies may still occur during actual execution. Therefore, UB is multiplied by a penalty factor (e.g., 1.5), which significantly increases the total cost of the global candidate plans containing that plan, thus causing it to be avoided by the optimizer. This achieves proactive avoidance of unreliable nodes.
[0079] like Figure 6 As shown, this embodiment of the invention also provides a distributed database query optimization system based on multi-agent cooperation, the system comprising:
[0080] The task hypergraph module 100 is used to receive and parse user query statements, generate logical query plans, construct a weighted task hypergraph H=(V,E), where vertices V represent atomic operations, hyperedges E represent data dependencies or resource competition relationships, and generate initial task allocation anchor points.
[0081] The state vector module 200 is used to collect the real-time state vector S of each computing node in the distributed database. The vector S includes CPU load, I / O pressure, network latency, cache hit rate and task queue depth.
[0082] The local scheme module 300 is used to generate candidate local schemes by the local optimization agent on each node based on the subtasks assigned to the node in the task hypergraph H, the vector S, and the local data distribution. The candidate local schemes include the expected cost Cn, the lower bound of the cost LB, the upper bound of the cost UB, and the data flow signature.
[0083] The total cost module 400 is used to collect all candidate local solutions through a global coordinating agent and calculate the total cost Ct for each global candidate plan, Ct=α·ΣCn+β·max(Cn)+γ·ΣSy+δ·ln(1+ΣH), where Cn is the expected cost of the local solution at each node, ΣCn is the sum of the costs of all nodes, max(Cn) is the maximum cost of a single node; ΣSy is the sum of the costs of all collaboration losses, ΣH is the sum of historical penalty terms; α, β, γ and δ are dynamic weight coefficients;
[0084] The execution plan module 500 is used to select the optimal global execution plan based on the total cost. This plan includes multiple execution phases and checkpoints between phases, and distributes the global execution plan to each node for execution.
[0085] In this embodiment of the invention, the task hypergraph module 100 includes:
[0086] Atomic operation units are used to extract all indivisible atomic database operations from the logical query plan, with each operation corresponding to a vertex V in the hypergraph;
[0087] Hyperedge connection unit is used to identify data dependencies or resource competition relationships between vertices and connects vertices with the following relationships with hyperedge E: data flow transmission, sequential execution on the same node, and competition for lock or buffer resources on the same node;
[0088] The initial weight unit is used to assign an initial weight to each hyperedge E. The weight is calculated based on cardinality estimation, selectivity and operation complexity in the database statistics and is used to represent the amount of data transmission or the intensity of computational competition between the operations connected by the hyperedge.
[0089] The task allocation unit is used to generate an initial task allocation anchor point based on the topology of the hypergraph H and the data fragmentation positions involved in each vertex. The anchor point assigns a recommended group of computing nodes to each vertex.
[0090] In this embodiment of the invention, the local scheme module 300 includes:
[0091] The candidate path unit is used to read the subtasks assigned to this node in the task hypergraph H, combine the real-time state vector S of this node with the local data distribution, and search in the local execution plan space to generate at least one candidate execution path.
[0092] The expected cost unit is used to calculate the expected cost Cn for each candidate execution path, calculate the lower bound of the cost LB based on the best case, and calculate the upper bound of the cost UB based on the worst case.
[0093] The data stream signature unit is used to analyze the data characteristics of the intermediate results generated by the candidate execution path and form a data stream signature. The signature includes whether the data is ordered, the hash uniformity of the distribution key, and the data compression ratio. The signature will be passed to the global coordinating agent to calculate the cooperation loss cost.
[0094] In this embodiment of the invention, the total cost module 400 includes:
[0095] The information extraction unit is used to collect all candidate local solutions and extract the expected cost Cn, lower cost bound LB, upper cost bound UB, and data stream signature for each solution.
[0096] The game matrix unit is used to identify the cooperation relationship and conflict between different local solutions. The game matrix M is constructed. The rows of the matrix correspond to different query sub-tasks, the columns correspond to the local solutions that can be selected for the sub-task, and the matrix elements are the cooperation loss cost Sy generated after combining the local solutions. The cooperation loss cost is calculated based on whether the data stream signatures of two adjacent sub-tasks match.
[0097] The total cost calculation unit is used to calculate the total cost Ct for each global candidate plan. The historical penalty term is obtained by accumulating the ratio of the actual time taken by the nodes involved in this plan to the expected time taken when performing similar operations in the past.
[0098] The above description only details the preferred embodiments of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of the present invention should be included within the scope of protection of the present invention.
[0099] It should be understood that although the steps in the flowcharts of the various embodiments of the present invention are shown sequentially according to the arrows, these steps are not necessarily executed in the order indicated by the arrows. Unless explicitly stated herein, there is no strict order restriction on the execution of these steps, and they can be executed in other orders. Moreover, at least some steps in the various embodiments may include multiple sub-steps or multiple stages. These sub-steps or stages are not necessarily completed at the same time, but can be executed at different times. The execution order of these sub-steps or stages is not necessarily sequential, but can be performed alternately or in turn with other steps or at least a portion of the sub-steps or stages of other steps.
[0100] Other embodiments of this disclosure will readily occur to those skilled in the art upon consideration of the disclosure in the specification and embodiments. This application is intended to cover any variations, uses, or adaptations of this disclosure that follow the general principles of this disclosure and include common knowledge or customary techniques in the art not disclosed herein. The specification and embodiments are to be considered exemplary only, and the true scope and spirit of this disclosure are indicated by the claims.
Claims
1. A distributed database query optimization method based on multi-agent cooperation, characterized in that, The method includes the following steps: Receive and parse user query statements, generate logical query plans, construct a weighted task hypergraph H=(V,E), where vertices V represent atomic operations and hyperedges E represent data dependencies or resource competition relationships, and generate initial task allocation anchor points. Collect the real-time status vector S of each computing node in the distributed database. Vector S includes CPU load, I / O pressure, network latency, cache hit rate, and task queue depth. The local optimization agent at each node generates candidate local solutions based on the subtasks assigned to the node in the task hypergraph H, the vector S, and the local data distribution. The candidate local solutions include the expected cost Cn, the lower bound of the cost LB, the upper bound of the cost UB, and the data flow signature. All candidate local solutions are collected by a global coordinating agent, and the total cost Ct of each global candidate plan is calculated as follows: Ct = α·ΣCn + β·max(Cn) + γ·ΣSy + δ·ln(1 + ΣH), where Cn is the expected cost of the local solution at each node, ΣCn is the sum of the costs of all nodes, max(Cn) is the maximum cost of a single node, ΣSy is the sum of the costs of all collaboration losses, and ΣH is the sum of historical penalty terms; α, β, γ and δ are dynamic weight coefficients. The optimal global execution plan is selected based on the total cost. This plan includes multiple execution phases and checkpoints between phases. The global execution plan is then distributed to each node for execution.
2. The distributed database query optimization method based on multi-agent cooperation according to claim 1, characterized in that, The steps for constructing a weighted task hypergraph H include: Extract all indivisible atomic database operations from the logical query plan, each operation corresponding to a vertex V in the hypergraph; Identify data dependencies or resource contention relationships between vertices and connect vertices with the following relationships using hyperedges E: data flow transmission, sequential execution on the same node, and contention for locks or buffer resources on the same node; Each hyperedge E is assigned an initial weight, which is calculated based on cardinality estimation, selectivity and operation complexity from database statistics. The weight is used to represent the amount of data transfer or the intensity of computational competition between the operations connected by the hyperedge. Based on the topology of the hypergraph H and the data sharding locations involved in each vertex, an initial task assignment anchor point is generated, which assigns a recommended group of computing nodes to each vertex.
3. The distributed database query optimization method based on multi-agent cooperation according to claim 1, characterized in that, The step of generating candidate local schemes specifically includes: Read the subtasks assigned to this node in the task hypergraph H, combine the real-time state vector S of this node with the local data distribution, and search in the local execution plan space to generate at least one candidate execution path; For each candidate execution path, calculate the expected cost Cn, calculate the lower bound of the cost LB based on the best case, and calculate the upper bound of the cost UB based on the worst case. The data characteristics of the intermediate results generated by the candidate execution paths are analyzed to form a data stream signature. The signature includes whether the data is ordered, the hash uniformity of the distribution key, and the data compression ratio. The signature will be passed to the global coordinating agent to calculate the cooperation loss cost.
4. The distributed database query optimization method based on multi-agent cooperation according to claim 1, characterized in that, The step of calculating the total cost of each global candidate plan specifically includes: Collect all candidate local solutions and extract the expected cost Cn, lower bound LB, upper bound UB, and data stream signature for each solution; Identify the collaborative relationships and conflicts between different local solutions, construct a game matrix M, where rows of the matrix correspond to different query subtasks, columns correspond to the local solutions available for each subtask, and matrix elements represent the collaborative loss cost Sy resulting from combining the local solutions. The collaborative loss cost is calculated based on whether the data stream signatures of two adjacent subtasks match. Calculate the total cost Ct for each global candidate plan. The historical penalty term is obtained by summing the ratio of the actual time taken by the nodes involved in this plan to the expected time taken when performing similar operations in the past.
5. The distributed database query optimization method based on multi-agent cooperation according to claim 4, characterized in that, The method also dynamically adjusts the weight coefficients α, β, γ, and δ according to the overall load status of the current distributed database cluster: when the overall system load is higher than a preset value one, the weights of β and δ are increased to suppress node overload and repeated historical failure modes; when the overall system load is lower than a preset value two and the network latency is higher than a preset value three, the weight of γ is increased to prioritize reducing cross-node data transmission.
6. The distributed database query optimization method based on multi-agent cooperation according to claim 1, characterized in that, The method also includes node trust assessment, with the following specific steps: Maintain a trust score T for each computing node, with an initial value of 1.0; After completing a global execution plan, the actual time spent on each subtask allocated to the node in the plan is compared with the expected cost. If the ratio of the actual time spent to the expected cost exceeds the first threshold, the trust level of the node is updated exponentially: new T = old Trust × e^(-λ•(actual time spent / expected cost-1)), where λ is the decay coefficient. When selecting candidate local solutions, the global coordinating agent prioritizes solutions from high-trust nodes and multiplies the upper bound of the cost UB of low-trust nodes by a penalty factor greater than 1 before participating in the calculation of the total cost Ct.
7. A distributed database query optimization system based on multi-agent cooperation, characterized in that, The system includes: The task hypergraph module is used to receive and parse user query statements, generate logical query plans, construct a weighted task hypergraph H=(V,E), where vertices V represent atomic operations, hyperedges E represent data dependencies or resource competition relationships, and generate initial task allocation anchor points. The state vector module is used to collect the real-time state vector S of each computing node in the distributed database. Vector S includes CPU load, I / O pressure, network latency, cache hit rate and task queue depth. The local scheme module is used to generate candidate local schemes by the local optimization agent on each node based on the subtasks assigned to the node in the task hypergraph H, the vector S, and the local data distribution. The candidate local schemes include the expected cost Cn, the lower bound of the cost LB, the upper bound of the cost UB, and the data flow signature. The total cost module is used to collect all candidate local solutions through a global coordinating agent and calculate the total cost Ct for each global candidate plan, Ct=α·ΣCn+β·max(Cn)+γ·ΣSy+δ·ln(1+ΣH), where Cn is the expected cost of the local solution at each node, ΣCn is the sum of the costs of all nodes, max(Cn) is the maximum cost of a single node; ΣSy is the sum of the costs of all collaboration losses, ΣH is the sum of historical penalty terms; α, β, γ and δ are dynamic weight coefficients; The execution plan module is used to select the optimal global execution plan based on the total cost. This plan includes multiple execution phases and checkpoints between phases, and distributes the global execution plan to each node for execution.
8. The distributed database query optimization system based on multi-agent cooperation according to claim 7, characterized in that, The task hypergraph module includes: Atomic operation units are used to extract all indivisible atomic database operations from the logical query plan, with each operation corresponding to a vertex V in the hypergraph; Hyperedge connection unit is used to identify data dependencies or resource competition relationships between vertices and connects vertices with the following relationships with hyperedge E: data flow transmission, sequential execution on the same node, and competition for lock or buffer resources on the same node; The initial weight unit is used to assign an initial weight to each hyperedge E. The weight is calculated based on cardinality estimation, selectivity and operation complexity in the database statistics and is used to represent the amount of data transmission or the intensity of computational competition between the operations connected by the hyperedge. The task allocation unit is used to generate an initial task allocation anchor point based on the topology of the hypergraph H and the data fragmentation positions involved in each vertex. The anchor point assigns a recommended group of computing nodes to each vertex.
9. The distributed database query optimization system based on multi-agent cooperation according to claim 7, characterized in that, The local scheme module includes: The candidate path unit is used to read the subtasks assigned to this node in the task hypergraph H, combine the real-time state vector S of this node with the local data distribution, and search in the local execution plan space to generate at least one candidate execution path. The expected cost unit is used to calculate the expected cost Cn for each candidate execution path, calculate the lower bound of the cost LB based on the best case, and calculate the upper bound of the cost UB based on the worst case. The data stream signature unit is used to analyze the data characteristics of the intermediate results generated by the candidate execution path and form a data stream signature. The signature includes whether the data is ordered, the hash uniformity of the distribution key, and the data compression ratio. The signature will be passed to the global coordinating agent to calculate the cooperation loss cost.
10. The distributed database query optimization system based on multi-agent cooperation according to claim 7, characterized in that, The total cost module includes: The information extraction unit is used to collect all candidate local solutions and extract the expected cost Cn, lower cost bound LB, upper cost bound UB, and data stream signature for each solution. The game matrix unit is used to identify the cooperation relationship and conflict between different local solutions. The game matrix M is constructed. The rows of the matrix correspond to different query sub-tasks, the columns correspond to the local solutions that can be selected for the sub-task, and the matrix elements are the cooperation loss cost Sy generated after combining the local solutions. The cooperation loss cost is calculated based on whether the data stream signatures of two adjacent sub-tasks match. The total cost calculation unit is used to calculate the total cost Ct for each global candidate plan. The historical penalty term is obtained by accumulating the ratio of the actual time taken by the nodes involved in this plan to the expected time taken when performing similar operations in the past.