Method, system and equipment for creating indexes in parallel based on sampling sorting

Abstract

The invention relates to the technical field of database index creation, and provides a method, a system and equipment for parallel creation of indexes based on sampling sequencing, and the method comprises the steps: obtaining a current parallelism degree through a main thread, and calculating the number of sampling boundary values according to the obtained current parallelism degree and a configuration coefficient; collecting the number of data blocks of a user through a main thread, and constructing a sampling data block list according to the number of the sampling boundary values and the number of the data blocks of the user; performing reading and index key extraction on the data blocks in the sampling data block list through a main thread to obtain corresponding boundary values; and configuring a scanning range for the corresponding number of sub-threads through the main thread according to the boundary value, and generating an index according to a scanning result of the sub-threads. According to the method, the system and the equipment for creating the indexes in parallel based on the sampling sorting, the efficiency of creating the indexes in parallel can be improved, and the expandability is enhanced.

Description

Technical Field

[0001] The present invention relates to the technical field of database index creation, and in particular to a method, system and device for creating indexes in parallel based on sampling sorting. Background Art

[0002] The openGauss database uses the idea of ​​a multi-way merge algorithm to create a btree index in parallel, which mainly includes two steps: Step 1: Scan and sort the data in parallel to generate multi-way sorted segments; Step 2: Merge the multi-way sorted segments to generate index data and write them into the index table. In actual applications, as the degree of parallelism increases, the number of sorted segments generated in Step 1 will increase, and the time spent on multi-way merging in Step 2 will also increase, exceeding the performance improvement brought by parallelism, and even causing performance degradation.

[0003] Therefore, how to improve the efficiency and performance of btree index creation has become 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 method, system and device for creating indexes in parallel based on sampling sorting.

[0005] According to a first aspect of the present invention, a method for creating an index in parallel based on sampling sorting is provided, the method comprising: The current parallelism is obtained through the main thread, and the number of sampling boundary values ​​is calculated according to the obtained current parallelism and configuration coefficient; The number of data blocks of the user is collected through the main thread, and a sampling data block list is constructed according to the number of sampling boundary values ​​and the number of data blocks of the user; The main thread reads the data blocks in the sampled data block list and extracts the index key to obtain the corresponding boundary value; The main thread configures the scanning range for a corresponding number of sub-threads according to the boundary value, and generates an index according to the scanning results of the sub-threads.

[0006] Preferably, in the method of creating an index in parallel based on sampling sorting of the present invention, the number of sampling boundary values ​​is calculated according to the obtained current parallelism and configuration coefficient, including: obtaining the difference between the current parallelism and 1, taking the difference between the current parallelism and 1 as the first parameter, and taking the product of multiplying the first parameter and the configuration coefficient of the boundary value number as the number of sampling boundary values.

[0007] Preferably, in the method of creating an index in parallel based on sampling sorting of the present invention, a sampling data block list is constructed according to the number of sampling boundary values ​​and the number of data blocks of the user, including: determining the sampled data block positions according to the number of sampling boundary values, constructing a sampling list according to the sampled data block positions, and constructing a sampling data block list by adding the data blocks corresponding to the sampled data block positions to the sampling list.

[0008] Preferably, in the method for creating an index based on sampling sorting and parallelism of the present invention, the data blocks in the sampled data block list are read and the index keys are extracted by the main thread to obtain the corresponding boundary values, including: reading the data blocks from the sampled data block list by the main thread, sorting the read data blocks according to the index keys, taking the ratio of the number of read data blocks to the current parallelism as the second parameter value, obtaining a data block from the sorted data blocks every second parameter value, extracting the index keys of the obtained data blocks, and taking the extracted index keys as the corresponding boundary values.

[0009] Preferably, in the method of creating an index in parallel based on sampling sorting of the present invention, a main thread configures a scanning range for a corresponding number of sub-threads according to a boundary value, including: the main thread starts a corresponding number of sub-threads according to the current parallelism, and configures a scanning range for each sub-thread according to the boundary value.

