Cardinality estimation method for database operation statements and related products

Abstract

The invention provides a cardinal number estimation method for database operation statements and a related product. The method comprises the following steps: acquiring a database operation statement; in the cardinal number estimation stage, whether the database operation statement meets a preset condition or not is judged, and the preset extension statistical information is used for recording statistical information corresponding to a plurality of target columns with correlation in the data table; under the condition that the database operation statement meets the preset condition, whether all predicate filtering conditions in the database operation statement are connected through an OR logic predicate or not is judged; and if yes, circularly searching the optimal preset extension statistical information in the database operation statement to carry out selection rate estimation. By means of the method, the cardinality estimation algorithm can be improved, and then the cardinality estimation accuracy of the table under the single-table multi-predicate filtering condition is improved. As the cardinality estimation accuracy of the table is improved, the optimizer can select a better execution plan, and the query efficiency is improved.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a cardinality estimation method for database operation statements and related products. Background Art

[0002] In the current database technology field, if a single table in the query contains multiple predicate filter conditions, the database optimizer generally uses single-column statistical information for cardinality estimation. If multiple predicate filter conditions are OR operations, cardinality estimation is performed according to the formula P(A or B) = P(A) + P(B) - P(A) * P(B). This cardinality estimation method is calculated based on the independence assumption between column attributes, ignoring the correlation between multiple column attributes, and thus has a large error. Summary of the invention

[0003] In view of the above problems, the present invention proposes a cardinality estimation method for database operation statements and related products that overcome the above problems or at least partially solve the above problems.

[0004] It is an object of the present invention to improve the algorithm for cardinality estimation.

[0005] A further object of the present invention is to consider the correlation between different predicates to improve the accuracy of the selection rate estimation of the predicate filtering condition.

[0006] Another further object of the present invention is to maximize the selectivity of calculation by extending statistical information to improve the estimation accuracy.

[0007] In particular, the present invention provides a method for estimating the cardinality of a database operation statement, comprising:

[0008] Get database operation statements;

[0009] In the cardinality estimation phase, it is determined whether the database operation statement meets the preset conditions. The preset conditions are the conditions required for using the preset extended statistical information. The preset extended statistical information is used to record the statistical information corresponding to multiple target columns with correlation in the data table.

[0010] When the database operation statement meets the preset conditions, it is determined whether the predicate filter conditions in the database operation statement are all connected by the OR logical predicate;

[0011] If so, the best preset extended statistical information in the database operation statement is searched cyclically to estimate the selectivity. The best preset extended statistical information is the preset extended statistical information that covers the largest number of target columns.

[0012] Optionally, the step of cyclically searching for the best preset extended statistical information in the database operation statement to estimate the selectivity includes:

[0013] Find the best preset extended statistics in the predicate filter conditions in the database operation statement;

[0014] The best selection rate corresponding to the predicate filter condition covered by the best preset extended statistical information is estimated by a first preset formula, and the first preset formula is used to correct and calculate the best selection rate corresponding to the predicate filter condition connected by the OR logical predicate;

[0015] Determine whether there are any remaining unevaluated predicate filter conditions;

[0016] In the case where there is an unevaluated predicate filter condition, determining whether there is a target column covered by preset extended statistical information in the target column corresponding to the unevaluated predicate filter condition;

[0017] If yes, continue to perform the step of finding the best preset extended statistics in the predicate filter condition in the database operation statement.

[0018] Optionally, after the step of estimating the best selection rate corresponding to the predicate filtering condition covered by the best preset extended statistical information by using the first preset formula, the following step further comprises:

[0019] Determine whether this cycle is the first cycle;

[0020] If so, executing the step of determining whether there are any remaining unevaluated predicate filter conditions;

[0021] When this cycle is not the first cycle, the stage selection rate obtained in the previous cycle and the best selection rate obtained in this cycle are substituted into the second preset formula to calculate the stage selection rate corresponding to this cycle. The second preset formula is used to calculate the result after the two sets of selection rates are connected by the OR logical predicate.

[0022] Optionally, the first preset formula includes:

[0023] P(A or B)=sel_A+sel_B-(sel_ext+sel_simple-sel_extbase)

[0024] P(A or B) indicates the optimal selectivity calculated by correcting columns A and B after connecting them with the OR logical predicate. sel_A indicates the selectivity calculated based on the independence assumption for column A. sel_B indicates the selectivity calculated based on the independence assumption for column B. sel_ext indicates the selectivity calculated based on the best preset extended statistical information for columns A and B. sel_simple indicates the selectivity calculated based on single-column statistical information for columns A and B. sel_extbase indicates the selectivity calculated based on the best preset extended statistical information but without considering correlation for columns A and B.

[0025] The second preset formula includes:

[0026] P'(A or B)=P(A)+P(B)-P(A)*P(B)

[0027] P'(A or B) represents the result of the selectivity P(A) and the selectivity P(B) being connected by the OR logical predicate.

[0028] Optionally, after the step of determining whether there are target columns covered by preset extended statistical information in the target columns corresponding to the unevaluated predicate filtering condition, the step further includes:

[0029] When it is determined that there is no target column covered by the preset extended statistical information in the target column corresponding to the unestimated predicate filter condition, the unestimated predicate filter condition is estimated according to the single-column statistical information to obtain the single-column selectivity corresponding to each column of the unestimated predicate filter condition;

[0030] Substituting the stage selectivity and the single column selectivity into the second preset formula to calculate the final selectivity;

[0031] Get the total number of data rows in the data table corresponding to the database operation statement;

