Pipeline sorting method based on column storage

Abstract

The present invention relates to a pipeline sorting method based on column storage, and its technical characteristics are: including asynchronous IO thread, pipeline thread and sorting thread, which constructs CU sequence according to the meta information of all CUs, and constructs boundary sequence according to the minimum value information of the CU sequence. In the sorting process, CU is regarded as a data set. For two adjacent CUs, the minimum value of the latter CU is used as the boundary to divide the data into two parts. The former part can be sorted separately as a subset, and the latter part and the second CU form a new set, and then continue to perform the same processing with the latter CU to realize pipeline sorting. The present invention is reasonably designed, realizes the function of fast sorting of data sets that are stored in column and are approximately ordered, and has the characteristics of fast processing speed and small resource occupation.

Description

Technical Field

[0001] The invention belongs to the technical field of databases, and in particular to a pipeline sorting method based on column storage. Background Art

[0002] Data warehouses usually store massive amounts of raw data, allowing users to gain insights and decision-making guidance from the data through tools such as reports and dashboards. The fact table in a data warehouse refers to a table that stores a large amount of business measurement data, and is also the core table of a data warehouse. Usually, the most useful facts are numerical facts and additive facts, such as sales amounts, costs, etc. Usually, the data in a fact table is not allowed to be modified, and new data is simply added. The characteristics of a fact table are: a large amount of data, a small number of attribute columns, and continuous growth. In layman's terms, a fact table is a complete and detailed business table, and new data will continue to be added.

[0003] Figure 1 A typical fact table structure for recording commodity sales records is given. The records in the fact table are usually approximately ordered according to fields such as time and primary key, or have an ordered trend, such as Figure 1 Many operations provided by the data warehouse are also based on the sorting of these approximately ordered fields. Figure 1 Taking the commodity sales record fact table as an example, typical businesses include:

[0004] (1) Draw a sales curve for product numbered 1010. This requires sorting by sales time after retrieving and calculating the results for the product. The SQL statement is as follows:

[0005] SELECT sales time, real-time price*discount*sales quantity

[0006] FROM product sales records

[0007] WHERE Product Number = 1010

[0008] ORDER BY sales time;

[0009] (2) Generate a sales report for all products in hours. This requires a grouping operation. Because the grouping result set is very large, a sorting and aggregation grouping algorithm must be used. The SQL statement is as follows:

[0010] SELECT to_char(sales time,'yyyy-mm-dd,hh24'),sum(real-time price*discount*sales quantity)

[0011] FROM product sales records

[0012] GROUP BY to_char(sales time,'yyyy-mm-dd,hh24').

[0013] Since the data range processed by the data warehouse is usually very large, the amount of data to be sorted will also be very large, and may even exceed the physical memory. Therefore, the merge sort algorithm is usually used to sort the large-scale data set that exceeds the physical memory. Because merge sort requires frequent IO reading and writing, the sorting performance will be extremely slow. Even if the data set to be sorted can be completely loaded into the physical memory and a more efficient quick sort algorithm is used, it will still generate very large memory and computing overhead due to the huge data set. In addition, the sorting algorithm needs to use materialization to return the result after the complete sorting (the heap sort algorithm can return in advance, but it still needs to compare and judge all the data in advance), which is usually called a high startup cost in the data warehouse. Therefore, the seemingly simple drawing of curves and making reports will involve a lot of resource overhead.

[0014] The time and primary key fields in the data warehouse are approximately ordered. This is mainly because although the data is generated sequentially, the data stored is not completely ordered due to concurrent loading and re-recording of historical records. If we observe the sales time field in the fact table of commodity sales records, we will find a relatively small number of unordered records, usually called "abnormal" values, such as Figure 2 shown.

