A data sorting method, device, equipment, medium and product in a database

Abstract

This invention discloses a data sorting method, apparatus, device, medium, and product in a database. The method includes: obtaining a query statement that meets optimization conditions, wherein the optimization conditions include the query statement requiring sorting of a range partition table during execution, and the sorting item of the query statement matching the range partition column of the range partition table; determining the degree of parallelism; traversing the data sub-tables included in the range partition table, and according to the degree of parallelism, calling a corresponding number of worker threads to scan the data in the data sub-tables in parallel, and sorting the scanned data to obtain sorted data; merging all the sorted data corresponding to each data sub-table to obtain the sorting result of the range partition table. The technical solution of this invention utilizes the ordered nature of data between the sub-tables of a range partition table to reduce memory usage during querying and sorting of the range partition table, avoiding memory bloat and reducing sorting time.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to a data sorting method, apparatus, device, medium, and product in a database. Background Technology

[0002] With the explosive growth of enterprise data, efficient processing of massive amounts of data has become a core challenge for database systems. Existing technologies often use horizontal partitioned tables to manage this data.

[0003] A horizontally partitioned table can be created by splitting a logically large table into multiple physically stored sub-tables. Each sub-table can contain a subset of rows from the horizontally partitioned table and share the same table structure. Horizontal partitioning mainly includes range partitioning, list partitioning, and hash partitioning. A horizontally partitioned table that creates partitions based on the range of values ​​in one or more columns of the table is called a range partitioned table.

[0004] When executing Structured Query Language (SQL) queries on range-partitioned tables, multiple threads or database instances often need to scan all the data in the query table in parallel and then sort the query results before outputting them. However, scanning the entire range-partitioned table and storing the large amount of data obtained in memory for sorting results in long sorting times and consumes a lot of memory, which can easily lead to memory bloat. Summary of the Invention

[0005] This invention provides a data sorting method, apparatus, device, medium, and product in a database to solve the problem of memory bloat during data sorting.

[0006] According to one aspect of the present invention, a data sorting method in a database is provided, comprising: Obtain a query statement that meets the optimization conditions, wherein the optimization conditions include that the query statement needs to sort the range partition table when it is executed, and the sorting item of the query statement matches the range partition column of the range partition table; Determine the degree of parallelism; Traverse the data sub-tables included in the range partition table, and according to the parallelism, call the corresponding number of worker threads to scan the data in the data sub-tables in parallel, and sort the scanned data to obtain the sorted data; The sorted data corresponding to each of the data sub-tables are merged to obtain the sorting result of the range partition table.

[0007] According to another aspect of the present invention, a data sorting apparatus for a database is provided, comprising: The acquisition module is used to acquire query statements that meet optimization conditions, including that the query statement needs to sort the range partition table when it is executed, and that the sorting item of the query statement matches the range partition column of the range partition table. The module is used to determine the degree of parallelism. The sorting module is used to traverse the data sub-tables included in the range partition table, call a corresponding number of worker threads to scan the data in the data sub-tables in parallel according to the parallelism, sort the scanned data, and obtain the sorted data. The merge module is used to merge all the sorted data corresponding to each of the data sub-tables to obtain the sorting result of the range partition table.

[0008] According to another aspect of the present invention, an electronic device is provided, the electronic device comprising: At least one processor; and A memory communicatively connected to the at least one processor; wherein, The memory stores a computer program that can be executed by the at least one processor, the computer program being executed by the at least one processor to enable the at least one processor to perform the method described in any embodiment of the present invention.

[0009] According to another aspect of the present invention, a computer-readable storage medium is provided, the computer-readable storage medium storing computer instructions for causing a processor to execute and implement the method described in any embodiment of the present invention.

[0010] According to another aspect of the present invention, a computer program product is provided, the computer program product comprising a computer program that, when executed by a processor, implements the method described in any embodiment of the present invention.

[0011] The technical solution of this invention involves obtaining a query statement that meets optimization conditions, including that the query statement needs to sort a range partition table during execution, and that the sorting item of the query statement matches the range partition column of the range partition table; determining the degree of parallelism; traversing the data sub-tables included in the range partition table; calling a corresponding number of worker threads to scan the data in the data sub-tables in parallel according to the degree of parallelism; sorting the scanned data to obtain sorted data; and merging all the sorted data corresponding to each data sub-table to obtain the sorted result of the range partition table. This technical solution, by changing the one-time scan and sorting of the range partition table to sequential scanning and sorting of the data sub-tables, utilizes the ordered nature of the data between the sub-tables of the range partition table to merge the sorted data of the data sub-tables, thereby obtaining the sorted result of the range partition table. This reduces the memory usage of a single sorting operation, avoids memory bloat, speeds up the return speed of query and sorting results, and reduces the response time of the query statement.

[0012] It should be understood that the description in this section is not intended to identify key or essential features of the embodiments of the present invention, nor is it intended to limit the scope of the invention. Other features of the invention will become readily apparent from the following description. Attached Figure Description

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

[0014] Figure 1 This is a flowchart of the execution plan of a data sorting method provided in an embodiment of the present invention; Figure 2 This is a flowchart of a data sorting method in a database provided in Embodiment 1 of the present invention; Figure 3 This is a flowchart of a data sorting method in a database provided in Embodiment 2 of the present invention; Figure 4 This is a schematic diagram of the structure of a data sorting device in a database provided in Embodiment 3 of the present invention; Figure 5 A schematic diagram of an electronic device that can be used to implement embodiments of the present invention is shown. Detailed Implementation