[0032] The cardinality estimate is calculated by multiplying the final selectivity by the total number of data rows.

[0033] Optionally, after the step of determining whether the database operation statement satisfies a preset condition, the step further includes:

[0034] When the database operation statement does not meet the preset conditions, each column in the predicate filtering condition is estimated according to the single column statistical information to obtain the single column selection rate corresponding to each column;

[0035] Substituting the estimated single-column selectivity into the second preset formula for calculation, to obtain the final selectivity corresponding to the database operation statement;

[0036] The cardinality estimation value corresponding to the database operation statement is calculated based on the final selectivity.

[0037] Optionally, the step of determining whether the database operation statement meets a preset condition includes:

[0038] Get the predicate filter conditions in the database operation statement;

[0039] Determine whether the predicate filter conditions all belong to the same data table;

[0040] If so, determine whether there is preset extended statistical information in the data table;

[0041] In the case where there is preset extended statistical information in the data table, determining whether the number of predicate filtering conditions is greater than a preset threshold;

[0042] When the number of predicate filtering conditions is greater than a preset threshold, it is determined that the database operation statement meets the preset condition.

[0043] Optionally, after the step of determining whether all predicate filtering conditions in the database operation statement are connected by an OR logical predicate, the following step further comprises:

[0044] When the logical predicates between the predicate filtering conditions in the database operation statement include and only include the OR logical predicate and the AND logical predicate, the optimal preset extended statistical information in the database operation statement is searched cyclically, and the selectivity is estimated by combining the corresponding calculation method of the logical predicate selection between the predicate filtering conditions.

[0045] According to another aspect of the present invention, a computer program product is provided, which includes a computer program, and when the computer program is executed by a processor, the steps of any of the above-mentioned methods for estimating the cardinality of database operation statements are implemented.

[0046] According to another aspect of the present invention, there is also provided a computer device, comprising a memory, a processor, and a machine executable program stored in the memory and running on the processor, and the processor implements the steps of any of the above-mentioned methods for estimating the cardinality of database operation statements when executing the machine executable program.

[0047] The cardinality estimation method of the database operation statement of the present invention first obtains the database operation statement; then in the cardinality estimation stage, it is judged whether the database operation statement meets the preset condition, the preset condition is the condition required for using the preset extended statistical information, and the preset extended statistical information is used to estimate the selectivity corresponding to multiple columns in the predicate filtering condition corresponding to the database operation statement; when the database operation statement meets the preset condition, it is judged whether the predicate filtering conditions in the database operation statement are all connected by the OR logical predicate; if so, the best preset extended statistical information in the database operation statement is cyclically searched for to estimate the selectivity, and the best preset extended statistical information is the preset extended statistical information covering the most columns in the predicate filtering condition. Through this method, the cardinality estimation algorithm can be improved when the predicate filtering conditions are all connected by the OR logical predicate, thereby improving the accuracy of the cardinality estimation of the table under the single table multi-predicate filtering condition. As the accuracy of the cardinality estimation of the table is improved, the optimizer can select a better execution plan to improve the efficiency of the query.

[0048] Furthermore, the cardinality estimation method of the database operation statement of the present invention, after finding the best preset extended statistical information, first estimates the best selection rate corresponding to the predicate filter condition covered by the best preset extended statistical information through the first preset formula, and the first preset formula is used to correct and calculate the best selection rate corresponding to the predicate filter condition connected by the or logical predicate; then, in the case of the existence of the unestimated predicate filter condition, it is determined whether there is still a target column covered by the preset extended statistical information in the target column corresponding to the unestimated predicate filter condition; if so, continue to execute this step. In this way, the correlation between the target columns corresponding to different predicate filter conditions can be considered to the greatest extent, thereby improving the accuracy of the cardinality estimation.

[0049] Based on the following detailed description of specific embodiments of the present invention in conjunction with the accompanying drawings, those skilled in the art will become more aware of the above and other objects, advantages and features of the present invention. BRIEF DESCRIPTION OF THE DRAWINGS

[0050] Hereinafter, some specific embodiments of the present invention will be described in detail in an exemplary and non-limiting manner with reference to the accompanying drawings. The same reference numerals in the accompanying drawings indicate the same or similar components or parts. It should be understood by those skilled in the art that these drawings are not necessarily drawn to scale. In the accompanying drawings:

[0051] Figure 1 is a flow chart of a method for estimating the cardinality of a database operation statement according to an embodiment of the present invention;

[0052] Figure 2 is a flow chart of a method for estimating the cardinality of a database operation statement according to another embodiment of the present invention;

[0053] Figure 3is a schematic diagram of a computer program product according to an embodiment of the present invention;

[0054] Figure 4 is a schematic diagram of a computer-readable storage medium according to an embodiment of the present invention; and

[0055] Figure 5 is a schematic diagram of a computer device according to an embodiment of the present invention. DETAILED DESCRIPTION

[0056] It should be understood by those skilled in the art that the embodiments described below are only some embodiments of the present invention, rather than all embodiments of the present invention, and these embodiments are intended to explain the technical principles of the present invention, rather than to limit the protection scope of the present invention. Based on the embodiments provided by the present invention, all other embodiments obtained by ordinary technicians in this field without creative work should still fall within the protection scope of the present invention.

