Database external sorting method, system and equipment

Abstract

The invention relates to the technical field of database management, and provides a database external sorting method, system and equipment, and the method comprises the steps: segmenting original to-be-sorted data into locally ordered data blocks in an external sorting segmentation stage; performing data format configuration on the locally ordered data blocks obtained by segmentation, and establishing a pre-cache space for the data blocks subjected to data format configuration; and in a merging stage of external sorting, locally ordered data blocks are merged in a pre-cache space through an optimized septicem tree algorithm in each round of merging process. According to the database external sorting method, system and equipment, the overall use efficiency of the cache and the disk can be remarkably improved, repeated I / O is reduced, the performance is improved, and the running time is shortened.

Description

Technical Field

[0001] The present invention relates to the technical field of database management, and in particular to a database external sorting method, system and device. Background Art

[0002] In the Sort row storage operator, when sorting large-scale data, if the amount of data exceeds the capacity of the working memory, an external sorting algorithm needs to be used. The core idea of ​​the external sorting algorithm is based on merge sort, and its execution process can be divided into the following two main stages. The first stage is the segmentation stage, in which the original data to be sorted is divided into multiple smaller data blocks, each of which is called a sequential string, that is, a locally ordered sequence. The second stage is the merging stage, in which all the segmented small sequential strings will be merged one by one, and finally a single ordered sequence containing all the data will be formed, that is, a large sequential string.

[0003] In practical applications, how to improve the overall performance of external sorting by optimizing the efficiency of each stage of external sorting is a technical problem that needs to be solved urgently. Summary of the invention

[0004] In view of this, in order to overcome the deficiencies of the prior art, the present invention aims to provide a database external sorting method, system and device.

[0005] According to a first aspect of the present invention, there is provided a database external sorting method, the method comprising: In the segmentation phase of external sorting, the original data to be sorted is segmented into locally ordered data blocks; Performing data format configuration on the locally ordered data blocks obtained by segmentation, and establishing a pre-cache space for the data blocks after the data format configuration; In the merging phase of external sorting, the locally ordered data blocks are merged in the pre-cache space through the optimized loser tree algorithm in each round of merging.

[0006] Preferably, in the database external sorting method of the present invention, dividing the original data to be sorted into locally ordered data blocks includes: dividing the original data to be sorted into ordered data blocks of uniform length by a quick sort or radix sort algorithm.

[0007] Preferably, in the database external sorting method of the present invention, data format configuration is performed on the locally ordered data blocks obtained by segmentation, including: performing data format configuration on the locally ordered data blocks by reducing the locally ordered data blocks obtained by segmentation.

[0008] Preferably, in the database external sorting method of the present invention, data format configuration is performed on the locally ordered data blocks by performing data compression or data header reduction on the locally ordered data blocks.

[0009] Preferably, in the database external sorting method of the present invention, a pre-cache space is established for the data blocks configured with the data format, including: using an input tape drive and an output tape drive as a cache space for the data blocks configured with the data format, pre-reading each input tape drive and output tape drive in sequence, establishing a merged data structure for the pre-read input tape drive and output tape drive and allocating memory.

[0010] Preferably, in the database external sorting method of the present invention, a pre-cache space is established for the data blocks configured with the data format, and it also includes: setting an equal number of input tape drives and output tape drives for the data blocks configured with the data format, and the number of input tape drives and output tape drives is equal to the number of data blocks configured with the data format.

[0011] Preferably, in the database external sorting method of the present invention, in each round of merging process, locally ordered data blocks are merged in the pre-cache space by using an optimized loser tree algorithm, including: Input the first record of each locally ordered data block as the leaf node of the loser tree and initialize the loser tree; Compare leaf nodes with each other, store leaf nodes with larger values ​​in parent nodes, use leaf nodes with smaller values ​​to continue to participate in mutual comparison, and place leaf nodes with the smallest value at the top of the loser tree; When the leaf node at the top of the loser tree is output to the pre-cache space, the next record is obtained from the corresponding locally ordered data block as a leaf node and placed at the top of the loser tree. The leaf nodes at the top are compared with each other along the parent node path, and the leaf node with the smallest value is placed at the top of the loser tree.

[0012] Preferably, in the database external sorting method of the present invention, in each round of merging process, the locally ordered data blocks are merged in the pre-cache space through the optimized loser tree algorithm, including: when the leaf node corresponding to the last record of the locally ordered data block is output to the pre-cache space, a loser tree with a smaller structure is reconstructed, and the new locally ordered data blocks are merged and output through the reconstructed loser tree.

