Distributed database query processing method and device, storage medium and program product

Abstract

The embodiment of the invention provides a distributed database query processing method and device, a storage medium and a program product, and relates to the field of distribution. The method comprises the following steps: receiving a query request containing a row number limiting condition and a sorting condition; splitting the line number limiting condition into a plurality of sub-limiting conditions, and generating a plurality of sub-query requests for instructing each storage node to perform data scanning by using the local index based on the sorting condition; distributing the plurality of sub-query requests to corresponding storage nodes for execution; and receiving the intermediate result from each storage node, and processing the intermediate result in a streaming merging mode to obtain a final result. According to the method, the optimization logic of the dynamic splitting row number limiting condition is added, and the response speed of mass data sorting query is increased.

Description

Technical Field

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

[0002] In distributed database systems, a collaborative architecture is typically adopted, where compute nodes and storage nodes work together. Compute nodes are responsible for receiving and distributing query requests, while storage nodes process their respective data shards. When handling paginated queries on massive amounts of data, this architecture can easily lead to significant consumption of disk I / O resources, thus impacting the overall system throughput and response time.

[0003] In the current implementation, the compute nodes directly distribute the complete query conditions to all storage nodes. Each storage node independently scans its local data according to the query conditions, sorts the data, and returns the results to the compute nodes. Finally, the compute nodes merge the returned results.

[0004] However, this implementation method is difficult to reduce the consumption of disk I / O resources. Summary of the Invention

[0005] This application provides a distributed database query processing method, device, storage medium, and program product to solve the technical problem of high disk I / O resource consumption.

[0006] In a first aspect, this application provides a distributed database query processing method, applied to computing nodes in a distributed database system, the distributed database system also including storage nodes, the method comprising:

[0007] Receive a query request that includes row count limits and sorting conditions; wherein, the row count limits are used to restrict the number of data rows returned from the query result set; and the sorting conditions are used to indicate the global sorting rules for the query result set.

[0008] The row count limit is broken down into multiple sub-limits, and based on the sorting condition, multiple sub-query requests are generated to instruct each storage node to perform data scanning using the local index.

[0009] Distribute multiple subquery requests to the corresponding storage nodes for execution;

[0010] It receives intermediate results from each storage node and processes them in a streaming merge mode to obtain the final result. The streaming merge mode sorts the continuously received intermediate results according to the global sorting rules and outputs ordered data rows in real time to form the final result set. It ends when the number of data rows in the final result set reaches the row limit condition.

[0011] In this embodiment, by dynamically splitting the query load and instructing storage nodes to perform targeted scans using the index, the full-shard data scan performed by each node to meet the LIMIT condition is fundamentally avoided. This method combines the global coordination capability of computing nodes with the local index efficiency of storage nodes, reducing the overall disk I / O consumption of the system and effectively distributing the computation and I / O load among the nodes, thereby leveraging the parallel processing advantages of the distributed architecture. Simultaneously, through a streaming merging mechanism, the system can receive data and sort and output it simultaneously, ending the query after meeting the row limit, achieving low-latency and efficient resource utilization.

[0012] In one possible implementation, the row count constraint is broken down into multiple sub-constraints, including:

[0013] Obtain the data volume and index density of each storage node;

[0014] Based on the ratio of data volume to index density of each storage node, the row count limit is proportionally allocated into multiple sub-limits corresponding to each storage node.

[0015] In this implementation, the global query load is distributed according to a proportion that more closely reflects the actual data distribution by acquiring and utilizing the data volume and index density of each storage node. This mechanism enables nodes with large data volumes and high index efficiency to handle a larger workload commensurate with their processing capabilities. Because task scheduling is optimized in the initial stage of query execution, it effectively prevents the allocation of excessive tasks to nodes with low index density, thereby avoiding large-scale, inefficient scans and table lookup I / O operations. This not only reduces the system's invalid workload but also provides a more balanced resource base for subsequent parallel execution.

[0016] In one possible implementation, when the index on which the sorting condition depends is a non-unique index, multiple subquery requests are generated to instruct each storage node to perform a data scan using the local index, including:

[0017] Select at least one other unique index field;

[0018] Based on the sorting criteria and at least one other unique index field selected, corresponding filter conditions are generated to locate the data range in the subquery request.

[0019] This implementation effectively solves the problem of ambiguous range positioning when sorting conditions rely on non-unique indexes by introducing additional unique index fields to construct composite filtering conditions. This approach improves the accuracy of data scanning on storage nodes and reduces invalid or duplicate scans caused by duplicate key values. While ensuring result integrity, it reduces the amount of data scanned for supplementary queries, thus improving query efficiency.

[0020] In one possible implementation, the method further includes:

[0021] When the index on which the sorting condition depends is a non-unique index, after merging the intermediate results of each storage node, record the last value of the sorting field in the merged result and the number of times the last value is repeated.

