A method and device for sorting data in a relational database

Abstract

The present invention discloses a relational database data sorting method and device, which is applied to a processing environment with multiple sorting processes, including: allocating the memory used by each sorting process to each sorting process based on the available memory size; dividing the data file to be sorted into multiple data blocks based on the memory used by the sorting process; and dividing the data file to be sorted into at least two parts of data; using two sorting processes, reading a data block from the part of data for sorting; merging the ordered files output by each sorting process to obtain a sorted file. The embodiment of the present invention can greatly improve the sorting performance under new hardware by changing the external sorting to parallel execution, dividing it into several sorting processes, and executing the read-sort-write steps in parallel, and designs a special data reading method, which can effectively avoid the data disorder problem that may be caused by concurrent reading.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a method and device for sorting relational database data. Background Art

[0002] The database manages data and needs to sort the data. When the amount of data is large, the database will use an external sorting algorithm to sort the data.

[0003] There are two types of database sorting: internal sorting and external sorting. Internal sorting is when the amount of data is small, all data is read into the memory for sorting, and the internal sorting speed is very fast. External sorting is when the amount of data is large, the data to be sorted needs to be written to the disk with the help of a disk for sorting, and the external sorting speed is very slow.

[0004] In order to improve the speed of external sorting in the prior art, the method adopted is to read a small part of the data into the memory each time, then sort this part of the data internally (the sorting in the memory mentioned above), and then write this part of the sorted data into the disk file, and repeat this cycle to obtain multiple sorted data files, and finally merge and sort these data files on the disk (that is, read the first number from these files each time, select the smallest number and write it to the final result file, and then read the next number until the end). Summary of the invention

[0005] The embodiment of the present invention provides a method and device for sorting data in a relational database, which are used to optimize the efficiency of external sorting by combining the performance of existing hardware.

[0006] The embodiment of the present invention provides a method for sorting data in a relational database, which is applied to a processing environment with multiple sorting processes, and includes:

[0007] Based on the available memory size, allocate the memory used by the sorting process to each sorting process involved in the sorting;

[0008] Dividing the data file to be sorted into a plurality of data blocks based on the memory used by the sorting process; and

[0009] Dividing the data file to be sorted into at least two parts of data, each part of data including at least two data blocks;

[0010] Using two sorting processes, a data block is read from the portion of data for sorting, and the two sorting processes read the data blocks in opposite directions;

[0011] Merge the ordered files output by each sorting process to obtain a sorted file.

[0012] In some embodiments, reading a data block from the portion of data for sorting includes:

[0013] Configure a shared memory;

[0014] The shared memory is used to record the sorting duration of the corresponding data blocks executed by the two sorting processes;

[0015] Determine the relationship between the difference in sorting time of the two sorting processes and the preset time;

[0016] The reading order of the data blocks of the two sorting processes is adjusted based on the size relationship.

[0017] In some embodiments, adjusting the reading order of the data blocks of the two sorting processes based on the size relationship includes:

[0018] If the difference between the sorting time of the first sorting process and the sorting time of the second sorting process of the two sorting processes is greater than a preset time, the reading order of the next data block of the second sorting process is changed.

[0019] In some embodiments, the shared memory records the file numbers of the data blocks processed by each sorting process.

[0020] In some embodiments, merging the ordered files output by each sorting process includes:

[0021] Calling the same number of merging processes as the number of the sorting processes involved in the sorting, merging the ordered files output by the sorting processes through the merging processes to obtain sorted sub-files;

[0022] The sorted sub-files are merged to obtain the sorted file.

[0023] In some embodiments, the merge processes and the sort processes are run in parallel.

[0024] In some embodiments, dividing the data file to be sorted into at least two parts of data includes:

[0025] The number of partial data divided from the data file to be sorted is half the number of sorting processes.

[0026] The embodiment of the present invention further provides a relational database data sorting device, comprising a processor, wherein the processor has multiple sorting processes, and the processor is configured to:

[0027] Based on the available memory size, allocate the memory used by the sorting process to each sorting process involved in the sorting;

[0028] Dividing the data file to be sorted into a plurality of data blocks based on the memory used by the sorting process; and

[0029] Dividing the data file to be sorted into at least two parts of data, each part of data including at least two data blocks;

[0030] Using two sorting processes, a data block is read from the portion of data for sorting, and the two sorting processes read the data blocks in opposite directions;

[0031] Merge the ordered files output by each sorting process to obtain a sorted file.

[0032] An embodiment of the present invention further provides a computer-readable storage medium, on which a computer program is stored. When the computer program is executed by a processor, the steps of the relational database data sorting method described in each embodiment of the present disclosure are implemented.