[0057] It should be noted that the logic and / or steps represented in the flowchart or described in other ways herein, for example, can be considered as a sequenced list of executable instructions for implementing logical functions, and can be specifically implemented in any computer-readable medium for use by an instruction execution system, device or equipment (such as a computer-based system, a system including a processor, or other system that can fetch instructions from an instruction execution system, device or equipment and execute instructions), or used in combination with these instruction execution systems, devices or equipment.

[0058] The present invention provides a method for estimating the cardinality of database operation statements. Figure 1 FIG. 1 is a flow chart of a method for estimating the cardinality of a database operation statement according to an embodiment of the present invention. Figure 1 As shown, the cardinality estimation method of the database operation statement at least includes the following steps S101 to S104.

[0059] Step S101, obtaining database operation statements. An optional example is SQL (Structured Query Language). SQL statements have multiple functions such as data manipulation and data definition. This language has interactive characteristics and can provide great convenience for users. In this step, the way to receive database operation statements can generally be active input by the user, or automatically generated by the database program during operation due to business needs. Those skilled in the art can decide the way to receive database operation statements according to actual conditions.

[0060] Step S102, in the cardinality estimation phase, determines whether the database operation statement meets the preset conditions. The preset conditions are generally the conditions required for using the preset extended statistical information, and the preset extended statistical information is generally used to record the statistical information corresponding to multiple target columns with correlation in the data table. Extended statistical information is a type of data used in the database system to more accurately estimate the size (cardinality) of the query result set. It goes beyond the traditional single-column statistical information and takes into account the correlation between multiple columns, data distribution, and other complex data features.

[0061] In SQL statements, a predicate is an expression used to specify conditions to filter, compare or judge data. It is the basic element of the query condition and the return value is a Boolean type, that is, true (True) or false (False). The predicate filter condition generally refers to the conditional expression used to filter data in statements such as SELECT, WHERE, HAVING, etc. It can select the row data that meets the requirements from the table according to specific conditions. Predicate filter conditions generally filter data through comparison predicates, such as = (equal to), > (greater than), < (less than), > = (greater than or equal to), < = (less than or equal to) and <> or! = (not equal to). In addition, each predicate filter condition is also connected by a logical predicate, which includes and logical predicates (such as AND) and or logical predicates (such as OR).

[0062] In some optional embodiments, the step of determining whether a database operation statement satisfies a preset condition may generally include: obtaining a predicate filter condition in the database operation statement; determining whether the predicate filter conditions all belong to the same data table; if so, determining whether there is preset extended statistical information in the data table; if there is preset extended statistical information in the data table, determining whether the number of predicate filter conditions is greater than a preset threshold; if the number of predicate filter conditions is greater than the preset threshold, determining that the database operation statement satisfies the preset condition.

[0063] In the process of judging whether the number of predicate filter conditions is greater than the preset threshold, since the extended statistical information generally includes at least 2 columns, the preset threshold can be set to 1, that is, whether the number of predicate filter conditions is greater than 1. When the number of predicate filter conditions is at least 2, it is possible to use the corresponding extended statistical information. Those skilled in the art can set the number of preset thresholds according to actual conditions.

[0064] Step S103, when the judgment in step S102 is yes, it is determined whether all the predicate filtering conditions in the database operation statement are connected by an OR logical predicate.

[0065] Step S104, when the judgment in step S103 is yes, looping to search for the best preset extended statistical information in the database operation statement to estimate the selection rate.

[0066] Optionally, the step of cyclically searching for the best preset extended statistical information in the database operation statement to estimate the selectivity may generally include: searching for the best preset extended statistical information in the predicate filter condition in the database operation statement; estimating the best selectivity corresponding to the predicate filter condition covered by the best preset extended statistical information by using a first preset formula, the first preset formula being used to correct and calculate the best selectivity corresponding to the predicate filter condition connected by the OR logical predicate; judging whether there are any remaining unestimated predicate filter conditions; in the case of unestimated predicate filter conditions, judging whether there are any target columns covered by the preset extended statistical information in the target columns corresponding to the unestimated predicate filter conditions; if so, continuing to perform the step of searching for the best preset extended statistical information in the predicate filter condition in the database operation statement. When the number of columns covered by the preset extended statistical information is the largest, the number of columns with correlation is also the largest. At this time, the best preset extended statistical information will be counted based on the correlation between different columns, and the selectivity calculated by the best preset extended statistical information will be more accurate than the selectivity calculated by calculating each column according to the single column statistical information.

[0067] Specifically, the first preset formula generally includes:

[0068] P(A or B)=sel_A+sel_B-(sel_ext+sel_simple-sel_extbase)

[0069] P(A or B) indicates the optimal selectivity calculated by correcting columns A and B after connecting them with the OR logical predicate. sel_A indicates the selectivity calculated based on the independence assumption for column A. sel_B indicates the selectivity calculated based on the independence assumption for column B. sel_ext indicates the selectivity calculated based on the best preset extended statistical information for columns A and B. sel_simple indicates the selectivity calculated based on single-column statistical information for columns A and B. sel_extbase indicates the selectivity calculated based on the best preset extended statistical information but without considering correlation for columns A and B.

[0070] As can be seen from the first preset formula, this formula is generally calculated in pairs. When the best preset extended statistical information covers the target columns corresponding to more than two predicate filter conditions, and multiple predicate filter conditions are connected by OR logical predicates, the calculation process at this time is as follows:

[0071] Assuming that the best preset extended statistical information covers the target columns corresponding to three predicate filtering conditions, in the case of more than two optional conditions, the use example of the first preset formula is as follows:

[0072] select * from mcv_lists_multi where a=0 or b=0 or c=0; Formula 1