[0022] When generating supplementary query tasks, filter conditions are constructed based on the last value, and the offset of the supplementary query task is set based on the number of repetitions.

[0023] This implementation ensures that, in cases where duplicate values ​​exist in the sorting field, subsequent supplementary queries can accurately skip all merged duplicate rows and begin scanning directly from the data yet to be retrieved. This effectively avoids data omissions or duplications that may result from duplicate values ​​being scattered across different nodes or batches, thus guaranteeing the overall accuracy and completeness of the final results in dynamic pagination or deep query scenarios.

[0024] In one possible implementation, intermediate results are processed using a streaming merge method, including:

[0025] During the merging process, the remaining demand is determined based on the difference between the amount of merged data and the row count limit.

[0026] When it is determined that the intermediate results of the current batch from any storage node have been fully merged, sub-constraints and filtering conditions for subsequent queries against the storage node are determined based on the remaining demand, and supplementary query requests are distributed to the storage node based on the determined filtering conditions.

[0027] In this implementation, the system controls the data acquisition behavior of storage nodes, initiating requests only when a node runs out of data, and ensuring that the amount of data requested each time matches the current actual demand. This effectively controls the total amount of data scanned by each node throughout the query cycle to the minimum range necessary to meet the global row count limit, thereby reducing unnecessary disk scans and network data transfers and improving overall resource utilization efficiency.

[0028] In one possible implementation, the method further includes:

[0029] Cache intermediate results from each storage node and record the cache invalidation conditions of the cached intermediate results;

[0030] Upon receiving a new query request, the system utilizes the valid cached intermediate results based on the cache expiration condition to process the new query request.

[0031] In this implementation, for repetitive or similar sorting and pagination queries that are common in business (such as when a user flips through multiple pages), the system can achieve performance improvement and resource saving. Especially in scenarios with massive amounts of data, it can effectively reduce the response latency of subsequent queries and the overall load.

[0032] Secondly, this application provides a distributed database system, comprising:

[0033] The compute node is used to receive query requests containing row count limits and sorting conditions; break down the row count limits into multiple sub-limits; and based on the sorting conditions, generate multiple sub-query requests to guide each storage node to perform data scanning using local indexes; and process intermediate results from each storage node in a streaming merge manner.

[0034] Multiple storage nodes, each used to execute subquery requests from compute nodes and return intermediate results.

[0035] Thirdly, this application provides an electronic device, including: a processor and a memory communicatively connected to the processor;

[0036] The memory stores the instructions that the computer executes;

[0037] The processor executes computer-executable instructions stored in memory to implement any of the methods of the first aspect.

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

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

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

[0041] Figure 1 This is a schematic diagram illustrating a distributed database workflow provided in an embodiment of this application.

[0042] Figure 2 A flowchart illustrating a distributed database query processing method provided in an embodiment of this application;

[0043] Figure 3 A schematic diagram of a table representing a state recording operation provided in an embodiment of this application;

[0044] Figure 4 A schematic diagram of a table representing disk I / O consumption provided for an embodiment of this application;

[0045] Figure 5 This is a schematic diagram illustrating the process of database query optimization provided in an embodiment of this application.

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

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

[0048] It should be noted that the distributed database query processing method, device, storage medium and program product provided in this application can be used in the distributed field, or in any field other than distributed. This application does not limit the application field of the distributed database query processing method, device, storage medium and program product.

[0049] Specific application scenarios for this application include queries in distributed database systems that contain LIMIT and ORDER BY clauses. Specifically, in paginated query scenarios, such as in e-commerce, content management systems, or social platforms, the backend service needs to respond to frontend requests for sorting and paginating large datasets; these queries typically include an ORDER BY clause with a large LIMIT...OFFSET... value.

[0050] In large-scale data analysis and processing scenarios, such as business intelligence analysis, log analysis, or time-series data query, users or applications often need to quickly retrieve header or tail data records sorted by specific fields (such as timestamps or transaction amounts) from massive amounts of data. The corresponding queries include ORDER BY and large LIMIT values.

[0051] Furthermore, this application is also applicable to distributed database systems that employ sharding or shared storage architectures. In such systems, business data is horizontally split and stored across multiple physical nodes based on sharding keys (e.g., user ID, geographical region). When a query request requires global sorting and result set truncation based on a non-sharding key, this corresponds to the typical application scenario to which this application applies.

[0052] For the above application scenarios, in possible implementations, distributed databases typically employ a global merge-based processing flow for join queries containing LIMIT and ORDER BY. This flow mainly includes the following steps:

[0053] Query distribution: After receiving an SQL query request from a client, the compute node directly distributes the complete query statement (including all conditions such as LIMIT and ORDER BY) to all relevant storage nodes without modification.

[0054] Local execution: Each storage node independently executes the query on its own assigned data shard. Specifically, the storage node needs to perform a full local scan of the dataset that matches the WHERE condition in the query (if it exists, then execute), then sort the scan results locally according to the column specified by ORDER BY, and finally select the first LIMIT records from the sorted results.

