Equivalent query method based on grouping information and database system

Abstract

The invention discloses an equivalence query method based on grouping information and a database system, and belongs to the technical field of databases, the database system adopts the equivalence query method based on the grouping information, the method comprises the following steps: firstly, sequencing and grouping connection columns in a connection table, sequencing according to the size of data values, the grouping comprises the following steps of: dividing duplicate value regions on the basis of cardinal number sorted data, recursively calling the data sorting and grouping method for the data in the regions, and finally obtaining sorted data and a grouping result; and then, obtaining sorting and grouping results of the connection columns in the at least two connection tables, comparing the data values of the current group and the current connection column of the connection tables, changing the group based on the sorting direction of the numerical values according to the comparison result of the data values, and if all the connection columns of the group are equal, outputting the grouped data of the connection tables as a 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: 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.

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

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

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

[0008] 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; 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.

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

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

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

[0012] The advantages and beneficial effects of the present invention are: 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

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

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

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

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

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

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

[0019] like Figure 1 As shown in the figure, the existing algorithm of sort-merge join based on sorting table connection includes the following steps: Step 1: Sort the data in the left table according to the connection column, increasing from left to right and from top to bottom; Step 2: Sort the data in the right table according to the connection column, increasing from left to right and from top to bottom; Step 3: Initialize the result to an empty data set; 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; Step 5: Construct the outer loop and execute it, including the following steps: Step 5.1: Construct and execute the first inner loop, and compare 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, that is, until the corresponding equal join columns are found in the left and right tables; 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; 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; Step 5.2: Record the current position of the left table pointer; 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; 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; 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; 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. Step 6: Output the results.

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

[0021] like Figure 2 As shown, the data is sorted according to the connection column, generally using merge sort, including the following steps: 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; 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; Step 3: Initialize the result data to be empty; Step 4: Merge the sorting results on the left and right sides: Step 4.1: Maintain the positions of the currently considered rows on the left and right; Step 4.2: Construct and execute the following loop until there is no more data on the left or right: 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; Step 4.3: If there are unconsidered rows on the left, append all remaining rows to the end of the result data; Step 4.4: If there are unconsidered rows on the right, append all remaining rows to the end of the result data; Step 7: Return the merged result.

[0022] 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: 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; 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.

[0023] In order to improve the performance of the sort-merge join algorithm, the present invention proposes a method for sorting a table according to multiple columns, using the idea of ​​radix sorting to improve the sorting performance under large amounts of data. While sorting, the data is grouped, and a group is defined as: an area in the sorted data where all the connection columns have the same value.

[0024] First, sort and group the data based on the join columns, such as Figure 3 As shown, the specific process is as follows: Step 1: Input data, all join columns, and the join column index currently considered (0 for the first call); Step 2: Sort the data according to the currently considered connection column; Step 3: For the sorted data, mark the starting position and length of all repeated value areas in the current connection column; Step 4: Initialize the grouping result to be empty; Step 5: For each duplicate value region in the current column, perform the following operations: If the current column is not the last joined column, for the data within the region, recursively call the current data sorting and grouping process, moving the subscript of the joined column to be considered backward by 1; return the grouping result from the recursive call and merge it into the overall grouping result; Otherwise, append the starting position and length of the current region to the overall grouping result; Step 6: Return the sorted data and the grouping result.

[0025] Assume that when performing "sorting its data according to the currently considered joined column" in Step 2 above, radix sort is used. The complexity of the algorithm is O(k * w * n), where k is the number of joined 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.

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

[0027] Then, based on the grouping result, improve the sort-merge join algorithm to reduce the number of comparisons. For example, Figure 4 as shown, it specifically includes the following steps: Step 1: Input the sorted results of the left and right tables according to the joined columns, and the grouping result; Step 2: Initialize the result as an empty data set; Step 3: Maintain the subscripts of the currently considered groups in the left and right tables; Step 4: Construct an outer loop to loop and execute the following steps until there are no more groups to consider in the current left or right table: Step 4.1: Construct an inner loop to loop and execute all joined columns: Compare the values of the current joined column in the current groups of the left and right tables: If the current joined column in the current group of the left table > the current joined column in the current group of the right table, move the subscript of the currently considered group in the right table backward by 1 and stop the inner loop; If the current joined column in the current group of the left table < the current joined column in the current group of the right table, move the subscript of the currently considered group in the left table backward by 1 and stop the inner loop; 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; Step 5: Output the result set.

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

[0029] Performance comparison between merge sort and radix sort: like Figure 5 As 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.

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

[0031] 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: Sorting and grouping 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 the cardinality, and recursively calling the data sorting and grouping methods on the data in the area to finally obtain sorted data and grouping results. Get 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.

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: 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; 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.

6. The method for equal value query based on grouping information according to claim 5, 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.

7. The method for equal value query based on grouping information according to claim 5, 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.

8. 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