[0015] To enable those skilled in the art to better understand the present invention, the technical solutions of the present invention will be clearly and completely described below with reference to the accompanying drawings of the embodiments of the present invention. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of the present invention.

[0016] It should be noted that the terms "comprising" and "having," and any variations thereof, are intended to cover non-exclusive inclusion, for example, a process, method, system, product, or device that includes a series of steps or units is not necessarily limited to those steps or units that are explicitly listed, but may include other steps or units that are not explicitly listed or that are inherent to such process, method, product, or device.

[0017] If a query specifies that all records in a range-partitioned table be retrieved, and the query results are sorted in ascending or descending order according to the range partition column, the possible execution plan for the above query in a parallel execution environment is as follows. It's important to note that parallelism can refer to single-machine parallelism or multi-machine parallelism. Single-machine parallelism refers to multiple threads executing the plan concurrently on the same database instance. Multi-machine parallelism refers to executing the plan simultaneously on multiple database instances, with each instance having its own degree of parallelism. There is no fundamental difference between single-machine parallelism and multi-machine parallelism. Taking single-machine parallelism as an example, the execution plan for the sorting method is as follows: Figure 1 As shown, Figure 1 This is a flowchart of the execution plan for a data sorting method provided in an embodiment of the present invention. Figure 1In the execution plan shown, the plan tree contained in each box is treated as a whole and completed in a set of threads; that is, the root plan is completed by one set of threads, and the sub-plans are completed by another set of threads. The parallelism of the root plan is always 1, meaning that the root plan is always executed by a single thread. Data transfer between sub-plans and between sub-plans and the root plan is accomplished through the data send operator (Send) and the data receive operator (Recv). The operators in the plan have the following meanings: Scan: Data scanning operator, such as Scan(T1) means scanning the data in the range partition table (denoted as T1) and passing the scan result to its parent operator; Sort: Data sorting operator, receives data sent by its child operator (i.e., the data scanning operator), sorts it, and then passes the sorting result to its parent operator; Send: Data sending operator, sends data to the Recv operator across threads (and possibly across database instances if it is multi-machine parallelism); Recv: Data receiving operator, receives data sent by one or more Send operators, and then passes the data to its parent operator (i.e., the projection operator); Project: Projection operator, outputs data. For the above execution plan, if the Sort operator sorts all data in table T1 together, not only will the sorting time be long, but it will also consume a large amount of memory, easily leading to memory bloat.

[0018] Example 1 Figure 2 This is a flowchart illustrating a data sorting method in a database according to Embodiment 1 of the present invention. This embodiment is applicable to sorting data in a range data table. The method can be executed by a data sorting device in a database, which can be implemented in hardware and / or software and can be configured in an electronic device. For example, the electronic device can be a database, a server, or a computer. Figure 2 As shown, the method includes: S110. Obtain a query statement that meets the optimization conditions, wherein the optimization conditions include that the query statement needs to sort the range partition table when it is executed, and the sorting item of the query statement matches the range partition column of the range partition table.

[0019] In this embodiment, optimization conditions can be preconditions that need to be met to optimize the query statement. Optimization conditions can indicate that the object to be sorted is a range-partitioned table, and that the sorting should be based on the size of the values ​​in all range-partitioning columns of the range-partitioned table. A query statement can be a statement used to retrieve specific data from a database. The query statement can be written using SQL. The query statement can include the identifier of the data table to be queried and sorting terms. A range-partitioned table can be partitioned according to the size range of values ​​in one or more data columns. The value range between each partition (i.e., sub-table) of the range-partitioned table is fixed, and the data is ordered; the order is related to the value range of the data in the range-partitioning columns when the range-partitioned table was created. Each partition of the range-partitioned table can correspond to a sub-table. A sorting term can be the part of the query statement that indicates the output order of the query results. A sorting term can be represented by one or more columns in the data table. A sorting term can indicate that the query results are output in ascending or descending order according to the size of the values ​​in one or more columns of the data table. A range-partitioned table can be a data table partitioned according to a range-partitioning method. Range-partitioning columns can be the columns used as the basis for data division when performing range partitioning in the range-partitioned table. A range partitioned table can include multiple range partition columns, and there is a certain order among the range partition columns.

[0020] Specifically, the query statement is parsed to determine if it meets optimization criteria. These criteria include that the query object is a range-partitioned table, the range-partitioned table needs to be sorted, and the sorting terms in the query statement need to match all the range partitioning columns used to partition the range-partitioned table. Query statements that meet the optimization criteria are then optimized.

[0021] For example, when partitioning the range-partitioned table T1, the partitions are based on the range of values ​​in the partition column C. For instance, data with values ​​less than 1000 in column C belongs to sub-table P1 (also called a sub-partition); data with values ​​between 1000 and 2000 in column C belongs to sub-table P2; data with values ​​between 2000 and 3000 in column C belongs to sub-table P3; and data with values ​​between 3000 and 4000 in column C belongs to sub-table P4. In this way, table T1 is divided into four sub-tables. The data in each sub-table may be unordered, but the data in column C is ordered across the sub-tables. For example, the value in column C of data in sub-table P2 is always greater than the value in column C of data in sub-table P1.

