A method and system for returning result sets by paging based on distributed database
By generating random factors for each table in a distributed database and calculating the traversal order, the problem of unfixed traversal order in distributed database pagination query is solved, hot nodes are avoided, query efficiency and consistency are improved, and user experience is improved and business accuracy.
Patent Information
- Application Number
- CN202410348467.9
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2024-03-26
- Publication Date
- 2024-11-08
- Estimated Expiration
- 2044-03-26
AI Technical Summary
In a distributed database system, when paginated query, the traversal order is not fixed due to the data being distributed in each node, which may lead to the formation of hot spot nodes, affecting performance and load, and inconsistent results, affecting user experience and business accuracy.
Create virtual attributes for each table by randomly generating random factors, calculate the traversal starting point and order, ensuring that the traversal order of each table is fixed, but the order between tables is random, avoiding hotspot nodes, and in the event of node failure or cluster changes, Maintain data consistency.
It effectively avoids the formation of hot spot nodes, improves the query efficiency and consistency of distributed databases, and improves the user experience and the accuracy of business systems.
Smart Images

Figure CN117951184B_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the technical field of data query, and in particular to a method and system for returning result sets by paging based on a distributed database. Background Art
[0002] In traditional centralized storage databases or stand-alone database systems, since data is stored in one place, when doing paging queries, it is sufficient to return the data after offsetting it in the storage order. The data returned is also returned in order according to the storage order. The results of each query are fixed and kept in order, and the results of multiple queries can ensure consistency.
[0003] However, in a distributed database system, the data in the table is distributed and stored in various nodes. The data returned by paging faces a traversal order problem. If each query is traversed in order, that is, starting from the first node, then when multiple tables are queried at the same time, for example, all traversals start from the first item, then the task pressure on the first node will be too large, causing a hot node, thereby affecting the performance and load of the entire cluster. Therefore, in a distributed database system, the traversal order returned by paging is randomly traversed from each node. In different queries, the returned results may also be different, which will lead to inefficiency and poor user experience, and may also affect the accuracy of the business system. Summary of the invention
[0004] The present invention aims to solve at least one of the technical problems existing in the related art. To this end, the present invention provides a method and system for returning result sets by paging based on a distributed database.
[0005] The present invention provides a result set paging return method based on a distributed database, comprising:
[0006] S1: Randomly generate a random factor and create a table based on the random factor;
[0007] S2: Preset the number of traversal shards, and calculate the traversal starting point for executing the traversal plan according to the random factor and the number of traversal shards;
[0008] S3: Starting from the traversal starting point, the number of traversed shards is increased in sequence, and after reaching the last shard, it is transferred to the first shard until the previous node of the traversal starting point is reached, and the execution order is obtained;
[0009] S4: Execute the paging query statement according to the execution order to traverse the table. When the current traversal shard result set is insufficient, the next traversal shard is used to supplement the current traversal shard result set to obtain the traversal result.
[0010] According to a distributed database-based result set paging return method provided by the present invention, the random factor in step S1 is A random value in the interval.
[0011] According to a distributed database-based result set paging return method provided by the present invention, in step S1, when a table is created based on a random factor, the random factor is a virtual attribute of a table creation statement.
[0012] According to a result set paging return method based on a distributed database provided by the present invention, in step S2, the traversal starting point is a table node corresponding to the upward rounded value of the product of the random factor and the traversal shard number.
[0013] According to a distributed database-based result set paging return method provided by the present invention, step S4 further includes:
[0014] When the cluster node where the currently traversed shard is located fails, the traversal process is executed by the cluster node where the copy of the currently traversed shard is located.
[0015] According to a distributed database-based result set paging return method provided by the present invention, when the traversal process is executed by the cluster node where the copy of the currently traversed shard is located, the execution order does not change.
[0016] According to a distributed database-based result set paging return method provided by the present invention, step S4 also includes:
[0017] When the paging query statement includes a where condition, the execution order does not change.
[0018] The present invention also provides a result set paging return system based on a distributed database, comprising:
[0019] Random factor generation module: used to randomly generate random factors and create a table based on the random factors;
[0020] Traversal starting point calculation module: used to calculate the traversal starting point for executing the traversal plan according to the random factor generated by the random factor generation module and the preset traversal fragment number;
[0021] Execution order acquisition module: used to obtain the traversal starting point obtained by the traversal starting point calculation module, and sequentially increase it according to the number of traversal shards, and then turn to the first shard after reaching the last shard, until the previous node of the traversal starting point, to obtain the execution order;
[0022] Traversal execution module: used to execute paging query statements according to the execution order generated by the execution order acquisition module, traverse the table, and when the current traversal shard result set is insufficient, the next traversal shard will supplement the current traversal shard result set and output the traversal result.
[0023] The present invention provides a result set paging return method and system based on a distributed database. Each table has its own random factor. When generating an execution plan, the system generates a traversal order for each table according to the random factor. Therefore, the traversal order for a single table is fixed according to the shards. However, in the entire system, the traversal order between tables is random, so no hotspot nodes are generated. In addition, the present invention also satisfies the consistency and correctness of returned data when changes such as node failure, cluster expansion and contraction, and query conditions in SQL occur.
[0024] Additional aspects and advantages of the present invention will be given in part in the following description and in part will be obvious from the following description, or will be learned through practice of the present invention. BRIEF DESCRIPTION OF THE DRAWINGS
[0025] In order to more clearly illustrate the technical solutions in the present invention or the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.
[0026] Figure 1 The present invention provides a flow chart of a method for returning result sets in paging based on a distributed database.
[0027] Figure 2 It is a schematic diagram of computing cluster sharding in a result set paging return method based on a distributed database provided by an embodiment of the present invention.
[0028] Figure 3 The present invention is a schematic diagram of a distributed database-based result set paging return system structure.
[0029] Reference numerals:
[0030] 100, random factor generation module; 200, traversal starting point calculation module; 300, execution order acquisition module; 400, traversal execution module. DETAILED DESCRIPTION
[0031] In order to make the purpose, technical scheme and advantages of the present invention clearer, the technical scheme of the present invention will be clearly and completely described below in conjunction with the drawings in the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, rather than all the embodiments. Based on the embodiments in the present invention, all other embodiments obtained by ordinary technicians in the field without creative work are within the scope of protection of the present invention. The following embodiments are used to illustrate the present invention, but cannot be used to limit the scope of the present invention.
[0032] In the description of this specification, the description with reference to the terms "one embodiment", "some embodiments", "example", "specific example", or "some examples" etc. means that the specific features, structures, materials or characteristics described in conjunction with the embodiment or example are included in at least one embodiment or example of the embodiment of the present invention. In this specification, the schematic representations of the above terms do not necessarily refer to the same embodiment or example. Moreover, the specific features, structures, materials or characteristics described may be combined in any one or more embodiments or examples in a suitable manner. In addition, those skilled in the art may combine and combine the different embodiments or examples described in this specification and the features of the different embodiments or examples, without contradiction.
[0033] Combine the following Figure 1 Embodiments of the present invention are described.
[0034] The present invention provides a result set paging return method based on a distributed database, comprising:
[0035] S1: Randomly generate a random factor and create a table based on the random factor;
[0036] Among them, the random factor in step S1 is A random value in the interval.
[0037] Wherein, in step S1, when a table is created based on a random factor, the random factor is a virtual attribute of a table creation statement.
[0038] The specific process of step S1 is to add a virtual attribute - random factor, i.e., random_factor, when creating a table statement in the database. The random factor is a random value between (0, 1). When creating a table, the random factor is automatically generated and saved by the system without manual specification. The random factors between tables are independent.
[0039] S2: Preset the number of traversal shards, and calculate the traversal starting point for executing the traversal plan according to the random factor and the number of traversal shards;
[0040] Among them, in step S2, the traversal starting point is the table node corresponding to the rounded-up value of the product of the random factor and the traversal shard number.
[0041] Furthermore, the expression of the above traversal starting point is:
[0042]
[0043] in, The traversal starting point is calculated. is the ceiling function, is the selected random factor, is the total number of slices.
[0044] S3: Starting from the traversal starting point, the number of traversed shards is increased in sequence, and after reaching the last shard, it is transferred to the first shard until the previous node of the traversal starting point is reached, and the execution order is obtained;
[0045] The purpose of this stage is to calculate the execution order after traversing the starting shard. Specifically, the execution order starts from the starting shard and increases in order of the shard numbers. After reaching the last shard, it returns to the first shard, with the starting shard number - 1 as the last node in the execution order.
[0046] S4: Execute the paging query statement according to the execution order to traverse the table. When the current traversal shard result set is insufficient, the next traversal shard is used to supplement the current traversal shard result set to obtain the traversal result.
[0047] After the preceding steps S1 to S3, that is, after the table traversal order is determined, the traversal starts from the first shard, and the required results are returned after the offset. If the result set of the starting point shard is insufficient, it is supplemented from the next shard, and so on.
[0048] Wherein, step S4 also includes:
[0049] When the cluster node where the currently traversed shard is located fails, the traversal process is executed by the cluster node where the copy of the currently traversed shard is located.
[0050] When the traversal process is executed by the cluster node where the copy of the currently traversed shard is located, the execution order does not change.
[0051] Wherein, step S4 also includes:
[0052] When the paging query statement includes a where condition, the execution order does not change.
[0053] Furthermore, the present invention can also adapt to the following situations in the cluster, specifically:
[0054] When the cluster size changes, the total number of shards in the cluster changes, and the calculated traversal starting point is calculated according to the new number of shards;
[0055] When a node fails in the cluster, if the node where the shard is located fails, the node where the shard copy is located will continue to execute, and the shard number will not change, which will not affect the overall execution process;
[0056] When there is a where condition in SQL, the result set filtered by each shard changes, and the required results are returned in the storage order after deviating from the starting node, which does not affect the overall execution process.
[0057] When actually creating a table, the specific execution process is as follows: execute the creation statement DDL, add the attribute random_factor by default, and then complete the table creation;
[0058] When actually executing a query, the specific execution process is: accept the query statement, determine whether it is a single table and has a limit condition, if not, execute the normal query logic and return the query result, if so, calculate the traversal starting point based on the random factor of the queried table, then execute the query logic, and return the query result in the traversal order of the starting point.
[0059] Next, combine Figure 2 Some specific implementations of the present invention are described in detail.
[0060] like Figure 2 As shown, in this distributed database, the computing cluster includes 5 nodes, each node is divided into 2 instances, each node has 10 shards, and the total number of shards is 50, marked as n1 to n50, and the computing cluster is controlled by 3 devices gc1 to gc3 in the management cluster.
[0061] Assuming that the random factor of table T1 is 0.278659258297327, the starting point is 0.278659258297327*50=13.93…, which is rounded up to 14. Then the starting point of traversal of table T1 is n14. The traversal starts from the 14th shard. After determining the traversal starting point, the traversal order of table T1 is n14, n15…n50, n1…n13.
[0062] Assuming that the random factor of table T2 is 0.980936152842768, the starting point of T2 is 50, and the traversal order is n50, n1, n2...n49;
[0063] After generating the above plan, execute the following SQL statement:
[0064] SELECT column1, column2, ...
[0065] FROM T1
[0066] LIMIT 10 OFFSET 10000;
[0067] In the above SQL statement, 10 is the number of rows to be returned, and 10000 is the number of rows to be skipped in the result set, that is, the offset. After execution, the offset starts from shard n14 and returns 10 rows of data after row 10001. If the data in shard n28 is insufficient, it will be supplemented from shard n29, and so on, until the query of shard n13 is completed and the result set is returned;
[0068] After generating the above plan, the following SQL statement is also executed:
[0069] SELECT column1, column2, ...
[0070] FROM T2
[0071] LIMIT 10 OFFSET 10000;
[0072] Then the offset starts from shard n50 and 10 rows of data after row 10001 are returned. If the data in shard n50 is insufficient, the data is supplemented from shard n1, and so on, until the query of shard n49 is completed and the result set is returned.
[0073] like Figure 3 As shown, the present invention also provides a result set paging return system based on a distributed database, comprising:
[0074] Random factor generation module 100: used to randomly generate random factors and create a table based on the random factors;
[0075] The traversal starting point calculation module 200 is used to calculate the traversal starting point for executing the traversal plan according to the random factor generated by the random factor generation module 100 and the preset number of traversal slices;
[0076] The execution order acquisition module 300 is used to obtain the traversal starting point obtained by the traversal starting point calculation module 200, and sequentially increase it according to the number of traversal slices, and then turn to the first slice after the last slice, until the previous node of the traversal starting point, to obtain the execution order;
[0077] Traversal execution module 400: used to execute paging query statements according to the execution order generated by the execution order acquisition module 300, traverse the table, and when the current traversal shard result set is insufficient, the next traversal shard will supplement the current traversal shard result set and output the traversal result.
[0078] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or make equivalent replacements for some of the technical features therein. However, these modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present invention.
Claims
1. A method for returning result sets in pages based on a distributed database. It is characterized in that include: S1: Randomly generate a random factor and create a table based on the random factor; Among them, the random factor in step S1 is A random value in the interval; Wherein, when creating a table based on a random factor, the random factor is a virtual attribute of the table creation statement; The specific process of step S1 is to add a virtual attribute - random factor when creating a table statement in the database. The random factor is automatically generated and saved by the system when creating the table. The random factors between tables are independent. S2: Preset the number of traversal shards, and calculate the traversal starting point for executing the traversal plan according to the random factor and the number of traversal shards; S3: Starting from the traversal starting point, the number of traversed shards is increased in sequence, and after reaching the last shard, it is transferred to the first shard until the previous node of the traversal starting point is reached, and the execution order is obtained; S4: Execute the paging query statement according to the execution order to traverse the table. When the current traversal shard result set is insufficient, the next traversal shard is used to supplement the current traversal shard result set to obtain the traversal result.
2. According to the method for returning result sets by paging based on a distributed database according to claim 1, It is characterized in that In step S2, the traversal starting point is a table node corresponding to a rounded-up value of the product of the random factor and the number of traversal fragments.
3. According to the method for returning result sets by paging based on a distributed database according to claim 1, It is characterized in that Step S4 also includes: When the cluster node where the currently traversed shard is located fails, the traversal process is executed by the cluster node where the copy of the currently traversed shard is located.
4. The method for returning result sets by paging based on a distributed database according to claim 3, It is characterized in that When the traversal process is executed by the cluster node where the copy of the currently traversed shard is located, the execution order does not change.
5. The method for returning result sets by paging based on a distributed database according to claim 1, It is characterized in that Step S4 also includes: When the paging query statement includes a where condition, the execution order does not change.
6. A result set paging return system based on distributed database, It is characterized in that include: Random factor generation module: used to randomly generate random factors and create a table based on the random factors; The random factor is A random value in the interval; When creating a table based on a random factor, the random factor is a virtual attribute of the table creation statement; The specific process is to add a virtual attribute - random factor when creating a table statement in the database. The random factor is automatically generated and saved by the system when creating the table. The random factors between tables are independent. Traversal starting point calculation module: used to calculate the traversal starting point for executing the traversal plan according to the random factor generated by the random factor generation module and the preset traversal fragment number; Execution order acquisition module: used to obtain the traversal starting point obtained by the traversal starting point calculation module, and sequentially increase it according to the number of traversal shards, and then turn to the first shard after reaching the last shard, until the previous node of the traversal starting point, to obtain the execution order; Traversal execution module: used to execute paging query statements according to the execution order generated by the execution order acquisition module, traverse the table, and when the current traversal shard result set is insufficient, the next traversal shard will supplement the current traversal shard result set and output the traversal result.
