An equivalent query method and database system based on grouping information

Abstract

The invention discloses an equal value query method based on grouping information and a database system, belonging to the technical field of databases. The database system adopts an equal value query method based on grouping information. The method firstly sorts and groups connection columns in a connection table. The sorting is to sort the data values ​​in front and back. The grouping is to divide the repeated value area based on the cardinality sorted data, and recursively call the data sorting and grouping method for the data in the area to finally obtain the sorted data and grouping results. Then, the sorting and grouping results of the connection columns in at least two connection tables are obtained, the data values ​​of the current group and the current connection column of the connection table are compared, and according to the comparison result of the data value size, the grouping is changed based on the direction of the numerical value sorting. If all the connection columns of the group are equal, the grouped data of the connection table are output as the query result.

Description

Technical Field

[0001] The invention belongs to the technical field of databases, and in particular relates to an equivalent query method based on grouping information and a database system. Background Art

[0002] In the multi-table query of the database system, the join query includes inner join, outer join, etc., among which the inner join is also called equal join, which matches each row of data in the two tables according to the standard of equality of certain columns. For example, each row of data in the left table tries to match all rows in the right table. If a row of the left table matches a row of the right table, all columns of the rows of the left and right tables are combined into a row and put into the result. Rows without matching will not be output. The columns used by equal join to match the rows of the left and right tables are called join columns.

[0003] At present, the methods for implementing equal join in databases are mainly divided into sort-merge join based on sorting and hash join based on hash table. Taking sort-merge join based on sorting as an example, the number of comparisons between the left and right table rows is large, and there are repeated comparisons. In the prior art, based on the merge sort algorithm, its time complexity is high. Summary of the invention

[0004] In order to solve the shortcomings of the prior art and achieve the purpose of reducing the sorting complexity and the number of data comparisons under large amounts of data, the present invention adopts the following technical solutions:

[0005] A method for equal value query based on grouping information, firstly, sorting and grouping the connection columns in a connection table, sorting is based on the size of data values, grouping is based on the division of repeated value areas of the data sorted by radix, and the data sorting and grouping method is recursively called on the data in the area, finally obtaining the sorted data and grouping results; then, obtaining the sorting and grouping results of the connection columns in at least two connection tables, comparing the data values ​​of the current group and the current connection column of the connection table, changing the grouping based on the direction of the numerical value sorting according to the comparison result of the data value size, and outputting the connection table grouping data as the query result if all the connection columns of the group are equal.

[0006] Furthermore, the sorting and grouping is to sort the data of the current connection column, and find out the repeated value area of ​​the current connection column through the sorted data to divide it into different groups. For the repeated value area, if the current connection column is not the last connection column, the data in the area is sorted and grouped. After the sorting and grouping are completed layer by layer, the next current connection column is sorted and grouped until the final sorting and grouping are completed, and the grouping results are merged.

[0007] Furthermore, a subscript is set for the current connection column, and the data in the region of the current connection column is sorted and grouped layer by layer based on the subscript. After completion, the subscript is moved back to sort and group the data in the region of the next current connection column layer by layer, and the grouping results are merged through the subscript.

[0008] Furthermore, after the current connection column is sorted, the starting position and length of all repeated value regions of the current connection column are marked; after all groupings are completed, the starting position and length of the region are appended to all grouping results.

[0009] Furthermore, in the left and right connection tables, the connection columns are sorted according to the size of their data values, increasing from left to right and from top to bottom;

[0010] Construct an outer loop to repeatedly execute the inner loop until there are no more groups in the current left table or right table, and output the query result set;

[0011] Construct an inner loop. If the data value of the current connection column of the current group of the left connection table is greater than the data value of the current connection column of the current group of the right connection table, the next group of the right connection table is used as the current group and the inner loop is stopped. If the data value of the current connection column of the current group of the left connection table is less than the data value of the current connection column of the current group of the right connection table, the next group of the left connection table is used as the current group and the inner loop is stopped. If the current groups of the left and right connection tables are equal in all connection columns, the current left and right connection tables are appended to the query result set, and the next groups of the left and right connection tables are used as the current group.

[0012] Furthermore, the subscripts of the current groups of the left and right connection tables are set and maintained; when the data value of the current connection column of the current group of the left connection table is greater than the data value of the current connection column of the current group of the right connection table, the subscript of the current group of the right connection table is moved backward; when the data value of the current connection column of the current group of the left connection table is less than the data value of the current connection column of the current group of the right connection table, the subscript of the current group of the left connection table is moved backward; when the current groups of the left and right connection tables are equal in all connection columns, the subscripts of the current groups of the left and right connection tables are all moved backward.