[0022] For example, continuing from the above example, the query statement can be to query table T1, and the query results can be sorted in ascending order according to the value of column C. The query statement indicates that the query object is all the data in the range partition table, and the sorting item is column C. The range partition table needs to be sorted, and the sorting item matches the range partition column of the range partition table. Therefore, the query statement meets the optimization conditions.

[0023] S120, Determine the degree of parallelism.

[0024] Specifically, the number of threads that can be invoked, i.e., the degree of parallelism, is determined based on available hardware resources and the data size of the range partition table. For example, the degree of parallelism can be determined based on the maximum number of parallel threads supported by the database and the data size of the range partition table.

[0025] S130. Traverse the data sub-tables included in the range partition table, and according to the parallelism, call the corresponding number of worker threads to scan the data in the data sub-tables in parallel, and sort the scanned data to obtain the sorted data.

[0026] In this embodiment, the data sub-table can be a sub-table of a range-partitioned table. The data sub-table can include sub-tables obtained by partitioning the range-partitioned table according to the range partitioning column. The worker threads can be threads used to scan and sort the data sub-table in parallel. The number of worker threads can be a numerical value representing the degree of parallelism. Multiple worker threads can scan the data sub-table in parallel. The amount of data scanned by each worker thread each time can be the ratio of the data volume of the data sub-table to the degree of parallelism. The sorted data can be the data obtained by sorting the data after scanning by the worker threads. The sorted data can be the data allocated when multiple worker threads scan the data sub-table in parallel.

[0027] Specifically, the data sub-tables included in the range partition table are traversed sequentially. The order can be ascending or descending order when the range partition table was partitioned to obtain the data sub-tables. The data sub-tables are read sequentially, and each worker thread is allocated data to be scanned and sorted according to the amount of data included in the sub-table and the degree of parallelism. The amount of data can be the ratio of the amount of data included in the sub-table to the degree of parallelism. After scanning the data in the assigned data sub-table, each worker thread caches the data in memory and sorts it to obtain the sorted data. The sorting method can be such as bubble sort, selection sort, and / or heap sort, etc., and this invention does not impose any limitations.

[0028] S140. Merge all the sorted data corresponding to each of the data sub-tables to obtain the sorting result of the range partition table.

[0029] In this embodiment, the sorting result can be the sorted result of the data in the range partition table. The sorting result can also be the result of concatenating all sorted data from each data sub-table. Since the data in the data sub-tables of the range partition table is ordered, a simple concatenation of the sorted data from each data sub-table according to the ascending or descending order of the data when the range partition table was partitioned yields a globally ordered sorting result for the range partition table.

[0030] Specifically, for each data sub-table, all sorted data is merged. For example, the merging process could involve sorting all sorted data based on the size of the header element, merging all sorted data, and completing the merging of a single data sub-table. After merging all data sub-tables, according to the query statement's instructions, and following the ascending or descending order of the range partitioning sequence, all data sub-tables are concatenated to obtain the sorted result of the range partitioning table.

[0031] The technical solution of this invention involves obtaining a query statement that meets optimization conditions, including that the query statement needs to sort a range partition table during execution, and that the sorting item of the query statement matches the range partition column of the range partition table; determining the degree of parallelism; traversing the data sub-tables included in the range partition table; calling a corresponding number of worker threads to scan the data in the data sub-tables in parallel according to the degree of parallelism; sorting the scanned data to obtain sorted data; and merging all the sorted data corresponding to each data sub-table to obtain the sorted result of the range partition table. This technical solution, by changing the one-time scan and sorting of the range partition table to sequential scanning and sorting of the data sub-tables, utilizes the ordered nature of the data between the sub-tables of the range partition table to merge the sorted data of the data sub-tables, thereby obtaining the sorted result of the range partition table. This reduces the memory usage of a single sorting operation, avoids memory bloat, speeds up the return speed of query and sorting results, and reduces the response time of the query statement.

[0032] In one embodiment, obtaining the query statement that satisfies the optimization conditions includes: S1101, Obtain the query statement.

[0033] Specifically, it receives structured query statements entered by users through query interfaces such as database clients, command-line terminals, or integrated development environments, or through physical interfaces provided by the database.

[0034] S1102. Parse the query statement to obtain the execution object and sorting item. The execution object includes the data table that the query statement indicates needs to be queried and sorted.

[0035] In this embodiment, the execution object can be the object specified in the query statement that needs to be queried. The execution object can be determined according to the syntax structure of the query statement; for example, the table name or view name specified after the FROM clause is the execution object of the query statement. The data table can be the table specified in the query statement that needs to be queried. The data table can be a range-partitioned table.

[0036] Specifically, the query statement is parsed according to its syntax structure to obtain the execution object and sorting item indicated by the query statement. The execution object can be the data table to be queried.

[0037] S1103. Determine whether the execution object and the sorting item meet the optimization conditions.

[0038] Specifically, if the object of execution is to sort a range-partitioned data table, and the sorting item includes all range-partitioned columns, then the object of execution and the sorting item are determined to meet the optimization conditions; otherwise, the optimization conditions are not met.