[0013] According to a second aspect of the present invention, there is provided a database external sorting system, the system comprising an external sorting server, the external sorting server being used to: in the segmentation phase of the external sorting, segment the original data to be sorted into locally ordered data blocks; perform data format configuration on the locally ordered data blocks obtained by segmentation, and establish a pre-cache space for the data blocks that have undergone data format configuration; in the merging phase of the external sorting, merge the locally ordered data blocks in the pre-cache space by using an optimized loser tree algorithm in each round of merging.

[0014] According to a third aspect of the present invention, a computer device is provided, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the method described in the first aspect of the present invention when executing the program.

[0015] The present invention, through the database external sorting method, system and device, has the following beneficial technical effects: 1. By using the quick sort or radix sort algorithm in the segmentation phase and combining it with a compact data structure, the overall efficiency of cache and disk usage can be significantly improved; 2. Use a multi-way balanced merge algorithm in the merge phase to establish a read-ahead and write-ahead buffer to reduce duplicate I / O and improve performance. 3. By optimizing and using the loser tree algorithm in each round of merging, the number of comparisons in the merging phase is reduced by nearly half, further shortening the running time. BRIEF DESCRIPTION OF THE DRAWINGS

[0016] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings required for use in the embodiments will be briefly introduced below. Obviously, the drawings described below are only 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.

[0017] Figure 1 This is a diagram showing an example of the architecture of a database external sorting system according to an embodiment of the present invention; Figure 2 A flowchart of a method for external sorting of a database according to an embodiment of the present invention; Figure 3 A schematic diagram of the structure of the device provided by the present invention. DETAILED DESCRIPTION

[0018] The embodiments of the present invention are described in detail below with reference to the accompanying drawings.

[0019] It should be noted that the following embodiments and features in the embodiments may be combined with each other in the absence of conflict; and, based on the embodiments in the present disclosure, all other embodiments obtained by ordinary technicians in the field without making any creative work are within the scope of protection of the present disclosure.

[0020] It should be noted that various aspects of the embodiments within the scope of the appended claims are described below. It should be apparent that the aspects described herein may be embodied in a wide variety of forms, and any specific structure and / or function described herein is merely illustrative. Based on the present disclosure, it should be understood by those skilled in the art that an aspect described herein may be implemented independently of any other aspect, and two or more of these aspects may be combined in various ways. For example, any number of aspects described herein may be used to implement the device and / or practice the method. In addition, other structures and / or functionalities other than one or more of the aspects described herein may be used to implement this device and / or practice this method.

[0021] Figure 1 FIG. 4 is an example diagram of an architecture of an external sorting system for a database according to an embodiment of the present invention. Figure 1 As shown, the system may include an external sorting server 101, a communication network 102 and / or one or more external sorting clients 103. Figure 1 The example shown is a plurality of external sorting clients 103 .

[0022] The external sorting server 101 can be any suitable server for storing information, data, programs and / or any other suitable type of content. In some embodiments, the external sorting server 101 can perform appropriate functions. For example, in some embodiments, the external sorting server 101 can be used for external sorting of a database. As an optional example, in some embodiments, the external sorting server 101 can be used to implement external sorting of a database by optimizing the loser tree algorithm. For example, the external sorting server 101 can be used to: in the segmentation stage of external sorting, segment the original data to be sorted into locally ordered data blocks; perform data format configuration on the locally ordered data blocks obtained by segmentation, and establish a pre-cache space for the data blocks that have undergone data format configuration; in the merging stage of external sorting, merge the locally ordered data blocks in the pre-cache space by using the optimized loser tree algorithm in each round of merging.

[0023] As another example, in some embodiments, the external sorting server 101 may send the database external sorting method to the external sorting client 103 for the user to use according to the request of the external sorting client 103 .

[0024] As an optional example, in some embodiments, the external sorting client 103 is used to provide a visual external sorting interface, which is used to receive a user's selection input operation for performing external sorting of the database, and, in response to the selection input operation, obtain an external sorting interface corresponding to the option selected by the selection input operation from the external sorting server 101 and display the external sorting interface, wherein the external sorting interface at least displays information for performing external sorting of the database and operation options for the information for performing external sorting of the database.