[0013] Furthermore, when all the connection columns of the current groups of the left and right join tables are equal, each row in the current group of the left join table and each row in the current group of the right join table are combined into a row of results and appended to the query result set.

[0014] A database system supports the equivalent query method based on grouping information to perform data query.

[0015] The advantages and beneficial effects of the present invention are:

[0016] The invention discloses an equal value query method based on grouping information and a database system. Based on the repeated value area of ​​the data, the data is sorted and grouped according to the connection column to reduce the complexity of sorting. The sort-merge join algorithm is improved by using the grouping information after sorting. When matching the left table and the right table pointers, the area with the same connection column value is skipped to avoid repeated comparison of the data in each group of the left table and the data in each group of the right table, so as to reduce the number of comparisons and improve the performance of the database system. BRIEF DESCRIPTION OF THE DRAWINGS

[0017] Figure 1 This is a flow chart of the existing sort-merge join method.

[0018] Figure 2 It is a flow chart for merging and sorting data according to the connection column.

[0019] Figure 3 It is a flow chart of sorting and grouping data according to connection columns by using the cardinality sorting concept in an embodiment of the present invention.

[0020] Figure 4 It is a flow chart of a method of improving sort-merge join based on grouping results in an embodiment of the present invention.

[0021] Figure 5 4 is a time-consuming comparison diagram of various sorting methods in the embodiments of the present invention. DETAILED DESCRIPTION

[0022] The specific implementation of the present invention is described in detail below in conjunction with the accompanying drawings. It should be understood that the specific implementation described here is only used to illustrate and explain the present invention, and is not used to limit the present invention.

[0023] like Figure 1 As shown in the figure, the existing algorithm of sort-merge join based on sorting table connection includes the following steps:

[0024] Step 1: Sort the data in the left table according to the connection column, increasing from left to right and from top to bottom;

[0025] Step 2: Sort the data in the right table according to the connection column, increasing from left to right and from top to bottom;

[0026] Step 3: Initialize the result to an empty data set;

[0027] Step 4: Initialize the row pointers of the left table and the right table to point to the first data after sorting the left and right tables respectively;

[0028] Step 5: Construct the outer loop and execute it, including the following steps:

[0029] Step 5.1: Construct and execute the first inner loop, and compare the corresponding join columns of the row pointed to by the left table pointer and the row pointed to by the right table pointer from left to right until all join columns are equal, that is, until the corresponding equal join columns are found in the left and right tables;

[0030] If a connection column of the left table is smaller than the corresponding connection column of the right table, the following judgment is made: if the left table pointer does not point to the last data of the current row of the left table, the left table pointer moves to the next row; otherwise, the outer loop is stopped and the result is output;

[0031] If a connection column of the left table is greater than the corresponding connection column of the right table, the following judgment is made: if the pointer of the right table does not point to the last data of the current row of the right table, the pointer of the right table moves to the next row; otherwise, the outer loop is stopped and the result is output;

[0032] Step 5.2: Record the current position of the left table pointer;

[0033] Step 5.3: Build and execute the second inner loop, comparing the corresponding join columns from left to right for the row pointed to by the left table pointer and the row pointed to by the right table pointer, until all join columns are equal or there is no more data in the left table;

[0034] The rows pointed to by the left and right table pointers, which constitute a row of the result, are added to the result; the left table pointer moves to the next row;

[0035] If there is more data in the right table, the right table pointer moves to the next row; otherwise, stop the outer loop and output the result;

[0036] Compare the corresponding connection columns of the row where the left table pointer previously recorded the position with the row where the right table pointer points to from left to right. If all are equal, the left table pointer is reset to the previously recorded position. If not all are equal, make another judgment. If there is no more data in the left table, stop the outer loop and output the result. If there is more data, continue the outer loop.

[0037] Step 6: Output the results.

[0038] Comparison of the size of join columns refers to comparing the values ​​of the join columns of the left and right tables in a row. For example, if the values ​​of the three join columns of the left table in a row are a, b, and c, and the values ​​of the three join columns of the right table in a row are d, e, and f, then the values ​​of each join column of the rows of the left and right tables are compared one by one. For example, when a>d, it means that the join column of the left table is larger than the join column of the right table. If a=d, then determine whether b is larger than e. If b>e, it means that the join column of the left table is larger. If b=e, then continue to compare c and f, and so on.

[0039] like Figure 2 As shown, the data is sorted according to the connection column, generally using merge sort, including the following steps:

[0040] Step 1: If the length of the input data (table, sort column) is <= 1, return the original data (output data); otherwise, split the input data from the middle row;