[0033] The embodiment of the present invention changes the external sorting into parallel execution, divides it into several sorting processes, and executes the read-sort-write steps in parallel, which can greatly improve the sorting performance under new hardware, and designs a special data reading method to effectively avoid the data disorder problem that may be caused by concurrent reading.

[0034] The above description is only an overview of the technical solution of the present invention. In order to more clearly understand the technical means of the present invention, it can be implemented according to the contents of the specification. In order to make the above and other purposes, features and advantages of the present invention more obvious and easy to understand, the specific implementation methods of the present invention are listed below. BRIEF DESCRIPTION OF THE DRAWINGS

[0035] Various other advantages and benefits will become apparent to those of ordinary skill in the art by reading the detailed description of the preferred embodiments below. The accompanying drawings are only for the purpose of illustrating the preferred embodiments and are not to be considered as limiting the present invention. Moreover, the same reference symbols are used throughout the accompanying drawings to represent the same components. In the accompanying drawings:

[0036] Figure 1 A schematic diagram of the basic flow of the relational database data sorting method according to an embodiment of the present application;

[0037] Figure 2 This is an example of a process reading of a relational database data sorting method according to an embodiment of the present application;

[0038] Figure 3 A schematic diagram of a reading order adjustment process of a sorting process of a relational database data sorting method according to an embodiment of the present application;

[0039] Figure 4 A schematic diagram of a sorting process of a relational database data sorting method according to an embodiment of the present application;

[0040] Figure 5This is an example of sorting file merging of the relational database data sorting method according to an embodiment of the present application. DETAILED DESCRIPTION

[0041] The exemplary embodiments of the present disclosure will be described in more detail below with reference to the accompanying drawings. Although the exemplary embodiments of the present disclosure are shown in the accompanying drawings, it should be understood that the present disclosure can be implemented in various forms and should not be limited by the embodiments set forth herein. On the contrary, these embodiments are provided to enable a more thorough understanding of the present disclosure and to fully convey the scope of the present disclosure to those skilled in the art.

[0042] The embodiment of the present invention provides a method for sorting data in a relational database, which is applied to a processing environment with multiple sorting processes, such as Figure 1 As shown, the following steps are included:

[0043] In step S101, based on the available memory size, each sorting process participating in the sorting is allocated a memory usage of the sorting process. For example, if the current available memory size is 400M and there are 4 sorting processes participating in the sorting, each sorting process can be allocated 100M of memory usage.

[0044] In step S102, the data file to be sorted is divided into multiple data blocks based on the memory used by the sorting process. Specifically, based on the above example, the size of the multiple data blocks divided from the data file to be sorted cannot exceed 100M, and the multiple data blocks divided in this example are 100M.

[0045] In step S103, the data file to be sorted is divided into at least two parts of data, each part of data includes at least two data blocks. In some embodiments, the data file to be sorted is divided into at least two parts of data, including: the number of partial data divided from the data file to be sorted is half the number of sorting processes. For example, in the case of four sorting processes, the data file to be sorted can be divided into two parts of data, specifically, the division can be performed from the middle of the data file to be sorted. For another example, in the case of six sorting processes, the data file to be sorted can be divided into three parts of data, and the specific division method is not limited one by one here.

[0046] In step S104, two sorting processes are used to read a data block from the portion of data for sorting, and the two sorting processes read the data blocks in opposite directions. Figure 2As shown, in this example, four sorting processes are used as an example. According to the above embodiment, the data file to be sorted is divided into several data blocks, and the size of each chunk is the memory size available for each sorting process calculated above. (For example, if each sorting process can use 100MB of memory, the size of each chunk can be 100MB). Then, each sorting process will be sorted according to the following steps: Figure 2 The chunks are read in the order shown, as follows Figure 2 There are two parts of data in the data. The first part of data is read and sorted by process 1 and process 2, and the second part of data is read and sorted by process 3 and process 4. The data reading directions of process 1 and process 2, and process 3 and process 4 are relative. The data reading directions are relative, which means that process 1 reads sequentially and process 2 reads in reverse order. For example, if the total number of chunks is 2j, process 1 reads chunk1 sequentially, while process 2 reads chunkj in reverse order, process 3 reads sequentially from chunkj+1, and process 4 reads in reverse order from chunk2j.

[0047] In this example, process 1 reads sequentially from chunk1, which means that process 1 reads from the first byte of chunk1 to the last byte of chunk1, and then process 1 continues to read chunk2. Process 2 reads in reverse order from chunkj, which means that process 2 reads from the last byte of chunkj to the first byte of chunkj, and then process 2 continues to read chunkj-1. This design can effectively avoid the data disorder problem caused by concurrent reading.

