Method for optimizing nested loop connection query efficiency of relational database

Abstract

The invention discloses a method for optimizing the nested loop connection query efficiency of a relational database, relates to the field of relational database management system query, and aims at preliminarily completing data acquisition, processing empty sets and no intersection in advance and eliminating invalid query actions. Bidirectional data simplification is carried out in advance, and a result record set needing comparison is reduced to the maximum extent. And performing optimization selection, selecting a minimum result set as an outer-layer driving data set, completing loop comparison with minimum memory overhead, and outputting the result set to a user. And after each comparison is completed, invalid array members are discarded in time, and the size of the driven array is reduced, so that the overhead of the memory is further reduced.

Description

TECHNICAL FIELD

[0001] The present application relates to the field of relational database management system query, in particular to a method for optimizing the efficiency of nested loop join query of relational database. BACKGROUND

[0002] At present, when the relational database management system (RDBMS) involves multi-table association query, the general query method is to use "nested loop join" query, and the core idea is to compare "double loop", that is, taking each record of an a table (referred to as "outer table / driver table") as a benchmark, matching all records of another b table (referred to as "inner table / driven table") one by one, and merging the results if the association condition is met. It is suitable for the scene that the data volume of the driver table is small (such as within several thousand), or the inner table has efficient index (to avoid full table scan).

[0003] As shown in the accompanying Figure 1 The execution steps are as follows: Select the driver table: the RDBMS optimizer will preferentially select the table with smaller data volume as the driver table a (to reduce the number of outer loop).

[0004] Outer loop: traverse each record (denoted as r) of the driver table.

[0005] Inner loop: for each r, scan the records in the inner table b that meet the association condition (such as a.id = b.a_id), and merge them with r after finding them to join the result set.

[0006] Termination condition: all records of the driver table are traversed.

[0007] It can be seen that the nested loop join query has the following disadvantages: 1. If the inner table has no index and the data volume is large, the full table scan of the inner loop will cause the performance to drop sharply ("Cartesian product disaster") 2. When the driver table is too large, the number of outer loop is large, and the total time consumption increases linearly. SUMMARY

[0008] In order to overcome the above technical defects, the present application provides a method for optimizing the efficiency of nested loop join query of relational database, which saves computing resources and improves query efficiency.

[0009] The technical scheme adopted by the present application to overcome the technical problems is: A method for optimizing the efficiency of nested loop join query of relational database, comprising: S1. defining the primary key T1Pk1 of table T1, the primary key T2Pk1 of table T2, and the foreign key T1Fk1 of table T1; S2. Establish a filtering condition and an array A1 through the primary key T1Pk1 and the foreign key T1Fk1, and obtain a variable vT1Fk1Min and a variable vT1Fk1Max using the array A1; S3. Establish a filtering condition and an array A2 through the primary key T2Pk1 and the foreign key T1Fk1, and obtain a variable vT2Pk1Min and a variable vT2Pk1Max using the array A2; S4. Determine whether the variable vT1Fk1Min is greater than the variable vT2Pk1Max and the variable vT1Fk1Max is less than the variable vT2Pk1Min. If yes, execute step S5; S5. Delete all records with a T1Fk1 value less than the variable vT2Pk1Min and all records with a T1Fk1 value greater than the variable vT2Pk1Max in the array A1; delete all records with a T2Pk1 value less than the variable vT1Fk1Min and all records with a T2Pk1 value greater than the variable vT1Fk1Max in the array A2; S6. Compare the number of remaining records in the array A1 and the array A2, and take the array with a smaller number of remaining records as the outer array and the array with a larger number of remaining records as the inner array.

[0010] Further, in step S1, a database DB1 is created and managed through the relational database management system RDBMS. The database DB1 includes a table T1 and a table T2. Both the table T1 and the table T2 include n columns. The column T1Pk1 of the table T1 is the primary key of the table T1. The column T2Pk1 of the table T2 is the primary key of the table T2. The column T1Fk1 of the table T1 is the foreign key of the table T1.