[0010] Preferably, in the method for creating an index in parallel based on sampling sorting of the present invention, generating an index according to the scanning result of the child thread includes: The child thread scans the data blocks according to the scanning range, and generates sorted segments by sorting the scanned data blocks; After the main thread detects that all child threads have finished scanning, it reads the sorted segments of each child thread according to the order of the child thread scanning range to generate an index.

[0011] Preferably, in the method for creating an index based on sampling sorting in parallel of the present invention, a sub-thread scans data blocks according to a scanning range, and generates a sorted segment by sorting the scanned data blocks, including: the sub-thread scans all data blocks in a staggered manner, filters the scanned data blocks according to the corresponding scanning range, and generates a sorted segment by sorting the filtered data blocks.

[0012] Preferably, in the method of creating indexes in parallel based on sampling sorting of the present invention, the sub-threads scan all data blocks in a staggered manner, including: each sub-thread staggered scans different data blocks, and when a sub-thread scans a data block, the scan result is cached, and when other sub-threads scan the data block, the scan result is obtained by reading the corresponding cache.

[0013] According to a second aspect of the present invention, a system for creating an index based on sampling sorting and in parallel is provided, the system comprising an index creation server, the index creation server being used to: obtain the current degree of parallelism through a main thread, and calculate the number of sampling boundary values ​​according to the obtained current degree of parallelism and a configuration coefficient; collect the number of data blocks of the user through the main thread, and construct a sampling data block list according to the number of sampling boundary values ​​and the number of data blocks of the user; read and extract index keys from the data blocks in the sampling data block list through the main thread to obtain corresponding boundary values; configure a scanning range for a corresponding number of sub-threads according to the boundary values ​​through the main thread, and generate an index according to the scanning results of the sub-threads.