[0039] For example, the execution object that meets the optimization criteria could be one that requires sorting after scanning a range-partitioned table or after filtering a single table. The sorting item that meets the optimization criteria could include all range-partitioned columns.

[0040] S1104. If the optimization conditions are met, the query statement is determined to be a query statement that meets the optimization conditions.

[0041] Specifically, if the execution object and sorting item meet the optimization conditions, then the query statement corresponding to the execution object and sorting item will be determined as the query statement that meets the optimization conditions.

[0042] In one embodiment, matching the sorting item with the range partition column of the range partition table includes: The sorting item includes all the range partition columns of the range partition table, and the sorting item is in the same order as the range partition columns.

[0043] Specifically, the sorting item that meets the optimization criteria should include all the range partitioning columns of the range partitioning table, and the order of the columns in the sorting item should be consistent with the order of the range partitioning columns.

[0044] For example, if the range partitioning columns of the range partitioning table include columns C1, C2, and C3, and the sorting order of the range partitioning columns is (C1, C2, C3), then the sorting item can be (C1, C2, C3, C4), that is, the sorting item contains all the range partitioning columns C1, C2, and C3, and the order of the columns in the sorting item is consistent with the order of the range partitioning columns.

[0045] In one embodiment, merging all the sorted data corresponding to each of the data sub-tables to obtain the sorting result of the range partition table includes: S1401. For each data sub-table, the sorted data is sent to the data receiving operator through the data sending operator.

[0046] In this embodiment, the data send operator can be a data send operator called by a worker thread. The data send operator can send the sorted data processed by the worker thread to a data receive operator in another thread. The data receive operator can be an operator used to receive sorted data. The data receive operator can merge the sorted data.

[0047] Specifically, after receiving the sorted data, each worker thread sends the sorted data to the data receiver by calling the data send operator.

[0048] S1402. After the data receiving operator obtains the sorted data of all working threads of the data sub-table, the sorted data is merged to obtain the sorting result of the data sub-table.

[0049] Specifically, after receiving all sorted data corresponding to the data sub-table, the data receiving operator sorts all sorted data according to, for example, the size of the head element of the sorted data, and then merges them. The result of the merge is the sorting result of the data sub-table.

[0050] S1403. Merge the sorting results of each data sub-table according to the partitioning order of each data sub-table in the range partitioning table to obtain the sorting result of the range partitioning table.

[0051] In this embodiment, the partitioning order can be the logical order of the range partitions in the range partitioning table. The partitioning order can be determined based on the value range of the data in the range partitioning column. For example, when performing range partitioning, partitioning is performed according to the size range of the values ​​in the range partitioning column. Data with values ​​less than 1000 in the range partitioning column is divided into data sub-table P1, data with values ​​greater than or equal to 1000 and less than 2000 in the range partitioning column is divided into data sub-table P2, and data with values ​​greater than or equal to 2000 in the range partitioning column is divided into data sub-table P3. Then, according to the ascending order of the values ​​in the range partitioning column, the partitioning order is P1, P2, and P3.

[0052] Specifically, for the sorting results of each data sub-table, the sorting results of each data sub-table are merged together according to the partition order corresponding to each data sub-table to obtain the sorting result of the range partition table.

[0053] In one embodiment, the degree of parallelism is positively correlated with at least one of the following: The number of data sub-tables; the number of data in the range partition table.

[0054] Specifically, the degree of parallelism can be positively correlated with the number of data sub-tables and / or the amount of data in the range partition table. For example, when there are many data sub-tables or a large amount of data in the range partition table, the degree of parallelism can be increased to support the parallel execution of more worker threads, thereby making full use of hardware resources and accelerating the processing speed of a single data sub-table. Conversely, if there are few data sub-tables or a small amount of data in the range partition table, the degree of parallelism can be appropriately reduced to avoid the additional overhead of thread creation and scheduling, achieving a balance between resource utilization and execution efficiency.

[0055] Example 2 Figure 3 This is a flowchart illustrating a data sorting method in a database according to Embodiment 2 of the present invention. This embodiment is an optimization based on any of the above embodiments, and mainly includes a detailed description of the process of creating a sub-table linked list according to the order indicated in the query statement, and traversing the data sub-tables according to the order of the nodes in the sub-table linked list. It should be noted that technical details not described in detail in this embodiment can be found in any of the above embodiments. Figure 3 As shown, the method includes: S210. Obtain a query statement that meets the optimization conditions, wherein the optimization conditions include that the query statement needs to sort the range partition table when it is executed, and the sorting item of the query statement matches the range partition column of the range partition table.

[0056] S220, Determine the degree of parallelism.

[0057] S230. Obtain the order indicated in the query statement.

[0058] In this embodiment, the order indicated in the query statement can be the order in which the output sorting results are displayed. The order indicated in the query statement can be ascending or descending.

[0059] Specifically, based on the syntax structure of the query statement, the order in which the output results are sorted, as indicated in the query statement, is obtained. For example, the order indicated in the query statement could be ascending order based on the numerical value of the sorting items, or descending order based on the numerical value of the sorting items.

[0060] S240. Create a sub-table linked list according to the order. The sub-table linked list includes multiple nodes, each node corresponding to a data sub-table of the range partition table. The order of each node in the sub-table linked list is related to the size of the data in the corresponding data sub-table.