[0073] When the database receives the database operation statement shown in Formula 1, there are three predicate filter conditions a=0, b=0, c=0 inside it, and they are all connected by the OR logic predicate. The three columns a, b, and c all belong to the same data table, and the extended statistical information mcv_lists_partial_s1(a, b, c) is established inside the data table.

[0074] Under the above premise, it is determined that the database operation statement meets the preset conditions, and the best preset extended statistical information is mcv_lists_partial_s1(a,b,c). Therefore, the selectivity corresponding to a=0or b=0or c=0 needs to be calculated by the first preset formula.

[0075] For easy identification, the data column corresponding to the predicate filter condition a=0 is set as A, the data column corresponding to the predicate filter condition b=0 is set as B, and the data column corresponding to the predicate filter condition c=0 is set as C, that is, A or BorC.

[0076] First, calculate the selection rate corresponding to data column A and data column B.

[0077] P(A or B)=sel_A+sel_B-(sel_ext+sel_simple-sel_extbase)

[0078] Where sel_A represents the selection rate of data column A calculated based on the independence assumption, sel_B represents the selection rate of data column B calculated based on the independence assumption, and sel_A+sel_B represents the sum of the two selection rates;

[0079] sel_ext is the selectivity of data column A and data column B calculated based on the best preset extended statistical information, that is, the selectivity of the common area of ​​data column A and data column B;

[0080] sel_simple is the selectivity calculated based on single-column statistical information for data column A and data column B. sel_extbase is the selectivity calculated based on the best preset extended statistical information without considering correlation for data column A and data column B. The result of sel_simple-sel_extbase indicates the selectivity of the common area of ​​AB that is not in the extended statistical information.

[0081] After calculation, the selection rate of data column A or data column B can be calculated based on the best preset extended statistical information (assuming the result after calculation is D), and the result and data column C are substituted into the first preset formula. The calculation process is:

[0082] P(D or C)=sel_D+sel_C-(sel_ext+sel_simple-sel_extbase);

[0083] Among them, sel_D is the result of the first calculation of P(A or B), sel_C represents the selectivity of data column C calculated based on the independence assumption, sel_ext is the selectivity of data column A and data column Band and data column C calculated based on the best preset extended statistical information, sel_simple is the selectivity of data column A and data column B and data column C calculated based on single column statistical information, that is, the selectivity of the common area of ​​data column A and data column B and data column C; sel_extbase is the selectivity of data column A and data column B and data column C calculated based on the best preset extended statistical information but without considering the correlation, and the result of sel_simple-sel_extbase is the selectivity of the common area of ​​ABC that is not in the extended statistical information. The final result is the stage selectivity of this cycle.

[0084] It should be noted that the best preset extended statistical information only covers more columns than other currently available extended statistical information. If two or more extended statistical information with the same number of covered columns are encountered during the judgment process, any one of them can be selected as the best preset extended statistical information according to actual needs; if two or more extended statistical information cover the same number of columns and do not repeat, the two or more extended statistical information can be used as the best preset extended statistical information at the same time for synchronous calculation.

[0085] The operation of the loop search is that after the best preset extended statistical information is selected, the extended statistical information with the largest number of columns is continued to be searched in the remaining columns as the next best preset extended statistical information, until no extended statistical information can be obtained. In this way, the selection rate is estimated by using extended statistical information as much as possible, which can maximize the accuracy of the results.

[0086] In some optional embodiments, after the step of estimating the best selection rate corresponding to the predicate filtering condition covered by the best preset extended statistical information through the first preset formula, the following steps may generally be included: determining whether this cycle is the first cycle; if so, executing the step of determining whether there are any remaining unestimated predicate filtering conditions; if this cycle is not the first cycle, substituting the stage selection rate obtained in the previous cycle and the best selection rate obtained in this cycle into the second preset formula to calculate the stage selection rate corresponding to this cycle, and the second preset formula is used to calculate the result after the two groups of selection rates are connected by the OR logical predicate.

[0087] Optionally, the second preset formula generally includes:

[0088] P'(A or B)=P(A)+P(B)-P(A)*P(B)

[0089] P'(A or B) represents the result of the selectivity P(A) and the selectivity P(B) being connected by the OR logical predicate.

[0090] Since the preset extended statistical information used in different loops is different, that is, there is no correlation between the target columns calculated between different loop rounds, the stage selection rate obtained in the previous loop and the best selection rate obtained in this loop can be directly substituted into the second preset formula to calculate the stage selection rate corresponding to this loop.

[0091] This method can improve the cardinality estimation algorithm when all predicate filter conditions are connected by OR logical predicates, thereby improving the accuracy of cardinality estimation of tables under single-table multi-predicate filter conditions. As the accuracy of cardinality estimation of tables improves, the optimizer can select a better execution plan and improve query efficiency.

[0092] In some optional embodiments, after the step of determining whether there are target columns covered by preset extended statistical information in the target columns corresponding to the unestimated predicate filter conditions, the following steps may be generally performed: in the case where it is determined that there are no target columns covered by preset extended statistical information in the target columns corresponding to the unestimated predicate filter conditions, the unestimated predicate filter conditions are estimated according to the single-column statistical information to obtain the single-column selectivity corresponding to each column of the unestimated predicate filter conditions; the stage selectivity and the single-column selectivity are substituted into the second preset formula to obtain the final selectivity; the total number of data rows in the data table corresponding to the database operation statement is obtained; and the final selectivity is multiplied by the total number of data rows to obtain the cardinality estimation value. When there is no preset extended statistical information in the unestimated predicate filter conditions, it indicates that there is no correlation in the columns corresponding to the remaining predicate filter conditions, and therefore the single-column statistical information is estimated.