[0055] Result merging: The compute nodes collect local result sets (each containing a maximum of LIMIT records) returned by all storage nodes. Subsequently, the compute nodes need to globally merge and reorder these results from different nodes to determine the correct global order, and finally extract the exact LIMIT records that meet the query requirements, returning them to the client.

[0056] To facilitate understanding of the collaboration process between compute nodes and storage nodes described above, the following section will combine... Figure 1 This collaboration process will be further explained. Figure 1 This is a schematic diagram illustrating a distributed database workflow provided in an embodiment of this application.

[0057] like Figure 1 As shown in the diagram, this illustrates the basic workflow of a distributed database, with the core being the division of labor between compute nodes and storage nodes.

[0058] first, Figure 1 The diagram presents the core components of a distributed database: a compute node acts as a coordination center, paired with multiple storage nodes (storage nodes 1 to 3 shown in the diagram) responsible for the actual data storage. This separation is the foundation of a distributed architecture, separating data storage and query computation to avoid performance bottlenecks on a single node.

[0059] Secondly Figure 1 The basic query process is explained. Specifically, the client does not directly access the storage nodes, but first sends the SQL request to the compute nodes; the compute nodes then distribute the request to the various storage nodes, allowing each storage node to process the data within its own shard; after all the storage nodes return the results, the compute nodes merge these scattered results into a final complete result, which is then returned to the client.

[0060] In general, Figure 1 This approach embodies the following idea: compute nodes are responsible for scheduling and merging, storage nodes are responsible for local data processing, and large-scale data storage and retrieval are supported through multi-node collaboration.

[0061] However, the solution described above has the following technical problems:

[0062] Because each storage node needs to independently execute a query containing the complete LIMIT value, each node must perform large-scale local data scanning and sorting. This not only puts a heavy burden on individual nodes, but also causes the overall system disk I / O consumption to increase linearly with the number of nodes, failing to effectively distribute the query burden.

[0063] Furthermore, each storage node still needs to perform operations on a scale comparable to that of a centralized database. The overall system response time does not improve with the increase of nodes and may even deteriorate due to coordination overhead, failing to reflect the design goal of distributed systems to share the load through parallelization.

[0064] Furthermore, the existing solution adopts a uniform query distribution strategy without making differentiated adjustments based on the data distribution or query semantics of each storage node. This makes it impossible to accurately locate the target data range, resulting in a large amount of invalid data scanning and transmission, which affects query efficiency.

[0065] The distributed database query processing method provided in this application aims to solve the aforementioned technical problems of existing technologies. By dynamically decomposing the global LIMIT and OFFSET conditions in the query into a series of subtasks with differentiated LIMIT values ​​and filtering conditions by the compute nodes, these subtasks are distributed to each storage node for independent execution. Each storage node utilizes its local index to locate the data range related to the sort key, thereby avoiding a full scan of its local data. The compute nodes dynamically adjust subsequent subtasks based on the execution progress of each node to coordinate the aggregation of intermediate results, thus minimizing disk I / O.

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

[0067] First, combine Figure 2 This application introduces a distributed database query processing method provided in its embodiments. Figure 2 This is a flowchart illustrating a distributed database query processing method provided in an embodiment of this application. The execution entity of this method is a computing node (also called a coordinating node) in a distributed database system. This distributed database system may, for example, adopt a computing and storage separation architecture. In addition to the computing nodes, it may also include multiple storage nodes (also called data nodes). Each storage node is responsible for storing data shards and maintaining local indexes. The computing nodes are responsible for receiving queries, coordinating tasks, and merging results. Figure 2 As shown, the method includes:

[0068] S201. Receive a query request that includes row count limits and sorting conditions.

[0069] Specifically, the compute node receives a Structured Query Language (SQL) request initiated by a client or application, characterized by containing specific clauses for controlling the result set. For example, these might include row count limits and sorting criteria.

[0070] For row count limits, the SQL statement typically corresponds to the LIMIT N clause. Its function is to limit the number of rows returned to the client from the complete result set that meets the query criteria. For example, LIMIT 100 means returning a maximum of 100 rows.

[0071] For sorting conditions, this typically corresponds to the ORDER BY column [ASC|DESC] clause in SQL. Its function is to specify the global sorting rules that the result set should follow, that is, the columns and sorting direction (e.g., ascending or descending) used to uniformly sort the data on all storage nodes.

[0072] In an optional embodiment, the query request may also include an offset condition, typically corresponding to the OFFSETM clause in SQL, which limits the number of initial data rows to be skipped before returning the final result. Combined with the LIMIT clause, it can implement pagination query functionality. For example, LIMIT 20 OFFSET 40 means skipping the first 40 rows and returning the next 20 rows.