[0061] In this embodiment, the sub-table linked list can be a linked list indicating the order in which data sub-tables are traversed. The sub-table linked list includes multiple nodes. A node can be a node in the sub-table linked list that indicates a data sub-table. The arrangement of nodes in the linked list is determined by their corresponding partition order and the order indicated in the query statement.

[0062] Specifically, the order of traversing the data sub-tables of the range partition table is determined according to the order indicated in the query statement. A corresponding node is created for each data sub-table, and the nodes are connected in order to finally obtain the sub-table linked list.

[0063] S250. Following the order of the nodes in the sub-table linked list, traverse the corresponding data sub-tables in the range partition table in sequence. Based on the degree of parallelism, call a corresponding number of worker threads to scan the data in the data sub-tables in parallel, and sort the scanned data to obtain the sorted data.

[0064] Specifically, following the order of nodes in the sub-table linked list, the corresponding data sub-tables in the range partition table are traversed, and a number of worker threads corresponding to the parallelism are called to scan the data in the data sub-tables in parallel to obtain the sorted data corresponding to each data sub-table.

[0065] S260. Merge all the sorted data corresponding to each of the data sub-tables to obtain the sorting result of the range partition table.

[0066] Specifically, the sorted data corresponding to each data sub-table is merged, and the merged data is concatenated according to the order of the nodes in the sub-table linked list to obtain the sorted result of the range partition table.

[0067] The technical solution of this invention involves obtaining a query statement that meets optimization conditions, including that the query statement needs to sort a range partition table during execution, and that the sorting item of the query statement matches the range partition column of the range partition table; determining the degree of parallelism; obtaining the order indicated in the query statement; creating a sub-table linked list according to the order, wherein the sub-table linked list includes multiple nodes, each node corresponding to a data sub-table of the range partition table, and the order of each node in the sub-table linked list is related to the size of the data in the corresponding data sub-table; traversing the corresponding data sub-tables of the range partition table sequentially according to the order of the nodes in the sub-table linked list; and merging all the sorted data corresponding to each data sub-table to obtain the sorting result of the range partition table. The technical solution of this invention creates a sub-table linked list according to the order indicated in the query statement. Based on the order of the nodes in the sub-table linked list, the corresponding data sub-tables in the range partition table are traversed. Since the order of the nodes in the sub-table linked list is either ascending or descending order of the partition order, by sequentially scanning and sorting the data sub-tables, and then merging the ordered data of all data sub-tables according to the order of the nodes in the sub-table linked list, the sorted result of the range data table can be obtained. This changes the one-time scanning and sorting of the range partition table to a single sorting of the data sub-tables, reducing memory usage during sorting, avoiding memory bloat, speeding up the return speed of query and sorting results, and reducing the response time of the query statement.

[0068] In one embodiment, traversing the data sub-tables corresponding to the range partition table sequentially according to the order of the nodes in the sub-table linked list includes: After the scheduling thread receives the notification information, it retrieves the corresponding data sub-table in the range partition table according to the order of the nodes in the sub-table linked list, until all data sub-tables have been retrieved. The notification information is sent after the data receiving operator has received the sorted data from all worker threads corresponding to a data sub-table.

[0069] In this embodiment, the scheduling thread can be a thread used to manage and schedule worker threads. The number of scheduling threads can be one. The notification information can be information instructing the scheduling thread to continue traversing the next data sub-table. The notification information can be sent by the data receive operator, and the sending time can be after the data receive operator has finished receiving the sorted data from all worker threads.

[0070] Specifically, after the data receiving operator receives the sorted data sent by all worker threads, it considers the merging process complete. The data receiving operator then instructs the scheduling thread to obtain the next data sub-table according to the order of the nodes in the sub-table linked list and distribute it to all worker threads for scanning and sorting, until all data sub-tables corresponding to all nodes in the sub-table linked list have been obtained.

[0071] For example, the data receive operator receives sorted data sent by all worker threads. The data receive operator merges the sorted data, resulting in an ordered sub-table. Once one sub-table is processed, the next sub-table is retrieved according to the order of nodes in the sub-table linked list and assigned to all worker threads for scanning and sorting. This process continues until all sub-tables corresponding to nodes in the sub-table linked list have been processed. Finally, all ordered sub-tables are merged according to the order of nodes in the sub-table linked list to obtain the sorted result of the range partitioned table.

[0072] The following is an exemplary description of embodiments of the present invention, where "sub-table" represents "data sub-table" and "partition column" represents "range partition column": In a horizontally partitioned table, range partitioning ensures that the data within each sub-partition is ordered. When the sorting criteria match the partition column, this data characteristic can be leveraged to sort and output only the data from one sub-table at a time, thus avoiding memory bloat and long response times caused by sorting records from all sub-tables.

[0073] Before sorting, it is necessary to check whether the query statement meets the optimization conditions. The optimization conditions can be: the query statement needs to be sorted after scanning the range partition table or after filtering the single table, and the sorting items include all range partition columns, and the order of the columns in both cases is consistent.

[0074] If the optimization conditions are met, the sub-table linked list is created according to the ascending or descending order specified in the query statement: if it is ascending, the sub-table linked list is created in forward order; if it is descending, the sub-table linked list is created in reverse order. Each node in the linked list corresponds to a sub-table.