[0093] In some other optional embodiments, after the step of determining whether the database operation statement meets the preset conditions, the following may be generally included: if the database operation statement does not meet the preset conditions, each column in the predicate filter condition is estimated according to the single column statistical information to obtain the single column selectivity corresponding to each column; the estimated single column selectivity is substituted into the second preset formula for calculation to obtain the final selectivity corresponding to the database operation statement; and the cardinality estimation value corresponding to the database operation statement is calculated according to the final selectivity. If the predicate filter conditions do not belong to the same data table, but there are target columns corresponding to the predicate filter conditions greater than the preset threshold that belong to the same data table and the data table has preset extended statistical information, the selectivity of this part can also be calculated using the method of the present invention.

[0094] In addition, it should be noted that the method of the present invention is not only applicable to the case where all predicate filter conditions are connected by OR logical predicates. When the logical predicates between the predicate filter conditions in the database operation statement include only OR logical predicates and AND logical predicates, the best preset extended statistical information in the database operation statement can also be cyclically searched, and the corresponding calculation method can be selected in combination with the logical predicates between the predicate filter conditions to estimate the selectivity. For example:

[0095] select*from mcv_lists_multi where(a=0or b=0)and(c=0or d=0) Formula 2

[0096] When the database obtains the database operation statement shown in Formula 2, there are three predicate filter conditions a=0, b=0, c=0, d=0, and a=0 and b=0 are connected by an OR logic predicate, c=0 and d=0 are also connected by an OR logic predicate, and the results of the two are connected by an AND logic predicate. Columns A, B, C, and D all belong to the same data table, and the extended statistical information mcv_lists_partial_s1(a,b,c,d) is established inside the data table.

[0097] When the database operation statement meets the preset conditions, there is the best preset extended statistical information, and then the selectivity is calculated through the extended statistical information mcv_lists_partial_s1(a,b,c,d).

[0098] The specific calculation process may be: firstly, the selectivity of a=0 or b=0 is calculated, and then the selectivity is substituted into the first preset formula for calculation, that is, the selectivity corresponding to the data column A and the data column B is calculated.

[0099] P(A or B)=sel_A+sel_B-(sel_ext+sel_simple-sel_extbase)

[0100] Where sel_A represents the selection rate of data column A calculated based on the independence assumption, sel_B represents the selection rate of data column B calculated based on the independence assumption, and sel_A+sel_B represents the sum of the two selection rates;

[0101] sel_ext is the selectivity of data column A and data column B calculated based on the best preset extended statistical information, that is, the selectivity of the common area of ​​data column A and data column B;

[0102] sel_simple is the selectivity calculated based on single-column statistics for data columns A and B. sel_extbase is the selectivity calculated based on the best preset extended statistics without considering correlation for data columns A and B. The result of sel_simple-sel_extbase is the selectivity of the common area of ​​AB that is not in the extended statistics.

[0103] After calculation, the selectivity of data column A or data column B can be calculated based on the best preset extended statistical information, and then the selectivity of c=0 or d=0 can be calculated. Similarly, the corresponding result can be calculated through the first preset formula.

[0104] Finally, since the two are connected by the logical predicate and, the results of both sides can be directly multiplied to obtain the final selection rate, thereby improving the accuracy of cardinality estimation.

[0105] Another optional example is shown in Formula 3:

[0106] select*from mcv_lists_multi where(a=0and b=0)or(c=0and d=0) Formula 3

[0107] When the database obtains the database operation statement shown in Formula 3, there are three predicate filter conditions a=0, b=0, c=0, d=0 inside it, and a=0 and b=0 are connected by the AND logic predicate, c=0 and d=0 are also connected by the AND logic predicate, and the results are connected by the OR logic predicate. Among them, columns A, B, C, and D all belong to the same data table, and the extended statistical information mcv_lists_partial_s1(a,b,c,d) is established inside the data table.

[0108] When the database operation statement meets the preset conditions, there is the best preset extended statistical information, and then the selectivity is calculated through the extended statistical information mcv_lists_partial_s1(a,b,c,d).

[0109] The specific calculation process may be: firstly, the selectivity of a=0and b=0 is calculated based on the best preset extended statistical information, and the selectivity result is set as P(E). Similarly, the selectivity of c=0and d=0 is calculated, and the selectivity result is set as P(F). Since the two are connected by the logical predicate or, the result is substituted into the second preset formula for calculation:

[0110] P'(Eor F)=P(E)+P(F)-P(E)*P(F)

[0111] P'(E or F) is the final selection rate. Then, the total number of data rows in the data table corresponding to the database operation statement is obtained, and the cardinality estimate is calculated by multiplying the final selection rate by the total number of data rows.

[0112] In summary, when the logical predicates between the predicate filter conditions in the database operation statement include and only include OR logical predicates and AND logical predicates, it is also necessary to cyclically search for the best preset extended statistical information in the database operation statement, and select the corresponding calculation method in combination with the logical predicates between the predicate filter conditions to estimate the selectivity. For example, the two predicate filter conditions connected by the AND logical predicate are based on the best preset extended statistical information to calculate the selectivity of both parties and then multiply them to obtain the corresponding selectivity result; the two predicate filter conditions connected by the OR logical predicate are based on the best preset extended statistical information and substituted into the first preset formula to calculate the corresponding selectivity result. Finally, if the two groups of selectivities are still connected by the OR logical predicate, the final selectivity is calculated by the second preset formula.