[0073] After receiving a query request, the compute node parses the query request, identifies the key conditions mentioned above, and prepares to execute the subsequent processing flow.

[0074] S202. The row count limit is split into multiple sub-limits, and based on the sorting condition, multiple sub-query requests are generated to instruct each storage node to perform data scanning using the local index.

[0075] After parsing the query request, the compute node executes query rewriting and task distribution optimization logic, which includes the following two collaborative operations:

[0076] Operation 1: Dynamic row number limitation conditions:

[0077] The compute nodes break down the global row count limit into multiple sub-limits based on the current cluster topology and the data distribution characteristics of each storage node. Specifically, the limit can be distributed evenly based on the number of storage nodes, or proportionally based on information such as the data volume, index density, and data distribution histogram of each storage node obtained from system metadata or through lightweight pre-queries.

[0078] For example, for a query with a total limit of LIMIT 1,000,000, if the system has 4 storage nodes with a corresponding data volume ratio of 3:2:2:1, it may be split into sub-limits of LIMIT 375,000, LIMIT 250,000, LIMIT 250,000, and LIMIT 125,000, so that the expected workload of each node matches the corresponding data size.

[0079] Step 2: Generate a subquery request indicating an index scan.

[0080] While splitting the constraints, the compute nodes construct a customized subquery request for each storage node based on the sorting conditions. This process is crucial for achieving targeted data scanning and avoiding full table scans. In other words, each subquery request can be designed to instruct the storage node to use its corresponding local index to locate and scan the data, rather than performing a full table scan.

[0081] For example, for the sorting condition ORDER BY order_col ASC, the compute node generates a subquery that includes a range filter condition. On the first query, a request such as SELECT...FROM...WHERE [other conditions] ORDER BY order_col ASC LIMIT {sublimit value} might be generated, relying on the storage node's query optimizer to select an index on order_col.

[0082] In subsequent supplementary queries (e.g., when a node runs out of data and needs to retrieve more data), the compute node will generate a request such as `SELECT...FROM...WHERE order_col>{last_value} [AND other conditions] ORDER BY order_col ASC LIMIT {new sub-limit value}` based on the last `order_col` value (denoted as `last_value`) in the merged results. This `WHERE` condition effectively utilizes the index on `order_col` to directly locate the data after that value, skipping the already scanned data range.

[0083] Through the above steps, the computing nodes transform the original global query into a series of parallel subtasks with precise data volume limits and efficient data location capabilities, providing an optimized foundation for subsequent distributed execution.

[0084] S203. Distribute multiple subquery requests to the corresponding storage nodes for execution.

[0085] After the query task is decomposed and optimized, the compute nodes enter the task distribution and parallel execution phase. The core of this process is to efficiently and accurately dispatch the constructed sub-query tasks to each data storage node and trigger the local execution engine of each storage node to optimize the query.

[0086] Specifically, the compute nodes distribute the multiple subquery requests generated in step S202 to their corresponding target storage nodes according to the pre-maintained data sharding routing table or sharding rules. Each subquery request already includes sub-constraints customized for that node and filtering conditions indicating local index scans.

[0087] After receiving a subquery request from the compute node, each storage node independently executes the query in its local database engine.

[0088] Through the above steps, the original heavy-load global query is transformed into multiple lightweight, parallel-executable local index scan tasks. Each storage node fully utilizes its local computing and I / O resources to complete data retrieval and preliminary processing within its respective data shard in parallel, providing a foundation for subsequent result merging by the computing nodes.

[0089] S204. Receive intermediate results from each storage node and process the intermediate results in a streaming merge manner to obtain the final result.

[0090] After issuing a subquery request, the compute node enters the result collection and merging phase. This phase can, for example, employ a streaming merging strategy, the core of which is "receiving, comparing, and outputting simultaneously" to achieve low-latency, low-memory-consumption global sorting and result extraction.

[0091] Specifically, during the intermediate result receiving phase, after executing their local subqueries, each storage node continuously returns the generated intermediate result set (i.e., the data rows already sorted according to local sorting rules) to the compute nodes via data streams. The compute nodes simultaneously receive these data streams from multiple storage nodes.

[0092] During the streaming merge processing phase, the compute nodes do not wait for all intermediate results to arrive before starting processing. Instead, they sort and output the continuously received intermediate results in real time according to the global sorting rules (i.e., the ORDER BY clause) in the query request. The specific process is as follows:

[0093] Establishing and maintaining priority queues: Each compute node maintains a data buffer for each storage node it connects to, used to temporarily store the latest batch of ordered data returned by that node. Simultaneously, the compute node uses a min-heap or max-heap as a global priority queue. Each element in the heap represents the first row of data currently awaiting processing by a storage node and its source node identifier.

[0094] Instantly output ordered rows of data:

[0095] (1) Take the top element of the heap: Take the next row of data that is currently globally ordered from the top of the priority queue, that is, the row with the smallest or largest size according to the sorting rules in the first row of all node buffers.