[0041] Step 2: Recursively call merge sort on the left and right sides respectively; for example, if an array is [a,b,c,d,e,f,], first divide the data into two parts, the left side is [a,b,c], and the right side is [d,e,f], and then recursively call merge sort on the left and right sides;

[0042] Step 3: Initialize the result data to be empty;

[0043] Step 4: Merge the sorting results on the left and right sides:

[0044] Step 4.1: Maintain the positions of the currently considered rows on the left and right;

[0045] Step 4.2: Construct and execute the following loop until there is no more data on the left or right:

[0046] For the currently considered rows on the left and right, compare the corresponding connection columns from left to right. If all are equal, or for the first unequal connection column, if the left side < the right side, then the current row on the left is appended to the end of the result data, and the position of the currently considered row on the left is shifted back by 1; otherwise, the current row on the right is appended to the end of the result data, and the position of the currently considered row on the right is shifted back by 1;

[0047] Step 4.3: If there are unconsidered rows on the left, append all remaining rows to the end of the result data;

[0048] Step 4.4: If there are unconsidered rows on the right, append all remaining rows to the end of the result data;

[0049] Step 7: Return the merged result.

[0050] To improve the performance of the existing sort-merge join algorithm when the amount of data is large, we can start from the following two aspects:

[0051] 1. Find a sorting algorithm with lower complexity for large amounts of data. The time complexity of the above merge sort algorithm is O(k* n * log(n)), where k is the number of connection columns and n is the length of the data;

[0052] 2. When matching the left and right table pointers, skip the area with the same join column value to reduce the number of comparisons. The above sort-merge join algorithm compares the rows of the left and right tables O(k * m * n), where k is the number of join columns, m is the length of the left table, and n is the length of the right table.

[0053] To improve the performance of the sort-merge join algorithm, the present invention proposes a method for sorting a table based on multiple columns, using the idea of radix sort to improve the sorting performance under a large amount of data. While sorting the data, it groups the data, and a group is defined as: a contiguous region in the sorted data where all the values of the join columns are the same.

[0054] First, sort and group the data according to the join columns, as Figure 3 shown, and the specific process is as follows:

[0055] Step 1: Input the data, all the join columns, and the subscript of the currently considered join column (0 when called for the first time);

[0056] Step 2: Sort the data according to the currently considered join column;

[0057] Step 3: For the sorted data, mark the starting positions and lengths of all the duplicate value regions of the current join column;

[0058] Step 4: Initialize the grouping result as empty;

[0059] Step 5: For each duplicate value region of the current column, perform the following operations:

[0060] If the current column is not the last join column, for the data within the region, recursively call the current data sorting and grouping process, and increment the subscript of the considered join column by 1; append the grouping result returned by the recursive call to the overall grouping result;

[0061] Otherwise, append the starting position and length of the current region to the overall grouping result;

[0062] Step 6: Return the sorted data and the grouping result.

[0063] Assume that when the "sort the data according to the currently considered join column" in Step 2 above uses radix sort, the complexity of the algorithm is O(k * w * n), where k is the number of join columns, w is the average length of each piece of data in the array, and n is the data length. For the data in the database, w is generally not too long. For example, for a column with a data type of 32-bit integer, the maximum value of w is 32.

[0064] When the data length n is very large, according to the above assumption, w < log(n). Therefore, the algorithm complexity O(k * w * n) of multi-column sorting using the idea of radix sort in the database system < the algorithm complexity O(k * n * log(n)) of multi-column sorting using merge sort, that is, the performance of the database system is improved.

[0065] Then, based on the grouping results, the sort-merge join algorithm is improved to reduce the number of comparisons, such as Figure 4 As shown, the specific steps include:

[0066] Step 1: Input the results of sorting the left and right tables by the connection columns, as well as the grouping results;

[0067] Step 2: Initialize the result to an empty data set;

[0068] Step 3: Maintain the subscripts of the groups currently considered in the left and right tables;

[0069] Step 4: Create an outer loop to loop through the following steps until there are no more groups to consider in the current left or right table:

[0070] Step 4.1: Construct an inner loop to loop over all the concatenated columns:

[0071] Compare the values ​​of the current group and the current join column of the left and right tables:

[0072] If the current connection column of the current group in the left table is greater than the current connection column of the current group in the right table, then the subscript of the group currently considered in the right table is moved back by 1 and the inner loop is stopped;

[0073] If the current connection column of the current group in the left table is less than the current connection column of the current group in the right table, then the subscript of the group currently considered in the left table is moved back by 1 and the inner loop is stopped;

[0074] Step 4.2: If the current groups of the left and right tables are equal in all the join columns, each row in the current left table group and each row in the current right table group are combined into a result row and appended to the result set, and the subscripts of the currently considered groups of the left and right tables are moved back by 1;