[0015] At present, data warehouses often use column storage to optimize statistical query performance. Figure 3 A schematic diagram of column storage of a commodity sales record table is given. Column storage is usually composed of multiple storage units called Compress Units (CUs). Each CU consists of metadata and compressed data. The metadata includes the statistical information of the CU unit, including the minimum value, maximum value, and number of records, and the compressed data contains the actual data. Data warehouses with column storage structures also have problems such as slow sorting speed and large resource usage. How to optimize the performance of sorting operations in column storage structures is an urgent problem that needs to be solved. Summary of the invention

[0016] The purpose of the present invention is to overcome the deficiencies of the prior art and to provide a pipeline sorting method based on column storage that has a reasonable design, fast processing speed and small resource occupation.

[0017] The present invention solves the existing technical problems by adopting the following technical solutions:

[0018] A pipeline sorting method based on column storage includes an asynchronous IO thread, a pipeline thread and a sorting thread, and is implemented according to the following steps:

[0019] Step 1: The sorting thread applies for two fixed-size first sorting buffer blocks and second sorting buffer blocks;

[0020] Step 2: The asynchronous IO thread reads the metadata of all CUs in the column storage in sequence and records the minimum and maximum values ​​of each CU;

[0021] Step 3: The pipelined thread sorts all CUs according to the minimum value of CU and constructs a CU sequence and a boundary sequence;

[0022] Step 4: The pipelined thread detects whether the CU sequence is empty. If the CU sequence is empty, jump to step 11; otherwise, obtain a CU sequence number and wake up the asynchronous IO thread.

[0023] Step 5: The pipelined thread obtains the boundary corresponding to the CU sequence number as a comparison base value. If the CU sequence number is the last CU in the CU sequence, the maximum value of the CU is used as the comparison base value.

[0024] Step 6: The pipelined thread reads the data of the compression unit CU from the compression unit CU and decompresses it;

[0025] Step 7: The pipelined thread compares the maximum value of CU with the comparison base value. If the maximum value of CU is less than the comparison base value, the data is written into the first sorting buffer block and the process goes to step 9. Otherwise, the process goes to step 8.

[0026] Step 8: The pipelined thread traverses the CU records of the CU sequence and compares them with the comparison base value. If the CU record is less than the comparison base value, the CU record is written into the first sorting buffer block; otherwise, the CU record is written into the second sorting buffer block.

[0027] Step 9: The pipeline thread processes all records of the CU. If there are other CUs with the same boundary value, jump to step 4. Otherwise, put the first sorting buffer block into the queue to be sorted and wake up the sorting thread.

[0028] Step 10: The pipelined thread renames the second sort buffer block to the first sort buffer block, re-applies for the second sort buffer block, and jumps to step 4;

[0029] Step 11: If the first sorting buffer block is not empty, put the first sorting buffer block into the queue to be sorted, waiting for the sorting thread to process;

[0030] Step 12: The sorting thread executes the sorting tasks in sequence until the pipeline sorting is completed.

[0031] Furthermore, the specific implementation method of step 3 includes the following steps:

[0032] ⑴ Sort all CUs according to the minimum value;

[0033] (2) Merge CUs with the same minimum value to construct a CU sequence;

[0034] ⑶ Determine the CU boundary value according to the minimum value of the CU sequence;

[0035] (4) According to the CU boundary value and using the associative law, a boundary sequence is constructed, which is a series of non-overlapping subsets.

[0036] The advantages and positive effects of the present invention are:

[0037] The present invention is reasonably designed, and divides the overall sorting of a large data set into a pipeline sorting method for a series of small data sets. Since pipeline sorting has the characteristics of low memory overhead and low response delay, and its space complexity and time complexity are comprehensively superior to ordinary sorting, even in the case of completely disordered data, pipeline sorting will automatically degenerate into ordinary sorting without incurring any additional overhead, thereby realizing the function of fast sorting of data sets that are stored in columnar form and are approximately ordered, and has the characteristics of fast processing speed and small resource occupation, etc. BRIEF DESCRIPTION OF THE DRAWINGS

[0038] Figure 1 It is a schematic diagram of the structure of an existing commodity sales record table;

[0039] Figure 2 It is a schematic diagram of the "abnormal" values ​​of existing commodity sales records;