[0025] In some embodiments, the communication network 102 can be any suitable combination of one or more wired and / or wireless networks. For example, the communication network 102 can include any one or more of the following: the Internet, an intranet, a wide area network (WAN), a local area network (LAN), a wireless network, a digital subscriber line (DSL) network, a frame relay network, an asynchronous transfer mode (ATM) network, a virtual private network (VPN), and / or any other suitable communication network. The external sorting client 103 can be connected to the communication network 102 via one or more communication links (e.g., communication link 104), and the communication network 102 can be linked to the external sorting server 101 via one or more communication links (e.g., communication link 105). The communication link can be any communication link suitable for transmitting data between the external sorting client 103 and the external sorting server 101, such as a network link, a dial-up link, a wireless link, a hardwired link, any other suitable communication link, or any suitable combination of such links.

[0026] The external sorting client 103 may include any one or more clients that present an interface related to performing external sorting of a database in an appropriate form for use and operation by a user. In some embodiments, the external sorting client 103 may include any suitable type of device. For example, in some embodiments, the external sorting client 103 may include a mobile device, a tablet computer, a laptop computer, a desktop computer, and / or any other suitable type of client device.

[0027] Although the external ordering service 101 is illustrated as one device, in some embodiments, any suitable number of devices may be used to perform the functions performed by the external ordering service 101. For example, in some embodiments, multiple devices may be used to implement the functions performed by the external ordering service 101. Alternatively, the functions of the external ordering service 101 may be implemented using a cloud service.

[0028] Based on the above system, an embodiment of the present invention provides a method for external sorting of a database, which is described below through the following embodiments.

[0029] Figure 2The flowchart of a method for external sorting of a database according to an embodiment of the present invention is shown in FIG. The method for external sorting of a database according to this embodiment can be executed at an external sorting server, such as Figure 2 As shown, the database external sorting method includes the following steps: Step S201: In the segmentation phase of external sorting, the original data to be sorted is segmented into locally ordered data blocks.

[0030] In the prior art, external sorting uses a replacement selection algorithm in the segmentation stage. The advantage of the replacement selection algorithm is that the length of the output string can be greater than the memory limit, generally up to twice. However, this heap-based algorithm has a performance bottleneck when sorting in memory, and the length of the generated string, that is, the data block, is uneven.

[0031] As an optional example, in this embodiment, the original data to be sorted is divided into ordered data blocks of uniform length by a quick sort or radix sort algorithm. Although the length of the data block sequence that can be sorted in each round becomes smaller, a more efficient single-round sorting performance is obtained, and overall evaluation shows that this method is more cost-effective.

[0032] Step S202: performing data format configuration on the locally ordered data blocks obtained by segmentation, and establishing a pre-cache space for the data blocks after the data format configuration.

[0033] This embodiment configures a more compact data format for the locally ordered data blocks obtained by segmentation, reduces the storage space of each row of data by reducing the data header, compressing the data, etc., and can reduce the space occupancy by several times, especially for fixed-length data. By reducing the cache and disk space required for the overall sorted data, better performance in reading and writing can be obtained.

[0034] As an optional example, this embodiment configures the data format of the locally ordered data blocks obtained by reducing the segmentation. For example, the data format of the locally ordered data blocks is configured by compressing the data or reducing the data header of the locally ordered data blocks.

[0035] In the merging stage, this embodiment uses a multi-way balanced merging algorithm to merge the data. Compared with the traditional multi-phase merging algorithm, this method is more efficient. In the early days, due to the high cost of tape drives, the multi-phase merging algorithm can ensure that all tape drives are fully utilized, thereby reducing overhead. However, in a modern hardware environment, the advantages of the balanced merging algorithm are more obvious. Considering that the cost of a single tape drive is not significant compared to a small amount of Kb memory buffer, more tape drives can be deployed. If the number of tape drives can match the number of serials, then repeated I / O operations can be completely eliminated, thereby improving overall performance.

[0036] As an optional example, after the data format is configured for the locally ordered data blocks, the present embodiment uses the input tape drive and the output tape drive as the cache space for the data blocks that have been configured for the data format, reads each input tape drive and the output tape drive in sequence in advance, establishes a merged data structure for the pre-read input tape drive and the output tape drive, and allocates memory. It should be noted that the present embodiment sets an equal number of input tape drives and output tape drives for the data blocks that have been configured for the data format, and the number of input tape drives and output tape drives is equal to the number of data blocks that have been configured for the data format.