[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 method, system and device for creating indexes in parallel based on sampling sorting of the present invention have the following beneficial technical effects: 1. Improve the efficiency of creating indexes in parallel: In engineering practice, data distribution is often random. The method and system of this embodiment can effectively divide the data range according to the degree of parallelism, ensuring that each thread undertakes a relatively balanced processing task. In the final stage, only the output results of each thread need to be read sequentially, thereby avoiding the performance degradation that may be caused by merge sorting.

[0016] 2. Enhanced scalability: In view of the diversity of actual business data distribution, the method and system of this embodiment allow definition of multiple sampling methods according to different business scenarios to adapt to a wider range of business needs. BRIEF DESCRIPTION OF THE DRAWINGS

[0017] 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.

[0018] Figure 1 This is an example diagram of the architecture of a system for creating indexes in parallel based on sampling sorting according to an embodiment of the present invention; Figure 2 A flowchart of a method for creating indexes in parallel based on sampling sorting according to an embodiment of the present invention; Figure 3 An example diagram of data sampling based on a method for creating indexes in parallel based on sampling sorting according to an embodiment of the present invention; Figure 4A schematic diagram of the structure of the device provided by the present invention. DETAILED DESCRIPTION

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

[0020] 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.

[0021] 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.

[0022] The technical principles of the present invention are as follows: The sampling sorting algorithm first samples a small amount of data to obtain a set of ordered boundary values, and determines in advance the data range to be processed by each sub-thread based on the boundary values; each sub-thread only needs to read and process the data in the corresponding range to generate a sorted segment of the data in the range; at this time, it is only necessary to read the data of each sorted segment in the order of the boundary values ​​to obtain an ordered final data, avoiding the need to do a merge sort at the end; by eliminating the last merge sort, it is possible to achieve an almost linear improvement in the effect of index creation by increasing the degree of parallelism of parallel index creation, thus solving the problem of performance degradation after the degree of parallelism caused by the merge sorting algorithm is increased.

[0023] Figure 1 FIG. 1 is an exemplary diagram of the architecture of a system for creating indexes in parallel based on sampling sorting according to an embodiment of the present invention. Figure 1 As shown, the system may include an index creation server 101, a communication network 102 and / or one or more index creation clients 103. Figure 1 The example in FIG. 1 is a client 103 for creating multiple indexes.

[0024] The index creation server 101 can be any appropriate server for storing information, data, programs and / or any other suitable type of content. In some embodiments, the index creation server 101 can perform appropriate functions. For example, in some embodiments, the index creation server 101 can be used to create indexes in parallel based on sampling sorting. As an optional example, in some embodiments, the index creation server 101 can be used to: obtain the current parallelism through the main thread, and calculate the number of sampling boundary values ​​based on the obtained current parallelism and the configuration coefficient; collect the number of data blocks of the user through the main thread, and build a sampling data block list based on the number of sampling boundary values ​​and the number of data blocks of the user; read the data blocks in the sampling data block list and extract the index key through the main thread to obtain the corresponding boundary value; configure the scanning range for the corresponding number of sub-threads according to the boundary value through the main thread, and generate an index based on the scanning results of the sub-threads.

[0025] As another example, in some embodiments, the index creation server 101 may send the method for creating an index in parallel based on sampling sorting to the index creation client 103 for the user to use according to the request of the index creation client 103 .

[0026] As an optional example, in some embodiments, the index creation client 103 is used to provide a visual index creation interface, which is used to receive a user's selection input operation for creating an index in parallel based on sampling sorting, and, in response to the selection input operation, obtain an index creation interface corresponding to the option selected by the selection input operation from the index creation server 101 and display the index creation interface, wherein the index creation interface at least displays information on creating an index in parallel based on sampling sorting and operation options for the information on creating an index in parallel based on sampling sorting.

[0027] 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 index creation 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 index creation 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 index creation client 103 and the index creation server 101, such as a network link, a dial-up link, a wireless link, a hard-wired link, any other suitable communication link, or any suitable combination of such links.

[0028] The index creation client 103 may include any one or more clients that present an interface related to creating an index in parallel based on sampling sorting in an appropriate form for use and operation by a user. In some embodiments, the index creation client 103 may include any suitable type of device. For example, in some embodiments, the index creation 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.

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

[0030] Based on the above system, an embodiment of the present invention provides a method for creating indexes in parallel based on sampling sorting, which is described below through the following embodiments.

[0031] Figure 2 1 is a flowchart of a method for creating an index in parallel based on sampling sorting according to an embodiment of the present invention. The method for creating an index in parallel based on sampling sorting according to this embodiment can be executed at the index creation server, such as Figure 2 As shown, the method for creating indexes in parallel based on sampling sorting includes the following steps: Step S201: obtaining the current parallelism through the main thread, and calculating the number of sampling boundary values ​​according to the obtained current parallelism and the configuration coefficient.

[0032] As an optional example, in this embodiment, the difference between the current parallelism and 1 is calculated, the difference between the current parallelism and 1 is used as the first parameter, and the product of the first parameter and the boundary value number configuration coefficient is used as the number of sampling boundary values. For example, in this embodiment, the main thread calculates the number of sampled boundary values ​​according to the following method: S = (dop-1) * M; M>=1, which is a boundary value number configuration coefficient. In actual applications, the larger the value, the more boundary values ​​are sampled, the higher the sampling accuracy, but the sampling time will increase; otherwise, it will lead to reduced sampling accuracy, but reduce sampling time. Those skilled in the art can select a reasonable M according to the actual application scenario when implementing the solution of the present invention.

[0033] Step S202: The number of data blocks of the user is collected through the main thread, and a sampling data block list is constructed according to the number of sampling boundary values ​​and the number of data blocks of the user.

[0034] In this embodiment, the data block list for calculating the boundary value, i.e., the sampling data block list, should cover as many parts of the user data as possible. In this way, no matter whether the user data is randomly distributed or has local inclination, sufficient user data samples can be obtained so that the calculated boundary value can divide the user data into parts with similar data volume.

[0035] As an optional example, in this embodiment, the sampled data block positions are determined according to the number of sampling boundary values, a sampling list is constructed according to the sampled data block positions, and a sampled data block list is constructed by adding data blocks corresponding to the sampled data block positions to the sampling list.

[0036] For example, assuming that the number of data blocks is N, starting from the first data block, every N / dop data blocks, dop is the number of current parallelism, obtain a data block and add it to the list of data blocks to be sampled, including the last one; in this way, a data block is sampled in each part of the user data as a sample for calculating the boundary value.

[0037] Step S203: read the data blocks in the sampled data block list and extract the index key through the main thread to obtain the corresponding boundary value.

[0038] As an optional example, in this embodiment, data blocks are read from a sampled data block list through the main thread, the read data blocks are sorted according to the index key, the ratio of the number of read data blocks to the current parallelism is used as the second parameter value, a data block is obtained from the sorted data blocks every second parameter value, the index key is extracted from the obtained data blocks, and the extracted index key is used as the corresponding boundary value.

[0039] For example, suppose a total of S data are read out. After sorting, start from the first data and obtain one data every S / dop data, and obtain dop-1 data in total. Then, extract the index key in these data as the boundary value to divide the user data into dop parts.

[0040] Step S204: The main thread configures a scanning range for a corresponding number of sub-threads according to the boundary value, and generates an index according to the scanning results of the sub-threads.

[0041] As an optional example, in this embodiment, the main thread starts a corresponding number of sub-threads according to the current parallelism, and configures a scanning range for each sub-thread according to the boundary value.

[0042] Since the boundary values ​​are ordered, such as S(1), S(2), S(3)...S(dop-1), the data range to be scanned by each child thread is: Subthread 1: less than S1 Subthread 2: greater than or equal to S1, less than S2 Subthread 3: greater than or equal to S2, less than S4 And so on, until the last child thread: Child thread dop: greater than or equal to S (dop-1) The child thread scans the data blocks according to the scanning range, and generates a sorted segment by sorting the scanned data blocks. As an optional example, in this embodiment, the child threads scan all data blocks in a staggered manner, that is, each child thread staggers and scans different data blocks. After a child thread scans a data block, it caches the scanned result. When other child threads scan the data block, they obtain the scan result by reading the corresponding cache. The scanned data blocks are filtered according to the corresponding scanning range, and the sorted segment is generated by sorting the filtered data blocks.

[0043] After the main thread detects that all child threads have finished scanning, it reads the sorted segments of each child thread according to the order of the child thread scanning range to generate an index.

[0044] Figure 3 FIG. 1 is an example diagram of data sampling according to a method for creating indexes in parallel based on sampling sorting according to an embodiment of the present invention. Figure 3As shown in the figure, for the method and system for parallel index creation based on sampling sorting in this embodiment, first, a series of ordered boundary values S1, S2, S3, S4, etc. are obtained by sampling user data, satisfying S1 < S2 < S3 < S4 <... Then, based on these boundary values, the data is divided into dop parts B1, B2, B3, etc., where dop is the degree of parallelism, and each sub-thread is responsible for processing a part of the data. Since the boundary values are ordered, the divided ranges B1, B2, B3, etc. are also ordered. As long as the data within each range is ensured to be ordered, when accessing these ranges in sequence finally, the data will also remain ordered. During the process of sub-thread scanning, filtering, and sorting, each sub-thread performs a full-table scan, filters and sorts according to the data range it is responsible for, and generates sorted segments. To improve the scanning efficiency, we utilize the data caching feature, divide the data into multiple blocks, and the sub-threads scan different data blocks with offsets. The scanned data blocks will be cached, so that other sub-threads can directly obtain data from the cache when accessing, avoiding repeated disk reads and thus improving the scanning efficiency. Finally, during the index generation process, since the data ranges responsible for by the sub-threads are ordered, when each sub-thread completes its sorted segment, these sorted segments can be directly concatenated in the boundary order to form globally ordered data, avoiding the need for secondary sorting. The main thread only needs to read the sorted segments of each sub-thread in sequence and generate the index.

[0045] The method and system for parallel index creation based on sampling sorting in this embodiment have the following beneficial technical effects: 1. Improve the efficiency of parallel index creation: In engineering practice, the data distribution often shows randomness. The method and system in this embodiment can effectively divide the data into ranges according to the degree of parallelism, ensuring that each thread undertakes a relatively balanced processing task. In the final stage, only the output results of each thread need to be read sequentially, thus avoiding the performance degradation that may be caused by merge sorting.

[0046] 2. Enhance scalability: Given the diversity of the actual business data distribution, the method and system in this embodiment allow defining multiple sampling methods according to different business scenarios, and are applicable to index creation in multiple databases such as the openGauss database.

[0047] As Figure 4 shown in the figure, the present invention also provides a device, including a processor 310, a communication interface 320, a memory 330 for storing computer programs executable by the processor, and a communication bus 340. Among them, the processor 310, the communication interface 320, and the memory 330 complete mutual communication through the communication bus 340. The processor 310 realizes the above method for parallel index creation based on sampling sorting by running the executable computer program.

[0048] 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.

[0049] 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.

[0050] 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.

[0051] 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 method for creating an index in parallel based on sampling sorting, characterized in that: The method comprises: The current parallelism is obtained through the main thread, and the number of sampling boundary values ​​is calculated according to the obtained current parallelism and configuration coefficient; The number of user data blocks is collected through the main thread, and a sampling data block list is constructed according to the number of sampling boundary values ​​and the number of user data blocks; The main thread reads the data blocks in the sampled data block list and extracts the index key to obtain the corresponding boundary value; The main thread configures the scanning range for the corresponding number of sub-threads according to the boundary value, and generates an index according to the scanning results of the sub-threads.

2. The method for creating indexes in parallel based on sampling sorting according to claim 1, characterized in that: The number of sampling boundary values ​​is calculated based on the current parallelism and configuration coefficient, including: obtaining the difference between the current parallelism and 1, taking the difference between the current parallelism and 1 as the first parameter, and taking the product of the first parameter and the boundary value number configuration coefficient as the sampling boundary value number.

3. The method for creating indexes in parallel based on sampling sorting according to claim 1, characterized in that: A sampling data block list is constructed according to the number of sampling boundary values ​​and the number of data blocks of the user, including: determining the sampled data block position according to the number of sampling boundary values, constructing a sampling list according to the sampled data block position, and constructing a sampling data block list by adding the data block corresponding to the sampled data block position to the sampling list.

4. The method for creating indexes in parallel based on sampling sorting according to claim 1, characterized in that: The data blocks in the sampled data block list are read and the index keys are extracted by the main thread to obtain the corresponding boundary values, including: reading the data blocks from the sampled data block list by the main thread, sorting the read data blocks according to the index keys, taking the ratio of the number of read data blocks to the current parallelism as the second parameter value, obtaining a data block from the sorted data blocks every second parameter value, extracting the index keys of the obtained data blocks, and taking the extracted index keys as the corresponding boundary values.

5. The method for creating indexes in parallel based on sampling sorting according to claim 1, characterized in that: The main thread configures a scanning range for a corresponding number of sub-threads according to a boundary value, including: the main thread starts a corresponding number of sub-threads according to a current parallelism, and configures a scanning range for each sub-thread according to the boundary value.

6. The method for creating indexes in parallel based on sampling sorting according to claim 1, characterized in that: Generate an index based on the scan results of the child thread, including: The child thread scans the data blocks according to the scanning range, and generates sorted segments by sorting the scanned data blocks; After the main thread detects that all child threads have finished scanning, it reads the sorted segments of each child thread according to the order of the child thread scanning range to generate an index.

7. The method for creating indexes in parallel based on sampling sorting according to claim 6, characterized in that: The child thread scans data blocks according to the scanning range, and generates sorted segments by sorting the scanned data blocks, including: the child thread scans all data blocks in a staggered manner, filters the scanned data blocks according to the corresponding scanning range, and generates sorted segments by sorting the filtered data blocks.

8. The method for creating indexes in parallel based on sampling sorting according to claim 7, characterized in that: The sub-threads scan all data blocks in a staggered manner, including: each sub-thread staggered scans different data blocks, when a sub-thread scans a data block, the scan result is cached, when other sub-threads scan the data block, the scan result is obtained by reading the corresponding cache.

9. A system for creating indexes in parallel based on sampling sorting, characterized in that: The system includes an index creation server, which is used to: obtain the current parallelism through the main thread, and calculate the number of sampling boundary values ​​according to the obtained current parallelism and the configuration coefficient; collect the number of data blocks of the user through the main thread, and build a sampling data block list according to the number of sampling boundary values ​​and the number of data blocks of the user; read and extract index keys from the data blocks in the sampling data block list through the main thread to obtain corresponding boundary values; configure the scanning range for the corresponding number of sub-threads according to the boundary value through the main thread, and generate indexes according to the scanning results of the sub-threads.

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