[0075] Step 5: Output the result set.

[0076] In the improved sort-merge join algorithm, the subscripts of the groups currently considered in the left and right tables only move backward, not forward. The total number of comparisons is O(k * (m + n)), where k is the number of join columns, m is the length of the left table, and n is the length of the right table. Compared with the comparison number O(k * m * n) of the general algorithm of the existing sort-merge join, the new algorithm significantly reduces the number of comparisons and improves the performance of the database system.

[0077] Performance comparison between merge sort and radix sort:

[0078] like Figure 5As shown, by comparing the time consumption of various sorting algorithms under different data volumes, it can be seen that when the data volume increases, the increase in the time consumption of merge sorting of the method of the present invention (radix sort) is very obvious compared with radix sorting, and the total time consumption of radix sorting has obvious advantages over merge sorting.

[0079] A database system adopts the above-mentioned equivalent query method based on grouping information to perform data query. The implementation method of this part is similar to the implementation method of the above-mentioned method embodiment, and will not be repeated here.

[0080] The above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit the same. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that the technical solutions described in the aforementioned embodiments may still be modified, or some or all of the technical features thereof may be replaced by equivalents. However, these modifications or replacements do not deviate the essence of the corresponding technical solutions from the scope of the technical solutions of the embodiments of the present invention.

Claims

1. A method for equal value query based on grouping information, characterized in that: Sort and group the connection columns in the connection table. Sorting is based on the size of the data values. Grouping is based on the division of repeated value areas of the data sorted by radix. A group is defined as: an area in which all connection columns have the same values ​​in the sorted data, and the data sorting and grouping methods are recursively called on the data in the area to finally obtain the sorted data and grouping results; Obtain the sorting and grouping results of the connection columns in at least two connection tables, compare the data values ​​of the current group and the current connection column of the connection table, and change the grouping direction based on the data value comparison result. If all the connection columns of the group are equal, output the grouped data of the connection table as the query result; In left and right join tables, the join columns are sorted according to their data values, increasing from left to right and from top to bottom. Construct an outer loop to repeatedly execute the inner loop until there are no more groups in the current left table or right table, and output the query result set; Construct an inner loop. If the data value of the current connection column of the current group of the left connection table is greater than the data value of the current connection column of the current group of the right connection table, the next group of the right connection table is used as the current group and the inner loop is stopped. If the data value of the current connection column of the current group of the left connection table is less than the data value of the current connection column of the current group of the right connection table, the next group of the left connection table is used as the current group and the inner loop is stopped. If the current groups of the left and right connection tables are equal in all connection columns, the current left and right connection tables are appended to the query result set, and the next groups of the left and right connection tables are used as the current group.

2. The method for equal value query based on grouping information according to claim 1, characterized in that: The sorting and grouping is to sort the data of the current connection column, find out the repeated value area of ​​the current connection column through the sorted data, and divide it into different groups. For the repeated value area, if the current connection column is not the last connection column, sort and group the data in the area. After the sorting and grouping are completed layer by layer, sort and group the next current connection column until the final sorting and grouping are completed, and merge the grouping results.

3. The method for equal value query based on grouping information according to claim 1, characterized in that: Set a subscript for the current connection column, and sort and group the data in the area of ​​the current connection column layer by layer based on the subscript. After completion, move the subscript back to sort and group the data in the next area of ​​the current connection column layer by layer, and merge the grouping results through the subscript.

4. The method for equal value query based on grouping information according to claim 1, characterized in that: After the current connection column is sorted, the starting position and length of all repeated value areas in the current connection column are marked; after all groupings are completed, the starting position and length of the area are appended to all grouping results.

5. The method for equal value query based on grouping information according to claim 1, characterized in that: Set and maintain the subscripts of the current groups of the left and right connection tables; when the data value of the current connection column of the current group of the left connection table is greater than the data value of the current connection column of the current group of the right connection table, move the subscript of the current group of the right connection table backward; when the data value of the current connection column of the current group of the left connection table is less than the data value of the current connection column of the current group of the right connection table, move the subscript of the current group of the left connection table backward; when the current groups of the left and right connection tables are equal in all connection columns, move the subscripts of the current groups of the left and right connection tables backward.

6. The method for equal value query based on grouping information according to claim 1, characterized in that: When all the connection columns of the current groups of the left and right join tables are equal, each row in the current group of the left join table and each row in the current group of the right join table are combined into a row of results and appended to the query result set.

7. A database system, characterized in that: The database system supports the equivalent query method based on grouping information described in claim 1 to perform data query.

Images