[0048] In step S105, the ordered files output by each sorting process are merged to obtain a sorted file.

[0049] The embodiment of the present invention changes the external sorting into parallel execution, divides it into several sorting processes, and executes the steps of reading->sorting->writing in parallel, which can greatly improve the sorting performance under the new hardware.

[0050] In some embodiments, reading a data block from the portion of data for sorting includes:

[0051] Configure a shared memory.

[0052] The shared memory is used to record the sorting duration of the corresponding data blocks executed by the two sorting processes;

[0053] Determine the relationship between the difference in sorting time of the two sorting processes and the preset time;

[0054] The reading order of the data blocks of the two sorting processes is adjusted based on the size relationship.

[0055] In some embodiments, the shared memory records the file numbers of the data blocks processed by each sorting process. Taking 4 sorting processes as an example, process 1 and process 2 do not process j / 2 chunks fixedly. The 4 sorting processes are configured with a shared memory area, and the chunk numbers currently processed by each process are synchronized in the shared memory. The sorting time of the corresponding data blocks executed by the two sorting processes is recorded in the shared memory, and the relationship between the difference in sorting time of the two sorting processes and the preset time is determined. In this way, the processing efficiency is optimized according to the speed of data processing by the sorting process, so that the process with fast reading and sorting speed can process more chunks.

[0056] In some embodiments, adjusting the reading order of the data blocks of the two sorting processes based on the size relationship includes: if the difference between the sorting time of the first sorting process and the sorting time of the second sorting process of the two sorting processes is greater than a preset time, changing the reading order of the next data block of the second sorting process.

[0057] Specifically, the arrangement of data in a database is often basically ordered, but the order is not fixed. For example, if the sequence 10, 11, 12, 13, 15, 17, 20 is sorted, it is obviously more efficient to process it in the forward order; for example, if the sequence 90, 80, 70, 50, 30, 20, 10 is sorted, it is more efficient to process it in the reverse order. The sorting referred to in the present invention generally refers to sorting from small to large, and it can also be other set orders.

[0058] In this example, process 1 reads chunk1 and sorts it, and process 2 reads chunkj and sorts it. After process 1 sorts chunk1, it records the time t1 consumed in the shared memory, and after process 2 sorts chunkj, it records the time t2 consumed in the shared memory.

[0059] At this time, by comparing t1 and t2, if t1-t2>s (s is a configurable preset duration), it means that the performance of reading and sorting chunks in reverse order is better; if t2-t1>s, it means that the performance of reading and sorting chunks in forward order is better.

[0060] like Figure 3 As shown, when t1-t2>s, process 1 starts from chunkj-1 and reads in reverse order.

[0061] When t2-t1>s, process 2 starts from chunk2 and reads in positive order.

[0062] And so on, such as Figure 4As shown, the sorting process reads chunks into memory and outputs them as physical files after sorting until all chunks are sorted and the process ends.

[0063] In some embodiments, merging the ordered files output by each sorting process includes:

[0064] Invoke the same number of merging processes as the number of sorting processes involved in sorting, and merge the ordered files output by the sorting processes through the merging processes to obtain sorted sub-files; in some embodiments, the merging processes and the sorting processes are run in parallel. Merge the sorted sub-files to obtain the sorted file.

[0065] Specifically, Figure 5 As shown in the figure, after the sorting of the aforementioned sorting process, several sorted chunk files are obtained. For example, if 10G data is sorted and each chunk is 100MB, a total of 100 sorted files will be generated. In order to maximize parallelism, the merging process will start merging processing synchronously during the output of these 100 files. Each merging process will merge the sorted files output by the corresponding sorting process until all the sorted files are merged to generate the final sorted file. The four processes will eventually generate 4 sorted files, and finally these 4 files need to be merged and sorted to obtain the final sorted file.

[0066] In summary, the external sorting algorithm in the database of this embodiment allows external sorting to be performed in parallel, which can give full play to the capabilities of current hardware and improve the efficiency of database sorting.

[0067] The embodiment of the present invention further provides a relational database data sorting device, comprising a processor, wherein the processor has multiple sorting processes, and the processor is configured to:

[0068] Based on the available memory size, allocate the memory used by the sorting process to each sorting process involved in the sorting;

[0069] Dividing the data file to be sorted into a plurality of data blocks based on the memory used by the sorting process; and

[0070] Dividing the data file to be sorted into at least two parts of data, each part of data including at least two data blocks;

[0071] Using two sorting processes, a data block is read from the portion of data for sorting, and the two sorting processes read the data blocks in opposite directions;