[0075] The appropriate degree of parallelism is assigned based on the number of sub-tables (i.e., the number of data sub-tables) and the data size (i.e., the number of data in the range partition table) of table T (i.e., the range partition table) on the current database instance. The degree of parallelism is positively correlated with the number of sub-tables and the data size.

[0076] The scheduling thread retrieves the unprocessed sub-tables (i.e., data sub-tables) sequentially from the head of the sub-table linked list. It then schedules the worker thread containing the data sorting operator to scan and sort the data in the sub-tables. The data sending operator then passes the processed data (i.e., the sorted data) to the data receiving operator. After one sub-table is processed, the scheduling thread continues to retrieve the next sub-table from the sub-table linked list, repeating this process until all sub-tables in the linked list have been processed. At this point, it is considered that all data sub-tables have been traversed, and the data receiving operator no longer waits to receive data; control is transferred to its parent operator (i.e., the projection operator).

[0077] In this process, a number of worker threads, corresponding to the degree of parallelism, simultaneously scan the data sub-tables. The amount of data processed by each worker thread is the ratio of the amount of data in the data sub-table to the degree of parallelism. After sorting, the data sending operator sends the result (i.e., the sorted data) to the data receiving operator. After receiving all the results from this group of threads (i.e., all worker threads) (i.e., the data sub-table sorting is complete), the data receiving operator notifies the scheduling thread to continue to obtain the next sub-table to be scanned from the sub-table linked list.

[0078] In one example, the system first checks if the query meets the optimization criteria. If it does, and the order specified in the query is ascending, a sub-table linked list is created in ascending order. During execution, assuming the range partition table T1 has over ten million rows, and considering available hardware resources, the optimizer determines the parallelism of the sorting sub-plan to be 8. During execution, the scheduling thread retrieves the first sub-table from the sub-table linked list, denoted as P1, allocates 8 threads, and schedules the sub-plan for execution. Each thread retrieves 1 / 8 of the data from P1, sorts it, and sends the sorted result to the data receiving operator. Each thread terminates after processing its assigned data. The data receiving operator receives 8 streams of ordered data and merges them. The merging is considered complete only after all worker threads' data sending operators have sent all their data (i.e., the sorted data). The data receiving operator then notifies the scheduling thread to retrieve the next sub-table to continue execution. The scheduling thread then retrieves the second sub-table and repeats the above rules until all sub-tables have been processed.

[0079] As can be seen from the above execution flow, for the matching and sorting of range partition columns, this invention proposes a decomposition parallel sorting method based on sub-tables, which can reduce the data size of a single sorting, thereby enabling faster output of sorting results and avoiding the occurrence of external sorting.

[0080] Example 3 Figure 4 This is a schematic diagram of a data sorting device in a database according to Embodiment 3 of the present invention. Figure 4 As shown, the device includes: The acquisition module 310 is used to acquire query statements that meet optimization conditions, the optimization conditions including that the query statement needs to sort the range partition table when it is executed, and the sorting item of the query statement matches the range partition column of the range partition table. Module 320 is used to determine the degree of parallelism; The sorting module 330 is used to traverse the data sub-tables included in the range partition table, call a corresponding number of worker threads to scan the data in the data sub-tables in parallel according to the parallelism, sort the scanned data, and obtain the sorted data. The merging module 340 is used to merge all the sorted data corresponding to each of the data sub-tables to obtain the sorting result of the range partition table.

[0081] The technical solution of this invention involves: acquiring query statements that meet optimization conditions through an acquisition module, wherein the optimization conditions include the need to sort the range partition table during query execution, and the sorting item of the query statement matching the range partition column of the range partition table; determining the degree of parallelism through a determination module; traversing the data sub-tables included in the range partition table through a sorting module, calling a corresponding number of worker threads to scan the data in the data sub-tables in parallel according to the degree of parallelism, and sorting the scanned data to obtain sorted data; and merging the sorted data corresponding to each data sub-table by a merging module to obtain the sorted result of the range partition table. This technical solution of the present invention, by changing the one-time scanning and sorting of the range partition table to sequential scanning and sorting of the data sub-tables, utilizes the ordered nature of the data between the sub-tables of the range partition table to merge the sorted data of the data sub-tables, thereby obtaining the sorted result of the range partition table. This reduces the memory usage of a single sorting operation, avoids memory bloat, speeds up the return speed of query and sorting results, and reduces the response time of the query statement.

[0082] In one embodiment, the acquisition module 310 is specifically used for: Retrieve the query statement; Parse the query statement to obtain the execution object and sorting item. The execution object includes the data table that the query statement indicates needs to be queried and sorted. Determine whether the execution object and the sorting item meet the optimization conditions; If the optimization conditions are met, the query statement is determined to be a query statement that satisfies the optimization conditions.

[0083] In one embodiment, the acquisition module 310 is specifically used for: The sorting item includes all the range partition columns of the range partition table, and the sorting item is in the same order as the range partition columns.

[0084] In one embodiment, the sorting module 330 further includes: The acquisition unit is used to acquire the order indicated in the query statement; A creation unit is used to create a sub-table linked list in the order stated therein. The sub-table linked list includes multiple nodes, each node corresponding to a data sub-table of the range partition table. The order of each node in the sub-table linked list is related to the size of the data in the corresponding data sub-table. The traversal unit is used to traverse the corresponding data sub-tables in the range partition table sequentially according to the order of the nodes in the sub-table linked list.