[0113] Figure 2 FIG. 1 is a flow chart of a method for estimating the cardinality of a database operation statement according to another embodiment of the present invention. Figure 2 As shown, the cardinality estimation method of the database operation statement at least includes the following steps S201 to S212.

[0114] Step S201, obtaining predicate filtering conditions in a database operation statement.

[0115] Step S202, in the cardinality estimation stage, determines whether the database operation statement meets the preset conditions.

[0116] Step S203: if the result of step S202 is yes, it is determined whether all predicate filtering conditions are connected by an OR logical predicate.

[0117] Step S204: if the result of step S203 is yes, search for the best preset extended statistical information in the predicate filtering condition in the database operation statement.

[0118] Step S205 , estimating the best selection rate corresponding to the predicate filtering condition covered by the best preset extended statistical information by using a first preset formula.

[0119] Step S206, determining whether this cycle is the first cycle.

[0120] Step S207, when the judgment in step S206 is no, the stage selection rate obtained in the previous cycle and the best selection rate obtained in this cycle are substituted into the second preset formula to calculate the stage selection rate corresponding to this cycle.

[0121] Step S208, when the judgment in step S206 is yes or step S207 is completed, it is determined whether there are any remaining unevaluated predicate filtering conditions.

[0122] Step S209, if the result of step S208 is yes, it is determined whether there is any target column covered by the preset extended statistical information in the target column corresponding to the unevaluated predicate filtering condition. If the result of step S209 is yes, then step S204 is continued.

[0123] Step S210 , when the judgment result of either step S202 or step S209 is no, the remaining unevaluated predicate filter conditions are estimated according to the single-column statistical information to obtain the single-column selectivity corresponding to each column of the unevaluated predicate filter conditions.

[0124] Step S211, substituting the stage selection rate and the single column selection rate into a second preset formula to calculate a final selection rate.

[0125] Step S212, when the judgment in step S208 is no or step S211 is completed, the cardinality estimation value corresponding to the database operation statement is calculated according to the final selection rate.

[0126] This method can improve the cardinality estimation algorithm, thereby improving the accuracy of cardinality estimation of tables under single-table multi-predicate filtering conditions. As the accuracy of cardinality estimation of tables improves, the optimizer can select a better execution plan and improve query efficiency.

[0127] In order to clearly illustrate the execution process of the solution of the present invention, a specific example is proposed as shown in Formula 4:

[0128] select * from mcv_lists_multi where a=0 or b=0 or c=0 or d=0 or e=0 or f=0 Formula 4

[0129] It is assumed that six columns a, b, c, d, e, and f exist in the mcv_lists_multi table, and two extended statistics mcv_lists_multi_s1 (a, b, c) and mcv_lists_multi_s2 (d, e) are pre-created in the data table.

[0130] Specifically, firstly, step S201 is executed: when the database receives the SQL statement as shown in Formula 4, the predicate filtering conditions therein are obtained to form a predicate filtering condition list, including a=0, b=0, c=0, d=0, e=0, f=0;

[0131] Then, step S202 is executed: since the six columns a, b, c, d, e, and f all belong to the mcv_lists_multi table, and there are two extended statistics mcv_lists_multi_s1(a, b, c) and mcv_lists_multi_s2(d, e), and the predicate filtering condition is that six are greater than the preset threshold, it is determined that the preset condition is met;

[0132] If the judgment in step S202 is yes, step S203 is executed: after judging that the predicate filtering conditions are all connected with the logical predicate, the judgment in step S203 is yes;

[0133] If the result of step S203 is yes, step S204 is executed: in the first cycle, the best preset extended statistical information is determined to be mcv_lists_multi_s1(a, b, c) through searching;

[0134] Execute step S205: estimate the selectivity of the three columns a, b, and c as the stage selectivity by using the first preset formula;

[0135] Execute step S206: Since this cycle is the first cycle, the determination is yes;

[0136] If the judgment in step S206 is yes, step S208 is executed: after step S208, it is found that there are remaining unevaluated predicate filter conditions d, e, and f;

[0137] Execute step S209: Since the database still contains extended statistical information mcv_lists_multi_s2(d,e), and covers columns d and e, it is determined to be yes;

[0138] If the result of step S209 is yes, continue to step S204: in the second cycle, after searching, determine that the best preset extended statistical information is mcv_lists_multi_s2(d,e);

[0139] Execute step S205: estimate the selectivity of columns d and e by using a first preset formula to obtain an optimal selectivity;

[0140] Execute step S206: Since this cycle is not the first cycle, the determination is no;

[0141] If the judgment in step S206 is no, execute step S207: substitute the stage selection rate obtained by the first round of calculation into the second preset formula to calculate the stage selection rate corresponding to the second round of circulation;

[0142] Execute step S208: After step S208, it is found that there are remaining unevaluated predicate filter conditions f.

[0143] Execute step S209: Since the extended statistical information mcv_lists_multi_s2(d,e) does not exist in the database, the determination is no;

[0144] If the result of step S209 is negative, step S210 is executed: a single column selection rate of column f is calculated;

[0145] Execute step S211: substitute the stage selectivity obtained by the second round of calculation and the single column selectivity of column f into the second preset formula to calculate the final selectivity.

[0146] Execute step S212: obtain the total number of data rows in the mcv_lists_multi table, and multiply the final selection rate by the total number of data rows to obtain a cardinality estimation value.