[0037] For example, in the merging phase, this embodiment provides memory buffer space for each input and output tape drive to improve read and write performance. Each round of merging in the balanced merging algorithm reads data from M input tapes and writes data to N output tapes. To avoid a large number of disk seek waits during the merging process, this embodiment improves the situation by pre-reading each tape in sequence. In addition to establishing the data structure required for merging, this embodiment allocates all available memory to the pre-read and pre-write buffers.

[0038] Step S203: In the external sorting merging stage, in each round of merging, locally ordered data blocks are merged in the pre-cache space by using an optimized loser tree algorithm.

[0039] As an optional example, this embodiment inputs the first record of each locally ordered data block as a leaf node of a loser tree, initializes the loser tree; compares the leaf nodes with each other, stores the leaf nodes with larger values ​​in the parent node, uses the leaf nodes with smaller values ​​to continue to participate in the comparison, and places the leaf node with the smallest value at the top of the loser tree; when the leaf node at the top of the loser tree is output to the pre-cache space, obtains the next record from the corresponding locally ordered data block as a leaf node and places it at the top of the loser tree, compares the leaf nodes at the top along the parent node path, and places the leaf node with the smallest value at the top of the loser tree. When the leaf node corresponding to the last record of the locally ordered data block is output to the pre-cache space, a loser tree with a smaller structure is reconstructed, and the new locally ordered data block is merged and output through the reconstructed loser tree.

[0040] Compared with heap sort, the biggest advantage of loser tree is that it reduces the number of comparisons. Assume that M is the number of input files and N is the length of the string or data block. Loser tree needs to perform log(M) comparisons to obtain each minimum element, and the running time of the output buffer is O(N*logM). In contrast, the heap requires approximately 2*log(M) comparisons each time, and the running time of the output buffer is O(2N*logM). Since external sorting is originally used to compare large batches of data when memory is insufficient, the value of N is usually large, so reducing the number of comparisons is very important to shorten the running time.

[0041] For example, this embodiment realizes data merging by optimizing the loser tree algorithm. First, the first record of each sequence is input as the leaf node of the loser tree to initialize the loser tree. Each leaf node is compared with each other, and the father node stores the loser (the larger value of the node) of the comparison between the two nodes; the winner (the smaller one) can participate in the competition at a higher level. In this way, the top of the tree is the champion (the smallest one) of the comparison. Adjust the loser tree. After the smallest one is output to the buffer, it is necessary to take out the next record from the corresponding sequence and replace it with the leaf node of the original smallest one. Next, it is only necessary to compare along the father node of the replaced node to the top. The comparison rule is to compare with the father node, and the winner can participate in the comparison of higher levels, all the way up to the root node. The loser stays at the current node. After the last record of a sequence is output to the buffer, the prior art is to mark the leaf node corresponding to the sequence or generate a maximum value for it, but this will cause performance loss of branch prediction or repeated comparison. This embodiment needs to remove this leaf node, rebuild a smaller loser tree, and continue to compare according to the above steps. After actual testing, this method will have less performance loss.

[0042] In practical applications, the database external sorting method and system of this embodiment have the following beneficial technical effects: 1. By using the quick sort or radix sort algorithm in the segmentation phase and combining it with a compact data structure, the overall efficiency of cache and disk usage can be significantly improved; 2. Use a multi-way balanced merge algorithm in the merge phase to establish a read-ahead and write-ahead buffer to reduce duplicate I / O and improve performance. 3. By optimizing and using the loser tree algorithm in each round of merging, the number of comparisons in the merging phase is reduced by nearly half, further shortening the running time.

[0043] The database external sorting method and system of this embodiment are applicable to various databases such as the openGauss database.

[0044] like Figure 3As shown, the present invention also provides a device, including a processor 310, a communication interface 320, a memory 330 for storing a computer program executable by the processor, and a communication bus 340. The processor 310, the communication interface 320, and the memory 330 communicate with each other through the communication bus 340. The processor 310 implements the above-mentioned database external sorting method by running the executable computer program.

[0045] Among them, the computer program in the memory 330 can be implemented in the form of a software functional unit and can be stored in a computer-readable storage medium when it is sold or used as an independent product. Based on this understanding, the technical solution of the present application can be essentially or partly embodied in the form of a software product that contributes to the prior art. The computer software product is stored in a storage medium, including several instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to perform all or part of the steps of the various embodiments of the present application. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), disk or optical disk, etc. Various media that can store program codes.