[0085] In one embodiment, traversing the unit is specifically used for: After the scheduling thread receives the notification information, it retrieves the corresponding data sub-table in the range partition table according to the order of the nodes in the sub-table linked list, until all data sub-tables have been retrieved. The notification information is sent after the data receiving operator has received the sorted data from all worker threads corresponding to a data sub-table.

[0086] In one embodiment, the merging module 340 is specifically used for: For each data sub-table, the sorted data is sent to the data receiving operator via the data sending operator; After the data receiving operator obtains the sorted data of all working threads of the data sub-table, the sorted data is merged to obtain the sorting result of the data sub-table. The sorting results of each data subtable are merged according to the partitioning order of each data subtable in the range partition table to obtain the sorting result of the range partition table.

[0087] In one embodiment, the degree of parallelism is positively correlated with at least one of the following: The number of data sub-tables; the number of data in the range partition table.

[0088] The data sorting device in a database provided in this embodiment of the invention can execute a data sorting method in a database provided in any embodiment of the invention, and has the corresponding functional modules and beneficial effects of the method.

[0089] Example 4 Figure 5 A schematic diagram of an electronic device that can be used to implement embodiments of the present invention is shown. The electronic device is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as personal digital processors, cellular phones, smartphones, wearable devices (such as helmets, glasses, watches, etc.), and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely illustrative and are not intended to limit the implementation of the invention described and / or claimed herein.

[0090] like Figure 5As shown, the electronic device 10 includes at least one processor 11 and a memory, such as a read-only memory (ROM) 12 or a random access memory (RAM) 13, communicatively connected to the at least one processor 11. The memory stores computer programs executable by the at least one processor. The processor 11 can perform various appropriate actions and processes based on the computer program stored in the ROM 12 or loaded from storage unit 18 into the RAM 13. The RAM 13 can also store various programs and data required for the operation of the electronic device 10. The processor 11, ROM 12, and RAM 13 are interconnected via a bus 14. An input / output (I / O) interface 15 is also connected to the bus 14.

[0091] Multiple components in electronic device 10 are connected to I / O interface 15, including: input unit 16, such as keyboard, mouse, etc.; output unit 17, such as various types of displays, speakers, etc.; storage unit 18, such as disk, optical disk, etc.; and communication unit 19, such as network card, modem, wireless transceiver, etc. Communication unit 19 allows electronic device 10 to exchange information / data with other devices through computer networks such as the Internet and / or various telecommunications networks.

[0092] Processor 11 can be a variety of general-purpose and / or special-purpose processing components with processing and computing capabilities. Some examples of processor 11 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various special-purpose artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, a digital signal processor (DSP), and any suitable processor, controller, microcontroller, etc. Processor 11 performs the various methods and processes described above, such as the methods proposed in this invention.

[0093] In some embodiments, the method proposed in this invention can be implemented as a computer program tangibly contained in a computer-readable storage medium, such as storage unit 18. In some embodiments, part or all of the computer program can be loaded and / or installed on electronic device 10 via ROM 12 and / or communication unit 19. When the computer program is loaded into RAM 13 and executed by processor 11, one or more steps of the method described above can be performed. Alternatively, in other embodiments, processor 11 can be configured to perform the method proposed in this invention by any other suitable means (e.g., by means of firmware).

[0094] Various embodiments of the systems and techniques described above herein can be implemented in digital electronic circuit systems, integrated circuit systems, field programmable gate arrays (FPGAs), application-specific integrated circuits (ASICs), application-specific standard parts (ASSPs), systems-on-chip (SoCs), complex programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various embodiments may include implementations in one or more computer programs that can be executed and / or interpreted on a programmable system including at least one programmable processor, which may be a dedicated or general-purpose programmable processor, capable of receiving data and instructions from a storage system, at least one input device, and at least one output device, and transmitting data and instructions to the storage system, the at least one input device, and the at least one output device.

[0095] Computer programs used to implement the methods of the present invention may be written in any combination of one or more programming languages. These computer programs may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, such that when executed by the processor, the computer programs cause the functions / operations specified in the flowcharts and / or block diagrams to be performed. The computer programs may be executed entirely on a machine, partially on a machine, or as a standalone software package, partially on a machine and partially on a remote machine, or entirely on a remote machine or server.

[0096] In the context of this invention, a computer-readable storage medium stores computer instructions that are used to cause a processor to execute and implement the method provided by this invention.

[0097] The present invention also provides a computer program product comprising a computer program that, when executed by a processor, implements the method provided according to embodiments of the present invention.

[0098] Computer-readable storage media can be tangible media that may contain or store computer programs for use by or in conjunction with an instruction execution system, apparatus, or device. Computer-readable storage media can be, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatus, or devices, or any suitable combination thereof. Alternatively, computer-readable storage media can be machine-readable signal media. More specific examples of machine-readable storage media include electrical connections based on one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fibers, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof.

[0099] To provide interaction with a user, the systems and techniques described herein can be implemented on an electronic device having: a display device for displaying information to the user, such as a cathode ray tube (CRT) or a liquid crystal display (LCD); and a keyboard and pointing device (e.g., a mouse or trackball) through which the user provides input to the electronic device. Other types of devices can also be used to provide interaction with the user; for example, feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including sound input, voice input, or tactile input).