[0096] (2) Output and counting: Output the data of the row immediately, add it to the final result set, and accumulate the number of rows that have been output.

[0097] (3) Supplement and Adjustment: Remove the row from the buffer of the storage node from which the data was taken. If the buffer of that node becomes empty as a result, request the next batch of data from that node, i.e., trigger a supplement query, and add the first row of the newly returned data back to the priority queue. If there is still data in the buffer, add the new first row to the priority queue.

[0098] (4) Maintain heap property: After each addition or deletion of an element, readjust the priority queue to ensure that the top element of the heap always represents the next row of the current globally ordered sequence.

[0099] Dynamic termination conditions:

[0100] The entire streaming merge process continues until the number of rows in the final result set reaches the row limit specified in the query request (e.g., LIMIT N). At this point, even if there is still unprocessed data in the buffers of some storage nodes or data still being transmitted over the network, the compute nodes will stop merging and return the generated final result set to the client. If the query contains OFFSET M, the first M rows are skipped only for internal counting and are not added to the final result set.

[0101] In particular, when the index on which the sorting condition depends is a non-unique index, the streaming merge process also includes a critical status recording operation to ensure that no data is missed during deep pagination or data supplementation queries.

[0102] The following is combined with Figure 3 Let's take the query SELECT...FROM...ORDER BY order_col LIMIT 6 as an example for explanation. Figure 3 This is a table diagram illustrating the status recording operation provided in an embodiment of this application. Assume the `order_col` column has a non-unique index, and the first 6 rows of data in the final result set obtained after streaming merging are as follows: Figure 3 As shown in the table, in this scenario, after outputting the last row of data, the compute node will detect the last group of consecutive identical values ​​in the sorting field `order_col`. Specifically, the last two rows of the result set both have the value 5 for `order_col`. Therefore, the compute node will record this information, namely the number of times the last value of the sorting key (`last_value=5`) appears consecutively in the merged results, denoted as `last_orderby_value_repeat_count=2`.

[0103] The purpose of recording this `last_orderby_value_repeat_count` is to allow compute nodes to use this count as a correction parameter when subsequent queries are needed to retrieve more data from data-depleted storage nodes (e.g., to satisfy a larger LIMIT value or process an OFFSET). When generating new filter conditions, this repeat count is used in addition to the `last_value` to ensure that all merged rows with the same sort key value are accurately skipped. This avoids data omissions caused by identical values ​​being scattered across different nodes, achieving accurate global pagination.

[0104] The distributed database query processing method provided in this embodiment fundamentally avoids the full-shard data scan performed by each node to meet the LIMIT condition by dynamically splitting the query load and instructing storage nodes to perform targeted scans using indexes. This method combines the global coordination capabilities of computing nodes with the local index efficiency of storage nodes, reducing overall system disk I / O consumption and effectively distributing the computational and I / O load across nodes, thereby leveraging the parallel processing advantages of a distributed architecture. Simultaneously, through a streaming merging mechanism, the system can receive data, sort it, and output it simultaneously, ending the query after meeting the row count limit, achieving low-latency and efficient resource utilization.

[0105] To visually demonstrate the performance improvement brought about by the methods in the above embodiments, the following is combined with... Figure 4 This will be illustrated with a quantitative comparison example. Figure 4 This is a table illustrating disk I / O consumption provided in an embodiment of this application. Consider a typical scenario: executing a query on a distributed table: SELECT...FROM...ORDER BY order_col LIMIT 100000000. Assume the data is evenly distributed across 4 storage nodes (A, B, C, D), and each disk I / O operation can read 100,000 records.

[0106] Before optimization: The compute nodes distributed queries containing the complete LIMIT 100,000,000 to all nodes as is. Each storage node had to independently scan and sort all its local data to meet this constraint, meaning each node needed to scan 100,000,000 data entries, corresponding to 100 I / O operations. The total I / O consumption of the system was 100 I / O operations per node × 4 nodes = 400 I / O operations.

[0107] After optimization using the methods described in the above embodiments:

[0108] Initial query (corresponding to steps S202-S203 above): The compute node splits LIMIT 100000000 into four LIMIT 25000000 subqueries and distributes them. Each node scans 25000000 data entries, corresponding to 25 I / O operations. The total I / O in this stage is 25 × 4 = 100 I / O operations.

[0109] Supplementary Query (Dynamically triggered during streaming merge, corresponding to step S204 above): Assume that during the streaming merge process, nodes A and B run out of data first, requiring supplementary queries. For node A, 25,000,000 data entries need to be supplemented (25 I / Os), and the starting position is located using the index (consuming 4 I / Os). For node B, since it is close to the total requirement, only 10,000,000 data entries need to be supplemented (10 I / Os), and the starting position is located using the index (4 I / Os). For nodes C and D, since the data is not exhausted, no supplementary query is needed. The total I / O in this stage is: 25 + 4 + 10 + 4 = 43 I / Os.