[0011] Further, in step S1, the foreign key T1Fk1 of the table T1 corresponds to the primary key T2Pk1 of the table T2.

[0012] Further, step S2 includes the following steps: S2-1. According to the T1Pk1, T1Fk1, and {a set of column names of the T1 table} in the SQL statement as a target data group to be collected and stored in the array A1, and according to {other filtering conditions of the T1 table} in the SQL statement as a filtering condition; S2-2. Arrange the data in the array A1 according to the values in the foreign key T1Fk1 from small to large; S2-3. Query the records in the array A1 that meet the filtering condition according to the filtering condition. If the number of records that meet the requirement is 0, terminate this query and return an empty value. If the number of records that meet the requirement is greater than 0, put the minimum value of T1Fk1 in the array A1 into the variable vT1Fk1Min, and put the maximum value of T1Fk1 in the array A1 into the variable vT1Fk1Max.

[0013] Further, step S3 includes the following steps: S3-1. According to T2Pk1 in the SQL statement, {T2 table column name set} as the target data group to be collected and stored in array A2, and {T2 table other filtering conditions} in the SQL statement as the filtering condition; S3-2. The data in array A2 is arranged in ascending order according to the value in the primary key T2Pk1; S3-3. According to the filtering condition, the records meeting the filtering condition in array A2 are queried, if the number of records meeting the requirement is 0, the query is terminated and an empty value is returned, if the number of records meeting the requirement is greater than 0, the minimum value of T2Pk1 in array A2 is put into variable vT2Pk1Min, and the maximum value of T2Pk1 in array A2 is put into variable vT2Pk1Max.

[0014] Further, in step S4, when variable vT1Fk1Min≥vT2Pk1Max, all memory variables are released and an empty value is returned.

[0015] Further, in step S4, when variable vT1Fk1Max≤vT2Pk1Min, all memory variables are released and an empty value is returned.

[0016] The beneficial effects of the present application are: preliminary completion of data collection, early processing of empty set and no intersection, and elimination of invalid query actions. Two-way data simplification is performed in advance to minimize the result record set required for comparison. Optimization selection is made to select the smallest result set as the outer driving data set, to minimize memory overhead, complete the loop comparison, and output the result set to the user. After each comparison is completed, the invalid array members are discarded in time to reduce the size of the driven array, thereby further reducing the memory overhead. BRIEF DESCRIPTION OF DRAWINGS

[0017] Figure 1 The execution steps of the RDBMS optimizer in the prior art. DETAILED DESCRIPTION

[0018] The following will be described in conjunction with the accompanying Figure 1 The present application is further described.

[0019] A method for optimizing the efficiency of nested loop connection query of a relational database, comprising: S1. Defining the primary key T1Pk1 of table T1, the primary key T2Pk1 of table T2, and the foreign key T1Fk1 of table T1.

[0020] S2. Establishing filtering conditions and array A1 through the primary key T1Pk1 and the foreign key T1Fk1, and obtaining variable vT1Fk1Min and variable vT1Fk1Max by using array A1.

[0021] S3. Establish a filter condition and an array A2 through the primary key T2Pk1 and the foreign key T1Fk1, and obtain a variable vT2Pk1Min and a variable vT2Pk1Max by using the array A2.

[0022] S4. Determine whether the variable vT1Fk1Min is greater than the variable vT2Pk1Max and the variable vT1Fk1Max is less than the variable vT2Pk1Min, and if so, execute step S5.

[0023] S5. Delete all records with a T1Fk1 value less than the variable vT2Pk1Min and all records with a T1Fk1 value greater than the variable vT2Pk1Max in the array A1, and delete all records with a T2Pk1 value less than the variable vT1Fk1Min and all records with a T2Pk1 value greater than the variable vT1Fk1Max in the array A2.

[0024] S6. Compare the number of remaining records in the array A1 and the array A2, and take the array with a smaller number of remaining records as the outer array (the driving array) and take the array with a larger number of remaining records as the inner array (the driven array).