[0100] The systems and technologies described herein can be implemented in computing systems that include backend components (e.g., as data servers), or middleware components (e.g., application servers), or frontend components (e.g., user computers with graphical user interfaces or web browsers through which users can interact with implementations of the systems and technologies described herein), or any combination of such backend, middleware, or frontend components. The components of the system can be interconnected via digital data communication of any form or medium (e.g., communication networks). Examples of communication networks include local area networks (LANs), wide area networks (WANs), blockchain networks, and the Internet.

[0101] A computing system can include clients and servers. Clients and servers are generally located far apart and typically interact through communication networks. The client-server relationship is created by computer programs running on the respective computers and having a client-server relationship with each other. The server can be a cloud server, also known as a cloud computing server or cloud host, which is a hosting product within the cloud computing service system. It addresses the shortcomings of traditional physical hosts and Virtual Private Server (VPS) services, such as high management difficulty and weak business scalability.

[0102] It should be understood that the various forms of processes shown above can be used, with steps reordered, added, or deleted. For example, the steps described in this invention can be executed in parallel, sequentially, or in different orders, as long as the desired result of the technical solution of this invention can be achieved, and this is not limited herein.

[0103] The specific embodiments described above do not constitute a limitation on the scope of protection of this invention. Those skilled in the art should understand that various modifications, combinations, sub-combinations, and substitutions can be made according to design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of this invention should be included within the scope of protection of this invention.

Claims

1. A data sorting method in a database, characterized in that, include: Obtain a query statement that meets the optimization conditions, wherein the optimization conditions include that the query statement needs to sort the range partition table when it is executed, and the sorting item of the query statement matches the range partition column of the range partition table; Determine the degree of parallelism; Traverse the data sub-tables included in the range partition table, and according to the parallelism, call the corresponding number of worker threads to scan the data in the data sub-tables in parallel, and sort the scanned data to obtain the sorted data; The sorted data corresponding to each of the data sub-tables are merged to obtain the sorting result of the range partition table.

2. The method according to claim 1, characterized in that, The process of obtaining query statements that satisfy the optimization conditions includes: Retrieve the query statement; Parse the query statement to obtain the execution object and sorting item. The execution object includes the data table that the query statement indicates needs to be queried and sorted. Determine whether the execution object and the sorting item meet the optimization conditions; If the optimization conditions are met, the query statement is determined to be a query statement that satisfies the optimization conditions.

3. The method according to claim 2, characterized in that, The matching of the sorting item with the range partition column of the range partition table includes: The sorting item includes all the range partition columns of the range partition table, and the sorting item is in the same order as the range partition columns.

4. The method according to claim 1, characterized in that, The traversal of the data sub-tables included in the range partition table includes: Obtain the order indicated in the query statement; A sub-table linked list is created in the order described above. The sub-table linked list includes multiple nodes, each node corresponding to a data sub-table of the range partition table. The order of each node in the sub-table linked list is related to the size of the data in the corresponding data sub-table. According to the order of the nodes in the sub-table linked list, the corresponding data sub-tables in the range partition table are traversed sequentially.

5. The method according to claim 4, characterized in that, The step of traversing the data sub-tables corresponding to the range partition table sequentially according to the order of the nodes in the sub-table linked list includes: After the scheduling thread receives the notification information, it retrieves the corresponding data sub-table in the range partition table according to the order of the nodes in the sub-table linked list, until all data sub-tables have been retrieved. The notification information is sent after the data receiving operator has received the sorted data from all worker threads corresponding to a data sub-table.

6. The method according to claim 1, characterized in that, The step of merging all the sorted data corresponding to each of the data sub-tables to obtain the sorting result of the range partition table includes: For each data sub-table, the sorted data is sent to the data receiving operator via the data sending operator; After the data receiving operator obtains the sorted data of all working threads of the data sub-table, the sorted data is merged to obtain the sorting result of the data sub-table. The sorting results of each data subtable are merged according to the partitioning order of each data subtable in the range partition table to obtain the sorting result of the range partition table.

7. The method according to claim 1, characterized in that, The degree of parallelism is positively correlated with at least one of the following: The number of data sub-tables; the number of data in the range partition table.

8. A data sorting device for a database, characterized in that, include: The acquisition module is used to acquire query statements that meet optimization conditions, including that the query statement needs to sort the range partition table when it is executed, and that the sorting item of the query statement matches the range partition column of the range partition table. The module is used to determine the degree of parallelism. The sorting module is used to traverse the data sub-tables included in the range partition table, call a corresponding number of worker threads to scan the data in the data sub-tables in parallel according to the parallelism, sort the scanned data, and obtain the sorted data. The merge module is used to merge all the sorted data corresponding to each of the data sub-tables to obtain the sorting result of the range partition table.

9. An electronic device, characterized in that, The electronic device includes: At least one processor; and A memory communicatively connected to the at least one processor; wherein, The memory stores a computer program that can be executed by the at least one processor to enable the at least one processor to perform the method of any one of claims 1-7.

10. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer instructions that cause a processor to execute the method of any one of claims 1-7.

11. A computer program product, characterized in that, The computer program product includes a computer program that, when executed by a processor, implements the method according to any one of claims 1-7.

Images