[0046] The system embodiments described above are merely illustrative, wherein the units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, i.e., they may be located in one place, or they may be distributed on multiple network units. Some or all of the modules may be selected based on actual needs to achieve the purpose of the solution of this embodiment. Those of ordinary skill in the art may understand and implement it without creative effort.

[0047] Through the description of the above implementation modes, those skilled in the art can clearly understand that each implementation mode can be implemented by means of software plus a necessary general hardware platform, or of course by hardware. Based on such an understanding, the above technical solution can essentially or in other words be embodied in the form of a software product that contributes to the prior art. The computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, a magnetic disk, an optical disk, etc., and includes a number of instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to execute the methods of each embodiment or some parts of the embodiment.

[0048] The above is only a specific embodiment of the present invention, but the protection scope of the present invention is not limited thereto. Any changes or substitutions that can be easily thought of by a person skilled in the art within the technical scope disclosed by the present invention should be included in the protection scope of the present invention. Therefore, the protection scope of the present invention shall be subject to the protection scope of the claims.

Claims

1. A database external sorting method, characterized in that: The method comprises: In the segmentation phase of external sorting, the original data to be sorted is segmented into locally ordered data blocks; Performing data format configuration on the locally ordered data blocks obtained by segmentation, and establishing a pre-cache space for the data blocks after the data format configuration; In the merging phase of external sorting, the locally ordered data blocks are merged in the pre-cache space through the optimized loser tree algorithm in each round of merging.

2. The database external sorting method according to claim 1, characterized in that: Splitting the original data to be sorted into locally ordered data blocks includes: splitting the original data to be sorted into ordered data blocks of uniform length by using a quick sort or a radix sort algorithm.

3. The database external sorting method according to claim 1, characterized in that: The data format of the locally ordered data blocks obtained by segmentation is configured, including: by reducing the locally ordered data blocks obtained by segmentation, the data format of the locally ordered data blocks is configured.

4. The database external sorting method according to claim 3, characterized in that: By performing data compression or data header reduction on the locally ordered data blocks, data format configuration is performed on the locally ordered data blocks.

5. The database external sorting method according to claim 1, characterized in that: Establishing a pre-cache space for data blocks that have been configured with a data format, including: using an input tape drive and an output tape drive as a cache space for data blocks that have been configured with a data format, pre-reading each input tape drive and output tape drive in sequence, establishing a merged data structure for the pre-read input tape drive and output tape drive, and allocating memory.

6. The database external sorting method according to claim 5, characterized in that: Establishing pre-cache space for data blocks configured with data formats also includes: setting an equal number of input tape drives and output tape drives for the data blocks configured with data formats, and the number of input tape drives and output tape drives is equal to the number of data blocks configured with data formats.

7. The database external sorting method according to claim 1, characterized in that: In each round of merging, the locally ordered data blocks are merged in the pre-cache space through the optimized loser tree algorithm, including: Input the first record of each locally ordered data block as the leaf node of the loser tree and initialize the loser tree; Compare leaf nodes with each other, store leaf nodes with larger values ​​in parent nodes, use leaf nodes with smaller values ​​to continue to participate in mutual comparison, and place leaf nodes with the smallest value at the top of the loser tree; When the leaf node at the top of the loser tree is output to the pre-cache space, the next record is obtained from the corresponding locally ordered data block as a leaf node and placed at the top of the loser tree. The leaf nodes at the top are compared with each other along the parent node path, and the leaf node with the smallest value is placed at the top of the loser tree.

8. The database external sorting method according to claim 7, characterized in that: In each round of merging, the locally ordered data blocks are merged in the pre-cache space through the optimized loser tree algorithm, including: when the leaf node corresponding to the last record of the locally ordered data block is output to the pre-cache space, a loser tree with a smaller structure is rebuilt, and the new locally ordered data blocks are merged and output through the rebuilt loser tree.

9. A database external sorting system, characterized in that: The system includes an external sorting server, which is used to: in the segmentation stage of external sorting, segment the original data to be sorted into locally ordered data blocks; perform data format configuration on the locally ordered data blocks obtained by segmentation, and establish a pre-cache space for the data blocks that have undergone data format configuration; in the merging stage of external sorting, merge the locally ordered data blocks in the pre-cache space through an optimized loser tree algorithm in each round of merging.

10. A computer device, characterized in that: The computer device comprises a memory, a processor and a computer program stored in the memory and executable on the processor, and the processor implements the steps of the method according to any one of claims 1 to 8 when executing the program.

Images