[0025] When the relational database management system RDBMS is running, the following steps are included: Outer loop: traverse each record (denoted as r) of the driving array.

[0026] Inner loop: for each r, scan the records x in the inner array that satisfy the association condition (such as t1.T1Fk1 = t2.T2Pk1), find the records x, and combine the records x with r and add the records x to the result set. At the same time, discard all records with a key value less than r, and reduce the size of the inner array.

[0027] Termination condition: all records of the driving array are traversed. Finally, the result set is fed back to the user.

[0028] By pre-filtering and bidirectional comparison to shrink the data set, the invalid data occupies less computing resources, thereby reducing the consumption of low computing resources and optimizing the query effect.

[0029] In an embodiment of the present application, a database DB1 is created and managed by the relational database management system RDBMS in step S1, the database DB1 includes a table T1 and a table T2, the table T1 and the table T2 each contain n columns, a column T1Pk1 of the table T1 is a primary key (unique, non-empty) of the table T1, a column T2Pk1 of the table T2 is a primary key (unique, non-empty) of the table T2, and a column T1Fk1 of the table T1 is a foreign key of the table T1.

[0030] In an embodiment of the present application, the foreign key T1Fk1 of the table T1 corresponds to the primary key T2Pk1 of the table T2 in step S1.

[0031] In one embodiment of the present application, step S2 comprises the following steps: S2-1. According to T1Pk1, T1Fk1, {set of column names of T1 table} in the SQL statement as the target data group to be collected and stored in array A1, and according to {other filtering conditions of T1 table} in the SQL statement as the filtering conditions.

[0032] The specific SQL statement is as follows: Select {set of column names of T1 table}, {set of column names of T2 table} From T1, T2 Where T1.T1Fk1 = T2.T2Pk1 and {other filtering conditions of T1 table} And {other filtering conditions of T2 table}; S2-2. Arrange the data in array A1 according to the values in the foreign key T1Fk1 from small to large.

[0033] S2-3. Query the records in array A1 that meet the filtering conditions according to the filtering conditions. If the number of records that meet the requirements is 0 (indicating that there is no result that meets the query), terminate the query this time and return an empty value. If the number of records that meet the requirements is greater than 0, put the minimum value of T1Fk1 in array A1 into variable vT1Fk1Min, and put the maximum value of T1Fk1 in array A1 into variable vT1Fk1Max.

[0034] In one embodiment of the present application, step S3 comprises the following steps: S3-1. According to T2Pk1, {set of column names of T2 table} in the SQL statement as the target data group to be collected and stored in array A2, and according to {other filtering conditions of T2 table} in the SQL statement as the filtering conditions.

[0035] S3-2. Arrange the data in array A2 according to the values in the primary key T2Pk1 from small to large.

[0036] S3-3. Query the records in array A2 that meet the filtering conditions according to the filtering conditions. If the number of records that meet the requirements is 0 (indicating that there is no result that meets the query), terminate the query this time and return an empty value. If the number of records that meet the requirements is greater than 0, put the minimum value of T2Pk1 in array A2 into variable vT2Pk1Min, and put the maximum value of T2Pk1 in array A2 into variable vT2Pk1Max.

[0037] In one embodiment of the present application, when the variable vT1Fk1Min≥vT2Pk1Max in step S4 indicates that the result set has no intersection, then all memory variables are released and an empty value is returned, ending the query. When the variable vT1Fk1Max≤vT2Pk1Min in step S4 indicates that the result set has no intersection, then all memory variables are released and an empty value is returned, ending the query.

[0038] Finally, it should be noted that the above only describes the preferred embodiments of the present application and is not intended to limit the present application. Although the present application has been described in detail with reference to the foregoing embodiments, those skilled in the art will still be able to make modifications to the technical solutions described in the foregoing embodiments or make equivalent replacements to some of the technical features. Any modifications, equivalent replacements, improvements, etc. made within the spirit and principles of the present application shall be included in the protection scope of the present application.