[0072] Merge the ordered files output by each sorting process to obtain a sorted file.

[0073] An embodiment of the present invention further provides a computer-readable storage medium, on which a computer program is stored. When the computer program is executed by a processor, the steps of the relational database data sorting method described in each embodiment of the present disclosure are implemented.

[0074] It should be noted that, in this article, the terms "include", "comprises" or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, an element defined by the sentence "comprises a ..." does not exclude the existence of other identical elements in the process, method, article or device including the element.

[0075] The serial numbers of the above embodiments of the present invention are only for description and do not represent the advantages or disadvantages of the embodiments.

[0076] Through the description of the above implementation methods, those skilled in the art can clearly understand that the above-mentioned embodiment methods can be implemented by means of software plus a necessary general hardware platform, and of course can also be implemented by hardware, but in many cases the former is a better implementation method. Based on such an understanding, the technical solution of the present invention, or the part that contributes to the prior art, can be embodied in the form of a software product, which is stored in a storage medium (such as ROM / RAM, a magnetic disk, or an optical disk), and includes a number of instructions for enabling a terminal (which can be a mobile phone, a computer, a server, an air conditioner, or a network device, etc.) to execute the methods described in each embodiment of the present invention.

[0077] The embodiments of the present invention are described above in conjunction with the accompanying drawings, but the present invention is not limited to the above-mentioned specific implementation modes, which are merely illustrative rather than restrictive. Under the guidance of the present invention, ordinary technicians in this field can also make many forms without departing from the scope of protection of the present invention and the claims, all of which are within the protection of the present invention.

Claims

1. A method for sorting data in a relational database, characterized in that: Applicable to processing environments with multiple sorting processes, including: Based on the available memory size, allocate the memory used by the sorting process to each sorting process involved in the sorting; Dividing the data file to be sorted into a plurality of data blocks based on the memory used by the sorting process; and Dividing the data file to be sorted into at least two parts of data, each part of data including at least two data blocks; Using two sorting processes, a data block is read from the portion of data for sorting, and the two sorting processes read the data blocks in opposite directions, wherein the first process reads from the first byte of the data block to the last byte of the data block, and the second process reads from the last byte of the data block to the first byte of the data block; Merge the ordered files output by each sorting process to obtain a sorted file.

2. The method for sorting relational database data according to claim 1, characterized in that: Reading a data block from the portion of data for sorting includes: Configure a shared memory; The shared memory is used to record the sorting duration of the corresponding data blocks executed by the two sorting processes; Determine the relationship between the difference in sorting time of the two sorting processes and the preset time; The reading order of the data blocks of the two sorting processes is adjusted based on the size relationship.

3. The method for sorting relational database data as claimed in claim 2, characterized in that: Adjusting the reading order of the data blocks of the two sorting processes based on the size relationship includes: If the difference between the sorting time of the first sorting process and the sorting time of the second sorting process is greater than a preset time, the reading order of the next data block of the first sorting process is changed; If the difference between the sorting time of the second sorting process and the sorting time of the first sorting process is greater than the preset time, the reading order of the next data block of the second sorting process is changed.

4. The method for sorting relational database data as claimed in claim 2, characterized in that: The shared memory records the file numbers of the data blocks processed by each sorting process.

5. The method for sorting relational database data according to claim 1, characterized in that: Merging the ordered files output by each sorting process includes: Calling the same number of merging processes as the number of the sorting processes involved in the sorting, merging the ordered files output by the sorting processes through the merging processes to obtain sorted sub-files; The sorted sub-files are merged to obtain the sorted file.

6. The method for sorting data in a relational database according to claim 5, characterized in that: The merge processes and sort processes run in parallel.

7. The method for sorting data in a relational database according to claim 1, characterized in that: Divide the data file to be sorted into at least two parts including: The number of partial data divided from the data file to be sorted is half the number of sorting processes.

8. A data sorting device for a relational database, characterized in that: A processor is included, the processor having a plurality of sorting processes, the processor being configured to: Based on the available memory size, allocate the memory used by the sorting process to each sorting process involved in the sorting; Divide the data file to be sorted into multiple data blocks based on the memory used by the sorting process; as well as Dividing the data file to be sorted into at least two parts of data, each part of data including at least two data blocks; Using two sorting processes, a data block is read from the portion of data for sorting, and the two sorting processes read the data blocks in opposite directions, wherein the first process reads from the first byte of the data block to the last byte of the data block, and the second process reads from the last byte of the data block to the first byte of the data block; Merge the ordered files output by each sorting process to obtain a sorted file.

9. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the steps of the relational database data sorting method according to any one of claims 1 to 7 are implemented.

Images