[0110] Therefore, the total I / O after optimization is: 100 times (initial) + 43 times (supplementary) = 143 I / O operations.

[0111] The comparison shows that, through the dynamic splitting, index positioning indication, and streaming merging mechanism of the above embodiments, the total number of disk I / O operations was reduced from 400 to 143. These data demonstrate the reduction in disk I / O resource consumption, the increase in system throughput, and the shortening of query response time, reflecting the effectiveness of the method in solving the performance bottleneck of massive data sorting and pagination queries.

[0112] To facilitate understanding of the execution flow of the distributed database query processing method provided in the above embodiments, the following is a detailed explanation. Figure 5 This process will be introduced. Figure 5 This is a schematic diagram illustrating the process of database query optimization provided in an embodiment of this application.

[0113] like Figure 5 As shown in the flowchart, this process diagram illustrates the optimized execution flow of a SELECT...LIMIT...ORDER BY query in a distributed database. The core principle is to reduce resource consumption by dynamically adjusting the LIMIT value of nodes. Specifically, it can be broken down into the following stages:

[0114] The process begins with pre-processing and initialization. It starts by checking if the optimization conditions are met, such as the values ​​of LIMIT / OFFSET and whether the sorting fields are suitable for sharding. If not, the normal process continues; otherwise, the optimization logic is activated: first, an initial LIMIT value is set based on the number of nodes; then, the split query is sent to each storage node to obtain the local result set from each node.

[0115] Next comes the result merging and dynamic adjustment. The computing nodes first merge the results returned by each node, and then determine whether any node's results have been used up or have remaining results. If a node's results have been used up, supplementary data for that node is retrieved again; if a node has remaining results, a new LIMIT value is calculated, for example, by gradually adjusting it to a larger value, and this retrieval and merging process is repeated until the merged result set meets the final LIMIT + OFFSET requirement.

[0116] Finally, in the final processing, once the result set meets the requirements, an ORDER BY operation is performed on the global results, and then the final results are output according to the number of LIMITs. The core of the entire process is to avoid having the storage nodes return the full amount of data at once. Instead, it reduces the IO overhead of each node by dynamically acquiring and merging data in batches, while ensuring the correctness of the global sorting and LIMITs.

[0117] Optionally, in order to achieve a more balanced load distribution, this embodiment may adopt a data distribution-based splitting strategy when performing the query splitting in step S202 above.

[0118] Specifically, before executing the split, the compute node first obtains the data volume (i.e., the total number of data rows stored) and index density (i.e., the data coverage of the index on the ORDER BY sorting field) of each storage node by querying the system metadata table or sending a lightweight statistical command to each storage node.

[0119] Next, the compute nodes perform calculations based on the acquired information. For each storage node, the ratio of data volume to index density is calculated. This ratio comprehensively reflects the size of the node's data volume and the efficiency of quickly locating data through the index. The core logic is: nodes with large data volumes and efficient indexes should undertake more query tasks; conversely, they should be assigned fewer tasks.

[0120] Finally, the compute nodes allocate the global row count limit (e.g., LIMIT N) proportionally according to this ratio, generating sub-limits that correspond one-to-one with each storage node. For example, if the system has nodes A and B with ratios of 3 and 1 respectively, and the global limit is LIMIT 100, then node A might be allocated LIMIT 75, and node B might be allocated LIMIT 25.

[0121] By acquiring and utilizing the data volume and index density of each storage node, the global query load is distributed in a proportion more closely aligned with the actual data distribution. This mechanism enables nodes with large data volumes and high index efficiency to handle a larger workload commensurate with their processing capabilities. Because task scheduling optimization is implemented in the initial stage of query execution, it effectively prevents the allocation of excessive tasks to nodes with low index density, thereby avoiding large-scale, inefficient scans and table-return I / O operations. This not only reduces the system's invalid workload but also provides a more balanced resource base for subsequent parallel execution.

[0122] Optionally, when the sorting condition of the query (i.e., the ORDER BY column) depends on a non-unique index, a strategy of using multiple indexes to locate the range can be adopted to compensate for the ambiguity in range positioning that may be caused by non-unique key values.

[0123] Specifically, when generating a subquery request to be sent to the storage node, the compute node selects at least one other unique index field from the table structure (e.g., a primary key id field or a column with a UNIQUE constraint). The compute node then constructs a composite filter based on the original sorting criteria and the selected unique index field.

[0124] For example, in the query `SELECT * FROM orders ORDER BY customer_id (non-unique index) LIMIT N`, the compute node might select the unique index field `order_id`. When generating the subquery, in addition to using a condition based on the last returned value of `customer_id` (e.g., `WHERE customer_id>=last_customer_id`), a condition based on `order_id` (e.g., `AND order_id>last_order_id`) would also be appended.