Claims

1. A method for optimizing the efficiency of nested loop join queries in relational databases, characterized in that, include: S1. Define the primary key T1Pk1 of table T1, the primary key T2Pk1 of table T2, and the foreign key T1Fk1 of table T1; S2. Establish filtering conditions and array A1 through primary key T1Pk1 and foreign key T1Fk1, and use array A1 to obtain variables vT1Fk1Min and vT1Fk1Max; S3. Establish filtering conditions and array A2 through primary key T2Pk1 and foreign key T1Fk1, and use array A2 to obtain variables vT2Pk1Min and vT2Pk1Max; S4. Determine whether variable vT1Fk1Min > vT2Pk1Max and variable vT1Fk1Max < vT2Pk1Min. If so, proceed to step S5. S5. In array A1, delete all records where the value of T1Fk1 is less than the variable vT2Pk1Min, and delete all records where the value of T1Fk1 is greater than vT2Pk1Max; in array A2, delete all records where the value of T2Pk1 is less than the variable vT1Fk1Min, and delete all records where the value of T2Pk1 is greater than vT1Fk1Max. S6. Compare the number of remaining records in array A1 and array A2, and use the array with fewer remaining records as the outer array and the array with more remaining records as the inner array.

2. The method for optimizing the efficiency of nested loop join queries in relational databases according to claim 1, characterized in that: In step S1, a database DB1 is created and managed using a relational database management system (RDBMS). Database DB1 includes tables T1 and T2. Both tables T1 and T2 contain n columns. Column T1Pk1 of table T1 is the primary key of table T1, column T2Pk1 of table T2 is the primary key of table T2, and column T1Fk1 of table T1 is the foreign key of table T1.

3. The method for optimizing the efficiency of nested loop join queries in relational databases according to claim 1, characterized in that: In step S1, the foreign key T1Fk1 in table T1 corresponds to the primary key T2Pk1 in table T2.

4. The method for optimizing the efficiency of nested loop join queries in relational databases according to claim 1, characterized in that, Step S2 includes the following steps: S2-1. Use T1Pk1, T1Fk1, and {the set of column names in table T1} from the SQL statement as the target data group to be collected and store them in array A1. Use {other filtering conditions in table T1} from the SQL statement as the filtering conditions. S2-2. Sort the data in array A1 in ascending order according to the values ​​in the foreign key T1Fk1; S2-3. Query the records in array A1 that meet the filter criteria. If the number of records that meet the requirements is 0, terminate the query and return an empty value. If the number of records that meet the requirements is greater than 0, put the minimum value of T1Fk1 in array A1 into the variable vT1Fk1Min, and put the maximum value of T1Fk1 in array A1 into the variable vT1Fk1Max.

5. The method for optimizing the efficiency of nested loop join queries in relational databases according to claim 1, characterized in that, Step S3 includes the following steps: S3-1. Use T2Pk1 and {the set of column names in table T2} from the SQL statement as the target data group to be collected and store them in array A2. Use {other filtering conditions in table T2} from the SQL statement as the filtering conditions. S3-2. Sort the data in array A2 in ascending order according to the value in the primary key T2Pk1; S3-3. Query the records in array A2 that meet the filter criteria. If the number of records that meet the requirements is 0, terminate the query and return an empty value. If the number of records that meet the requirements is greater than 0, put the minimum value of T2Pk1 in array A2 into the variable vT2Pk1Min, and put the maximum value of T2Pk1 in array A2 into the variable vT2Pk1Max.

6. The method for optimizing the efficiency of nested loop join queries in relational databases according to claim 1, characterized in that: In step S4, when variable vT1Fk1Min≥vT2Pk1Max, all memory variables are released and a null value is returned.

7. The method for optimizing the efficiency of nested loop join queries in relational databases according to claim 1, characterized in that: In step S4, when variable vT1Fk1Max ≤ vT2Pk1Min, all memory variables are released and an empty value is returned.

Images