[0147] Finally, in actual use, the cardinality estimation result of the SQL statement shown in Formula 4 before applying the solution of the present invention is 1 row, while the actual result is 102 rows, which is quite different; after applying the solution of the present invention, the cardinality estimation result is 102 rows, while the actual result is 102 rows, which is the same.

[0148] In some other optional embodiments, when faced with the situation that the logical predicates between the predicate filter conditions in the database operation statement only include or logical predicates and and logical predicates, the technical personnel in this field can also use the method of the present invention when calculating the or logical predicate; when faced with and logical predicates and the predicate filter conditions connected with the logical predicates have preset extended statistical information, the selectivity of the predicate filter conditions is calculated based on the extended statistical information, and the results are multiplied. In this way, the correlation between different data columns is considered to the greatest extent to perform cardinality estimation, and the accuracy of cardinality estimation is improved.

[0149] The flow chart provided by the present embodiment is not intended to indicate that the operation of the method will be performed in any particular order, or that all operations of the method are included in all every case. In addition, the method may include additional operations. Within the scope of the technical thinking provided by the present embodiment method, additional changes may be made to the above method.

[0150] It should be understood that in some embodiments, each part can be implemented by hardware, software, firmware or a combination thereof. In the above embodiments, multiple steps or methods can be implemented by software or firmware stored in a memory and executed by a suitable instruction execution system.

[0151] This embodiment also provides a computer program product 10 , a computer readable storage medium 20 , and a computer device 30 . Figure 3 is a schematic diagram of a computer program product 10 according to an embodiment of the present invention, Figure 4 is a schematic diagram of a computer-readable storage medium 20 according to an embodiment of the present invention, Figure 5 is a schematic diagram of a computer device 30 according to an embodiment of the present invention. The computer program product 10 includes a computer program 11, which implements the steps of any of the above-mentioned methods for estimating the cardinality of database operation statements when executed by a processor 32. The computer-readable storage medium 20 stores the above-mentioned computer program 11, which implements the steps of any of the above-mentioned methods for estimating the cardinality of database operation statements when executed by the processor 32. The computer device 30 may include a memory 31, a processor 32, and the computer program 11 stored in the memory 31 and running on the processor 32.

[0152] The computer program 11 for performing the operation of the present invention may be an assembly instruction, an instruction set architecture (ISA) instruction, a machine instruction, a machine-related instruction, a microcode, a firmware instruction, a state setting data, a configuration data of an integrated circuit, or a source code or an object code written in any combination of one or more programming languages ​​and process programming languages. The computer program 11 may be executed entirely on the user's computer, partially on the user's computer, as an independent software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the latter case, the remote computer may be connected to the user's computer via any type of network (including a local area network (LAN) or a wide area network (WAN), or may be connected to an external computer (e.g., using an Internet service provider via the Internet). In some embodiments, in order to perform various aspects of the present invention, an electronic circuit including, for example, a programmable logic circuit, a field programmable gate array (FPGA) or a programmable logic array (PLA) may execute computer-readable program instructions by utilizing the state information of the computer-readable program instructions to personalize the electronic circuit.

[0153] For the purposes of the description of the present embodiment, the computer program product 10 is a related product that includes the computer program 11. For the purposes of the description of the present embodiment, the computer readable storage medium 20 is a tangible device capable of retaining and storing the computer program 11, which may be any device that can contain, store, communicate, propagate or transmit the program 11 for use with or in conjunction with an instruction execution system, device or apparatus. More specific examples (a non-exhaustive list) of the computer readable storage medium 20 include the following: a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), a static random access memory (SRAM), a portable compact disk read-only memory (CD-ROM), a digital versatile disk (DVD), a memory stick, a floppy disk, a mechanical encoding device, and any suitable combination of the foregoing.

[0154] The computer device 30 may be, for example, a server, a desktop computer, a notebook computer, a tablet computer, or a smart phone. In some examples, the computer device 30 may be a cloud computing node. The computer device 30 may be described in the general context of computer system executable instructions (such as program modules) executed by a computer system. Typically, a program module may include routines, programs, target programs, components, logic, data structures, etc. that perform specific tasks or implement specific abstract data types. The computer device 30 may be implemented in a distributed cloud computing environment where remote processing devices linked through a communication network perform tasks. In a distributed cloud computing environment, program modules may be located on a local or remote computing system storage medium including a storage device.

[0155] The computer device 30 may include a processor 32 adapted to execute stored instructions, and a memory 31 providing temporary storage space for the operation of the instructions during operation. The processor 32 may be a single-core processor, a multi-core processor, a computing cluster, or any number of other configurations. The memory 31 may include a random access memory (RAM), a read-only memory, a flash memory, or any other suitable storage system.

[0156] The computer device 30 may also include a network adapter / interface and an input / output (I / O) interface. The I / O interface allows data to be input and output with external devices that may be connected to the computer device. The network adapter / interface may provide communication between the computer device and a network, which is typically shown as a communication network.

[0157] At this point, those skilled in the art should recognize that, although multiple exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications that conform to the principles of the present invention can still be directly determined or derived based on the content disclosed in the present invention without departing from the spirit and scope of the present invention. Therefore, the scope of the present invention should be understood and recognized as covering all these other variations or modifications.

Claims