[0040] Figure 3 It is a schematic diagram of the column storage structure of commodity sales records;

[0041] Figure 4 It is a schematic diagram of the relationship between the CU sequence and the boundary sequence of the present invention;

[0042] Figure 5 It is a schematic diagram of the pipeline sorting process of the present invention. DETAILED DESCRIPTION

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

[0044] The design concept of the present invention is: firstly, the meta information of all CUs of the sequence to be sorted is obtained and sorted according to the minimum value information (this embodiment takes ascending sorting as an example, and the descending principle is the same); since the data itself has an ordered trend as a whole, a "CU sequence" with the same ordered trend can be obtained. Then, a "boundary sequence" is constructed based on the minimum value information of the "CU sequence" and used as a comparison base value for dividing the data set during sorting.

[0045] The sorting process is a process of dividing CUs in sequence according to the boundaries, that is, in the sorting process, the CU is regarded as a data set. For two adjacent CUs, the minimum value of the latter CU is used as the boundary to divide the data into two parts. The first part can be sorted separately as a sub-set, and the second part and the second CU form a new set, and then continue the same processing with the following CU to achieve pipeline sorting. It should be noted that during processing, CUs with the same minimum value are first merged into a larger set, and then processed in the above manner. Figure 4 It is a schematic diagram of CU sequence and boundary sequence.

[0046] The following uses rigorous mathematical language to explain the principle of the sorting process. A represents the value range of the field to be sorted in the fact table. Assuming there are n CUs in total, then:

[0047] (1) Sort CUs by minimum value

[0048]

[0049] (2) Merge CUs with the same minimum value

[0050]

[0051] (3) Confirm the boundary value

[0052]

[0053] (4) Use the associative law to convert into a series of disjoint subsets

[0054]

[0055] After the above transformation, we finally get the following relationship:

[0056]

[0057] And, if given:

[0058] a∈(T i ∪S i+1 ),b∈(T i+1 ∪S i+2 )

[0059] Then, there must exist:

[0060] a

[0061] At this point, we have obtained a CU sequence and a boundary sequence, and then we can start pipeline sorting.

[0062] ​In order to realize multi-level pipelining, the present invention designs a total of three threads, namely, asynchronous IO thread, pipelining thread and sorting thread. The asynchronous IO thread is responsible for asynchronous reading of storage CU, the pipelining thread is responsible for constructing independent subsets according to CU sequence and boundary sequence, and the sorting thread is responsible for completing the final sorting. Figure 5 shown.

[0063] Based on the above description, the pipeline sorting method based on column storage of the present invention includes the following steps:

[0064] Step 1: The sorting thread pre-applies for two sorting buffer blocks of fixed size, which are recorded as the first sorting buffer block (SortBuf1) and the second sorting buffer block (SortBuf2).

[0065] Step 2: The asynchronous IO thread reads the metadata of all CUs in turn and records the minimum value (MIN value) and maximum value (MAX value) of each CU. Figure 5 501 of them.

[0066] Step 3: The pipelined thread sorts all CUs according to the minimum value (MIN value) of CU and constructs CU sequence and boundary sequence, see Figure 5 502 of them.

[0067] Step 4: The pipelined thread detects whether the CU sequence is empty. If the CU sequence is empty, jump to step 11. Otherwise, get a CU sequence number and wake up the asynchronous IO thread. Figure 5 503 of them.

[0068] Step 5: The pipelined thread obtains the boundary corresponding to the CU sequence number as a comparison base value. If the CU sequence number is the last CU, the maximum value of the CU is used as the comparison base value, such as time: 9999-12-31 23:59:59.

[0069] Step 6: The pipelined thread reads the data of the compression unit CU and decompresses it. Figure 5 504 in the.

[0070] Step 7: The pipelined thread compares the maximum value (MAX value) of CU with the comparison base value. If the maximum value (MAX value) of CU is less than the comparison base value, there is no need to compare each record. The data is directly written into SortBuf1 and the process goes to step 9. Otherwise, it goes to step 8. Figure 5 505 and 506 in it.