[0125] This composite filtering condition is added to the WHERE clause of the subquery and sent to the storage node for execution. Due to the introduction of the unique index, when performing a range scan using the non-unique index `customer_id`, the storage node can further refine the filtering and location of data rows using the unique index `order_id`. Even with a large number of duplicate `customer_id` values, it can effectively narrow down the range of data to be scanned in each batch, thus achieving more precise targeted data scanning.

[0126] By introducing additional unique index fields to construct composite filtering conditions, the problem of ambiguous range positioning when sorting conditions rely on non-unique indexes is effectively solved. This approach can improve the accuracy of data scanning on storage nodes and reduce invalid or duplicate scans caused by duplicate key values. While ensuring the integrity of the results, it reduces the amount of data scanned for supplementary queries, thereby improving query efficiency.

[0127] Optionally, for scenarios where the sorting field is a non-unique index, a control mechanism is also provided to record key states during the streaming merge process and correct them in subsequent supplementary queries.

[0128] When compute nodes perform streaming merging of intermediate results returned by storage nodes, they will record any consecutive repetitions of the sorting field value. Specifically, after merging each batch of data, compute nodes will check and record the last value of the sorting field in the currently merged result (denoted as last_value) and the number of times that value appears consecutively in the merged result (denoted as repeat_count).

[0129] When a storage node runs out of data and a supplemental query is needed to obtain more data, the compute node will calibrate the supplemental query using previously recorded state information. The filtering conditions for the supplemental query are constructed based on the record's `last_value` (e.g., using `WHERE order_col>=last_value`). Simultaneously, the query offset is set based on the record's `repeat_count`. For example, if `last_value` is 5 and `repeat_count` is 2, the supplemental query might be constructed as `WHERE order_col>=5 OFFSET 2`.

[0130] This approach ensures that when duplicate values ​​exist in the sorting field, subsequent supplementary queries can accurately skip all merged duplicate rows and begin scanning directly from the data yet to be retrieved. This effectively avoids data omissions or duplications that may occur due to duplicate values ​​being scattered across different nodes or batches, thus guaranteeing the overall accuracy and completeness of the final results in dynamic pagination or deep query scenarios.

[0131] Optionally, while continuously merging the intermediate results returned by each node, the computing node monitors the global progress and node status in real time: by calculating the difference between the merged data volume and the total limit, the remaining demand is obtained, and it is simultaneously determined whether the current batch of data of each node has been merged.

[0132] When it is detected that the current batch of data for a certain node has been processed, the system initiates an adjustment process. First, based on the latest remaining demand, the upper limit of the amount of data that the node should acquire next (i.e., the new sub-constraint) is calculated. At the same time, filter conditions are generated or updated based on the latest sorting key value obtained in the streaming merge. Subsequently, the compute node distributes a supplementary query request carrying these updated conditions to the node.

[0133] By employing the above methods, the system controls the data acquisition behavior of storage nodes, initiating requests only when a node's data is exhausted, and ensuring that the amount of data requested in each request matches the current actual demand. This effectively controls the total amount of data scanned by each node throughout the entire query cycle to the minimum range necessary to meet the global row count limit, thereby reducing unnecessary disk scans and network data transfers and improving overall resource utilization efficiency.