1. A method for estimating the cardinality of a database operation statement, comprising: Obtaining the database operation statement; In the cardinality estimation stage, it is determined whether the database operation statement satisfies a preset condition, wherein the preset condition is a condition required for using preset extended statistical information, and the preset extended statistical information is used to record statistical information corresponding to a plurality of target columns having correlation in the data table; In the case where the database operation statement meets the preset conditions, determining whether the predicate filtering conditions in the database operation statement are all connected by an OR logical predicate; If so, the best preset extended statistical information in the database operation statement is searched cyclically to estimate the selectivity, wherein the best preset extended statistical information is the preset extended statistical information that covers the largest number of target columns.

2. The method for estimating the cardinality of database operation statements according to claim 1, wherein: The step of cyclically searching for the best preset extended statistical information in the database operation statement to estimate the selectivity includes: Searching for the optimal preset extended statistical information in the predicate filtering condition in the database operation statement; estimating the best selection rate corresponding to the predicate filtering condition covered by the best preset extended statistical information by means of a first preset formula, wherein the first preset formula is used to correct and calculate the best selection rate corresponding to the predicate filtering condition connected by an OR logical predicate; Determine whether there are any remaining unevaluated predicate filter conditions; In the case where the unevaluated predicate filtering condition exists, determining whether the target column corresponding to the unevaluated predicate filtering condition still has a target column covered by the preset extended statistical information; If yes, continue to execute the step of searching the best preset extended statistical information in the predicate filtering condition in the database operation statement.

3. The method for estimating the cardinality of database operation statements according to claim 2, wherein: After the step of estimating the best selection rate corresponding to the predicate filtering condition covered by the best preset extended statistical information by using the first preset formula, the step further includes: Determine whether this cycle is the first cycle; If so, executing the step of determining whether there are any remaining unevaluated predicate filter conditions; When this cycle is not the first cycle, the stage selection rate obtained in the previous cycle and the optimal selection rate obtained in this cycle are substituted into the second preset formula to calculate the stage selection rate corresponding to this cycle. The second preset formula is used to calculate the result after the two groups of selection rates are connected by the OR logical predicate.

4. The method for estimating the cardinality of database operation statements according to claim 3, wherein: The first preset formula includes: P(A or B)=sel_A+sel_B-(sel_ext+sel_simple-sel_extbase) The P(A or B) represents the optimal selection rate obtained by correcting and calculating the two columns A and B after being connected by the OR logical predicate, the sel_A represents the selection rate of data column A calculated based on the independence assumption, the sel_B represents the selection rate of data column B calculated based on the independence assumption, the sel_ext is the selection rate of data column A and data column B calculated based on the optimal preset extended statistical information, the sel_simple is the selection rate of data column A and data column B calculated based on single-column statistical information, and the sel_extbase is the selection rate of data column A and data column B calculated based on the optimal preset extended statistical information but without considering the correlation; The second preset formula includes: P'(A or B)=P(A)+P(B)-P(A)*P(B) The P'(A or B) represents the result of the selectivity P(A) and the selectivity P(B) being connected by an OR logical predicate.

5. The method for estimating the cardinality of database operation statements according to claim 4, wherein: After the step of determining whether there is a target column covered by the preset extended statistical information in the target column corresponding to the unevaluated predicate filtering condition, the following step further includes: When it is determined that there is no target column covered by the preset extended statistical information in the target column corresponding to the unestimated predicate filtering condition, estimating the unestimated predicate filtering condition according to the single-column statistical information to obtain a single-column selectivity corresponding to the unestimated predicate filtering condition in each column; Substituting the stage selectivity and the single column selectivity into the second preset formula to calculate a final selectivity; Obtain the total number of data rows in the data table corresponding to the database operation statement; The cardinality estimation value is calculated by multiplying the final selection rate by the total number of data rows.

6. The method for estimating the cardinality of database operation statements according to claim 4, wherein: After the step of determining whether the database operation statement meets the preset condition, the following step is further included: When the database operation statement does not satisfy the preset condition, each column in the predicate filtering condition is estimated according to single column statistical information to obtain a single column selection rate corresponding to each column; Substituting the estimated single-column selectivity into the second preset formula for calculation to obtain a final selectivity corresponding to the database operation statement; The cardinality estimation value corresponding to the database operation statement is calculated according to the final selection rate.

7. The method for estimating the cardinality of database operation statements according to claim 1, wherein: The step of determining whether the database operation statement meets the preset condition comprises: Obtaining the predicate filtering condition in the database operation statement; Determine whether the predicate filtering conditions all belong to the same data table; If yes, determining whether the preset extended statistical information exists in the data table; In the case where the preset extended statistical information exists in the data table, determining whether the number of the predicate filtering conditions is greater than a preset threshold; When the number of the predicate filtering conditions is greater than the preset threshold, it is determined that the database operation statement meets the preset condition.

8. The method for estimating the cardinality of database operation statements according to claim 1, wherein: After the step of determining whether all predicate filtering conditions in the database operation statement are connected by an OR logical predicate, the following step further comprises: In the case where the logical predicates between the predicate filtering conditions in the database operation statement include and only include OR logical predicates and AND logical predicates, the best preset extended statistical information in the database operation statement is cyclically searched, and the selection rate is estimated by a corresponding calculation method in combination with the logical predicates between the predicate filtering conditions.

9. A computer program product, comprising a computer program, which, when executed by a processor, implements the steps of the cardinality estimation method for database operation statements according to any one of claims 1 to 8.

10. A computer device comprising a memory, a processor, and a machine executable program stored in the memory and running on the processor, wherein the processor implements the steps of the cardinality estimation method for database operation statements according to any one of claims 1 to 8 when executing the machine executable program.

Images