[0071] Step 8: The pipelined thread traverses the records of CU and compares them with the comparison base value. If the record is less than the comparison base value, it is written into SortBuf1; otherwise, it is written into SortBuf2.

[0072] Step 9: The pipelined thread processes all records of the CU. If there are other CUs with the same boundary value, jump to step 4. Otherwise, put SortBuf1 into the queue to be sorted and wake up the sorting thread.

[0073] Step 10: The pipelined thread renames SortBuf2 to SortBuf1, re-applies for SortBuf2, and jumps to step 4.

[0074] Step 11: If SortBuf1 is not empty, put SortBuf1 into the queue to be sorted and wait for the sorting thread to process it.

[0075] Step 12: The sorting threads execute the sorting tasks in sequence until the pipeline sorting is completed. Figure 5 507 of them.

[0076] It should be emphasized that the embodiments described in the present invention are illustrative rather than restrictive. Therefore, the present invention includes but is not limited to the embodiments described in the specific implementation manner. Any other implementation manners derived by those skilled in the art based on the technical solution of the present invention also fall within the scope of protection of the present invention.

Claims

1. A pipeline sorting method based on column storage, Features: It includes asynchronous IO thread, pipeline thread and sorting thread, and is implemented as follows: Step 1: The sorting thread applies for two fixed-size first sorting buffer blocks and second sorting buffer blocks; Step 2: The asynchronous IO thread reads the metadata of all CUs in the column storage in sequence and records the minimum and maximum values ​​of each CU; Step 3: The pipelined thread sorts all CUs according to the minimum value of CU and constructs a CU sequence and a boundary sequence; Step 4: The pipelined thread detects whether the CU sequence is empty. If the CU sequence is empty, jump to step 11; otherwise, obtain a CU sequence number and wake up the asynchronous IO thread. Step 5: The pipelined thread obtains the boundary corresponding to the CU sequence number as a comparison base value. If the CU sequence number is the last CU in the CU sequence, the maximum value of the CU is used as the comparison base value. Step 6: The pipelined thread reads the data of the compression unit CU from the compression unit CU and decompresses it; Step 7: The pipelined thread compares the maximum value of CU with the comparison base value. If the maximum value of CU is less than the comparison base value, the data is written into the first sorting buffer block and the process goes to step 9. Otherwise, the process goes to step 8. Step 8: The pipelined thread traverses the CU records of the CU sequence and compares them with the comparison base value. If the CU record is less than the comparison base value, the CU record is written into the first sorting buffer block; otherwise, the CU record is written into the second sorting buffer block. Step 9: The pipeline thread processes all records of the CU. If there are other CUs with the same boundary value, jump to step 4. Otherwise, put the first sorting buffer block into the queue to be sorted and wake up the sorting thread. Step 10: The pipelined thread renames the second sort buffer block to the first sort buffer block, re-applies for the second sort buffer block, and jumps to step 4; Step 11: If the first sorting buffer block is not empty, put the first sorting buffer block into the queue to be sorted, waiting for the sorting thread to process; Step 12: The sorting thread executes the sorting tasks in sequence until the pipeline sorting is completed; The specific implementation method of step 3 is: Assume that A represents the value range of the field to be sorted in the fact table. There are n CUs in total. Follow the steps below: (1) Sort CUs by minimum value In the above formula, X i represents the value range of the i-th CU, and min(X i )≤min(X i+1 ) (2) Merge CUs with the same minimum value (3) Confirm the boundary value AND i =S i ∪T i Among them, S i ={x|x∈Y i And x <min(Y i+1 )},T i =Y i -S i (4) Use the associative law to convert into a series of disjoint subsets After the above transformation, we finally get the following relationship: And, if given: a∈(T i ∪S i+1 ),b∈(T i+1 ∪S i+2 ) Then, there must exist: aAt this point, a CU sequence and a boundary sequence are obtained, and then pipeline sorting begins. ​

Images