[0134] Optionally, after completing a distributed query that includes sorting and constraints, the system will cache the intermediate results from each storage node (i.e., the sorted data blocks on each node's local storage) and record the expiration conditions associated with these cached data, such as the data version number, update time, or expiration date.

[0135] When the system receives a new query request, it first checks whether the request might contain existing cached data. This process might include parsing the new request's query conditions (such as WHERE filters, ORDER BY, and LIMIT / OFFSET values) and comparing them with cache expiration conditions to confirm whether the cached data is still valid and applicable to the current request.

[0136] If the cache is deemed valid, the system will directly use the intermediate results from the cache to process new query requests. Specifically, compute nodes do not need to reissue the complete subquery to all storage nodes. Instead, they can directly perform streaming merging and result truncation based on the sorted data blocks in the cache, or initiate small-scale supplementary queries only to nodes with insufficient data, thereby reducing duplicate scanning requests to storage nodes and network data transmission.

[0137] Through the above implementation, the system can improve performance and save resources for repetitive or similar sorting and pagination queries that are common in business (such as when users flip through multiple pages). Especially in the case of massive data, it can effectively reduce the response latency of subsequent queries and the overall load.

[0138] Furthermore, in complex query scenarios of distributed databases, data consistency is a crucial prerequisite for ensuring accurate and reliable query results. Therefore, in an optional implementation, this solution configures the transaction environment before query processing begins.

[0139] Specifically, when a compute node receives and begins processing a query request, it proactively sets the transaction isolation level to "Repeatable Read" for the database connection executing the request. This isolation level provides a stable and consistent view of the data during the query process. Its function is that once a transaction begins, all data read operations performed within the lifecycle of that transaction will see a consistent data snapshot, unaffected by new data committed by other concurrent transactions.

[0140] For example, connection A starts a transaction (isolation level is repeatable read) and executes the query for the first time to obtain result set R1.

[0141] After that, connection B inserts the new data and commits.

[0142] When connection A executes the same query again within the same transaction, the result set obtained will still be R1, and will not include the data newly inserted by connection B.

[0143] By setting the transaction isolation level to "repeatable read," this solution provides a stable data consistency foundation for subsequent core optimization steps such as query splitting, index location, and streaming merging (as described in the previous examples). This ensures that even in a highly concurrent data write environment, the intermediate data state processed throughout the distributed query process remains consistent, thereby avoiding problems such as result set corruption, duplication, or omissions that may occur due to changes in the data view, and guaranteeing the global correctness of the final query results.

[0144] This transaction setup works in conjunction with the query optimization techniques described above to achieve data consistency while pursuing high performance.

[0145] The electronic device provided in this application embodiment can execute the method provided in the above method embodiment. Its implementation principle and technical effect are similar, and will not be described in detail here.

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

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

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

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

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

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

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

[0153] In this application, the term "comprising" and its variations can refer to non-limiting inclusion; the term "or" and its variations can refer to "and / or". The terms "first", "second", etc., in this application are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. In this application, "multiple" refers to two or more. "And / or" describes the relationship between related objects, indicating that three relationships can exist. For example, A and / or B can represent: A existing alone, A and B existing simultaneously, and B existing alone. The character " / " generally indicates that the preceding and following related objects have an "or" relationship.

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

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

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

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

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

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

Claims

1. A distributed database query processing method, characterized in that, The method, applied to computing nodes in a distributed database system, which also includes storage nodes, comprises: Receive a query request that includes a row count limit and a sorting condition; wherein the row count limit is used to limit the number of data rows returned from the query result set; and the sorting condition is used to indicate the global sorting rule of the query result set. The row count limit is broken down into multiple sub-limits, and based on the sorting condition, multiple sub-query requests are generated to instruct each storage node to perform data scanning using the local index. Distribute the multiple sub-query requests to the corresponding storage nodes for execution; The system receives intermediate results from each storage node and processes them in a streaming merge manner to obtain the final result. The streaming merge method sorts the continuously received intermediate results according to the global sorting rules and outputs ordered data rows in real time to form the final result set. The process ends when the number of data rows in the final result set reaches the row count limit.

2. The method according to claim 1, characterized in that, The step of breaking down the row count constraint into multiple sub-constraints includes: Obtain the data volume and index density of each storage node; Based on the ratio of the data volume to the index density of each storage node, the row count constraint is proportionally allocated into multiple sub-constraints corresponding to each storage node.

3. The method according to claim 1, characterized in that, When the sorting condition depends on a non-unique index, generating multiple subquery requests to instruct each storage node to perform data scanning using local indexes includes: Select at least one other unique index field; Based on the sorting criteria and the selected at least one other unique index field, corresponding filtering criteria are generated for locating the data range in the subquery request.

4. The method according to claim 1 or 2, characterized in that, The method further includes: When the index on which the sorting condition depends is a non-unique index, after merging the intermediate results of each storage node, the last value of the sorting field in the merged result and the number of times the last value is repeated are recorded. When generating a supplementary query task, filter conditions are constructed based on the last value, and the offset of the supplementary query task is set based on the number of repetitions.

5. The method according to any one of claims 1-4, characterized in that, The process of processing the intermediate results using a streaming merge method includes: During the merging process, the remaining demand is determined based on the difference between the amount of merged data and the row count limit. When it is determined that the intermediate results of the current batch from any storage node have been fully merged, sub-constraints and filtering conditions for subsequent queries against the storage node are determined based on the remaining demand, and supplementary query requests are distributed to the storage node based on the determined filtering conditions.

6. The method according to any one of claims 1-4, characterized in that, The method further includes: The intermediate results from each storage node are cached, and the cache invalidation conditions of the cached intermediate results are recorded; Upon receiving a new query request, the system utilizes the valid intermediate results from the cache based on the cache invalidation condition to process the new query request.

7. A distributed database system, characterized in that, The distributed database system includes: The compute node is configured to receive query requests containing row count limits and sorting conditions; break down the row count limits into multiple sub-limits; and based on the sorting conditions, generate multiple sub-query requests to guide each storage node to perform data scanning using local indexes; and process intermediate results from each storage node in a streaming merge manner. Multiple storage nodes, each used to execute subquery requests from the compute nodes and return intermediate results.

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

9. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer-executable instructions, which, when executed by a processor, are used to implement the method as described in any one of claims 1 to 6.

10. A computer program product, characterized in that, Includes a computer program that, when executed by a processor, implements the method of any one of claims 1 to 6.

Images