Cardinality estimation method of database operation statements and related products

Abstract

The present invention provides a cardinality estimation method for database operation statements and related products. The above method includes: obtaining a database operation statement; parsing the database operation statement to obtain a predicate filter condition corresponding to the database operation statement; judging whether the comparison objects on both sides of the operator in the predicate filter condition meet the preset conditions, the preset conditions are the conditions required for calculating the selectivity using statistical information; if so, selecting the corresponding statistical information according to the specific type of the comparison object to calculate the selectivity corresponding to the predicate filter condition. Through this method, the cardinality estimation algorithm can be improved, thereby improving the accuracy of the cardinality estimation, and 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.

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 there are comparison operations between multiple attribute columns in a single table being queried, such as select*from t where a>b;, since the correlation between the two attribute columns cannot be determined, current databases in the industry cannot perform cardinality estimation based on existing statistical information. Usually, databases use default values ​​when calculating selectivity, and cannot evaluate the correlation between multiple columns. Therefore, there is no good calculation algorithm for this scenario, and there is a very large error in the selectivity calculation, which leads to extremely inaccurate cardinality estimation in this scenario, causing the optimizer to generate a poor execution plan, affecting database query efficiency. 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] Another further object of the present invention is to maximize the selectivity of calculation by extending statistical information or expression statistical information to improve the estimation accuracy.

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

[0007] Get database operation statements;

[0008] Parse the database operation statement to obtain the predicate filtering condition corresponding to the database operation statement;

[0009] Determine whether the comparison objects on both sides of the operator in the predicate filter condition meet the preset conditions, which are the conditions required to calculate the selectivity using statistical information;

[0010] If so, the corresponding statistical information is selected according to the specific type of the comparison object to calculate the selectivity corresponding to the predicate filter condition.

[0011] Optionally, the step of judging whether the comparison objects on both sides of the comparison predicate in the predicate filtering condition meet the preset conditions includes:

[0012] When the comparison objects on both sides of the operator are data columns, determine whether the data columns belong to the same data table;

[0013] In the case where the data columns belong to the same data table, it is determined whether there is preset extended statistical information covering the data columns in the data table, where the preset extended statistical information is used to record statistical information corresponding to multiple target columns that are correlated in the data table;

[0014] If so, it is determined that the comparison object meets the preset conditions.

[0015] Optionally, the step of selecting corresponding statistical information according to the specific type of the comparison object to calculate the selectivity corresponding to the predicate filtering condition includes:

[0016] When the comparison objects on both sides of the operator are data columns and meet the preset conditions, the selectivity corresponding to the predicate filtering condition is calculated according to the preset extended statistical information.

[0017] Optionally, the step of judging whether the comparison objects on both sides of the comparison predicate in the predicate filtering condition meet the preset conditions includes:

[0018] When at least one of the comparison objects on both sides of the operator is an expression, it is determined whether the data columns involved in the comparison objects belong to the same data table;

[0019] In the case where the data columns belong to the same data table, it is determined whether there is preset expression statistical information corresponding to the expression in the data table, where the preset expression statistical information is used to record statistical information corresponding to the expression;

[0020] If so, it is determined that the comparison object meets the preset conditions.

[0021] Optionally, the step of selecting corresponding statistical information according to the specific type of the comparison object to calculate the selectivity corresponding to the predicate filtering condition includes:

[0022] When at least one of the comparison objects on both sides of the operator is an expression and meets the preset conditions, the selectivity corresponding to the predicate filtering condition is calculated according to the preset expression statistical information.

[0023] Optionally, the step of judging whether the comparison objects on both sides of the comparison predicate in the predicate filtering condition meet the preset conditions includes:

[0024] When one of the comparison objects on both sides of the operator is an expression and the other is a single data column, determine whether the data columns involved in the comparison objects belong to the same data table;

[0025] In the case where the data columns belong to the same data table, it is determined whether there is preset expression statistics information containing both an expression and a single data column in the data table;

[0026] If so, it is determined that the comparison object meets the preset conditions.

[0027] Optionally, after the step of determining whether the comparison objects on both sides of the operator in the predicate filtering condition meet the preset conditions, the following step is further performed:

[0028] When the comparison object does not meet the preset condition, a preset default value is determined according to the type of the predicate filtering condition as the selection rate corresponding to the predicate filtering condition.

[0029] Optionally, after the step of selecting corresponding statistical information according to the specific type of the comparison object to calculate the selectivity corresponding to the predicate filtering condition, the step further includes:

[0030] In the case of multiple predicate filter conditions, the corresponding calculation formula is selected according to the logical predicates between the predicate filter conditions;

[0031] Substitute the selectivity corresponding to each predicate filter condition into the calculation formula to obtain the final selectivity corresponding to the database operation statement;

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

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

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

[0035] The cardinality estimation method of the database operation statement of the present invention first obtains the database operation statement; parses the database operation statement to obtain the predicate filtering condition corresponding to the database operation statement; determines whether the comparison objects on both sides of the operator in the predicate filtering condition meet the preset conditions, and the preset conditions are the conditions required for calculating the selectivity using statistical information; if so, selects the corresponding statistical information according to the specific type of the comparison object to calculate the selectivity corresponding to the predicate filtering condition. Through this method, the cardinality estimation algorithm can be improved, thereby improving the accuracy of the cardinality estimation, and 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.

[0036] Furthermore, the cardinality estimation method of the database operation statement of the present invention, when the comparison objects on both sides of the operator are data columns, when the comparison objects on both sides of the operator are data columns and meet the preset conditions, the selectivity corresponding to the predicate filtering condition is calculated according to the preset extended statistical information; when at least one of the comparison objects on both sides of the operator is an expression and meets the preset conditions, the selectivity corresponding to the predicate filtering condition is calculated according to the preset expression statistical information; when the comparison objects do not meet the preset conditions, a preset default value is determined according to the type of the predicate filtering condition as the selectivity corresponding to the predicate filtering condition. Different calculation methods are selected for different predicate filtering conditions, thereby improving the accuracy of the selectivity calculation.

[0037] 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

[0038] 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:

[0039] 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;

[0040] 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;

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

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

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

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

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

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

[0047] 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 S103.

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

[0049] Step S102, parse the database operation statement to obtain the predicate filter condition corresponding to the database operation statement. In the SQL statement, the 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., which can select the row data that meets the requirements from the table according to specific conditions. Among them, the expression form of the predicate filter condition is generally var1 OP var2, where var1 and var2 are the objects involved in the comparison, and OP is the operator. var1 and var2 may be a single data column, a fixed value or an expression, and the operator is generally a comparison predicate, such as = (equal to), > (greater than), < (less than), > = (greater than or equal to), < = (less than or equal to) and <> or! = (not equal to).

[0050] Step S103, determining whether the comparison objects on both sides of the operator in the predicate filtering condition meet the preset conditions.

[0051] Step S104, if the judgment in step S103 is yes, select the corresponding statistical information according to the specific type of the comparison object to calculate the selectivity corresponding to the predicate filter condition. The selectivity refers to the ratio of the number of records that meet the predicate filter condition to the total number of records. It is a key indicator in database query optimization, which is used to estimate the size of the query result set and help the optimizer select a more efficient query execution plan. For example, in a table with 100 records, if 20 records meet a certain predicate filter condition, then the selectivity of the predicate filter condition is 20 / 100=0.2.

[0052] In some optional embodiments, the predicate filtering condition may have a situation where the comparison objects on both sides of the operator are data columns, for example, data column A>data column B, that is, var1 and var2 are both data columns. In the face of such predicate filtering conditions, the step of determining whether the comparison objects on both sides of the predicate in the predicate filtering condition meet the preset conditions in the method of the present invention can generally include: when the comparison objects on both sides of the operator are data columns, determining whether the data columns belong to the same data table; when the data columns belong to the same data table, determining whether there is preset extended statistical information covering the data columns in the data table; if so, determining that the comparison object meets the preset conditions.

[0053] And when the comparison objects on both sides of the operator are data columns and meet the preset conditions, the step of selecting corresponding statistical information to calculate the selectivity corresponding to the predicate filtering condition according to the specific type of the comparison object can generally include: calculating the selectivity corresponding to the predicate filtering condition according to preset extended statistical information.

[0054] The preset extended statistical information is used to record the statistical information corresponding to multiple target columns with correlation in the data table. It is a kind 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 characteristics. The selectivity calculated based on the statistical information in the preset extended statistical information is more accurate than that of the traditional single-column statistical information.

[0055] Through this method, the cardinality estimation algorithm can be improved, thereby improving the accuracy of cardinality estimation. As the accuracy of the cardinality estimation of the table improves, the optimizer can select a better execution plan to improve the efficiency of the query.

[0056] In some other optional embodiments, the predicate filtering condition may also have a situation where one or more of the comparison objects on both sides of the operator are expressions, that is, at least one of var1 and var2 is an expression, or both are expressions or one is an expression and the other is a single data column. The expression generally includes arithmetic expressions and function expressions, such as price*(1-discount_rate)<100 (query discounted price is less than 100). In the face of such predicate filtering conditions, the step of judging whether the comparison objects on both sides of the predicate in the predicate filtering condition meet the preset conditions in the method of the present invention can generally include: when at least one of the comparison objects on both sides of the operator is an expression, judging whether the data columns involved in the comparison objects belong to the same data table; when the data columns belong to the same data table, judging whether there is preset expression statistics corresponding to the expression in the data table, and the preset expression statistics are used to record the statistics corresponding to the expression; if so, judging that the comparison object meets the preset conditions. Wherein, when both are expressions, it is necessary to judge whether there is preset expression statistics containing both expressions in the data table, and if so, judging that the comparison object meets the preset conditions. And when at least one of the comparison objects on both sides of the operator is an expression, the step of selecting corresponding statistical information according to the specific type of the comparison object to calculate the selection rate corresponding to the predicate filtering condition can generally include: when at least one of the comparison objects on both sides of the operator is an expression and meets the preset conditions, calculating the selection rate corresponding to the predicate filtering condition according to the preset expression statistical information.

[0057] Among them, expression statistics are information used in database systems to describe and quantify the data characteristics of the results of expressions (such as arithmetic expressions, function expressions, etc.). It is different from traditional column statistics. Traditional column statistics mainly focus on the data distribution and cardinality of the columns in the table, while expression statistics focus on the statistics of the results after evaluating the expression. For example, for the expression price*(1-discount) (the actual selling price after the price is multiplied by the discount), the expression statistics may include the minimum, maximum, average, number of different values ​​(cardinality) and distribution histogram of the expression result.

[0058] Optionally, the step of judging whether the comparison objects on both sides of the comparison predicate in the predicate filtering condition meet the preset conditions can also generally include: when one side of the comparison objects on both sides of the operator is an expression and the other side is a single data column, judging whether the data columns involved in the comparison object belong to the same data table; when the data columns belong to the same data table, judging whether there is preset expression statistics information in the data table that contains both the expression and the single data column; if so, judging that the comparison object meets the preset conditions. Among them, an example of the case where one side of the comparison objects on both sides of the operator is an expression and the other side is a single data column is as follows: cpu_usage>calculate_threshold(), where cpu_usage represents the CPU usage, calculate_threshold() is a function expression, and its return value is not fixed, but will be dynamically calculated according to the actual situation of the server. If faced with such a predicate filtering condition, it is necessary to judge whether there is preset expression statistics information in the data table that contains both the expression and the single data column, and if so, calculate its corresponding selectivity according to the preset expression statistics, thereby improving the accuracy of the selectivity estimation.

[0059] In some other optional embodiments, after the step of judging whether the comparison objects on both sides of the operator in the predicate filter condition meet the preset conditions, the following step may be generally included: if the comparison object does not meet the preset conditions, a preset default value is determined as the selection rate corresponding to the predicate filter condition according to the type of the predicate filter condition. For example, for equal value comparison, a default value such as 0.005 may generally be used, while for range comparison, a default value such as 1 / 3 may generally be used. Those skilled in the art may set specific values ​​of the default values ​​corresponding to different comparison methods according to actual conditions.

[0060] In addition, in some database operation statements, there will be operations of filtering and screening multiple predicate filter conditions together, and each predicate filter condition is generally connected by a logical predicate, such as an AND logical predicate and an OR logical predicate. Therefore, optionally, after selecting the corresponding statistical information according to the specific type of the comparison object to calculate the selectivity corresponding to the predicate filter condition, it can generally include: in the case of multiple predicate filter conditions, select the corresponding calculation formula according to the logical predicates between the predicate filter conditions; substitute the selectivity corresponding to each predicate filter condition into the calculation formula to calculate the final selectivity corresponding to the database operation statement; calculate the cardinality estimation value corresponding to the database operation statement according to the final selectivity. The calculation formula corresponding to the AND logical predicate (AND) is generally: P(A and B) = P(A) * P(B); the calculation formula corresponding to the OR logical predicate (OR) is generally: P(A or B) = P(A) + P(B) - P(A) * P(B). Those skilled in the art can determine the calculation formula corresponding to the logical predicate according to actual conditions.

[0061] The step of calculating the cardinality estimation value corresponding to the database operation statement according to the final selection rate generally includes: obtaining the total number of data rows in the data table corresponding to the database operation statement; and multiplying the final selection rate by the total number of data rows to obtain the cardinality estimation value.

[0062] In some optional embodiments, the predicate filter condition may have a situation where the comparison objects on both sides of the operator are data columns, for example, data column A>data column B. In the face of such a predicate filter condition, 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 S208.

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

[0064] Step S202 : when the comparison objects on both sides of the operator are data columns, it is determined whether the data columns belong to the same data table.

[0065] Step S203, when the judgment in step S202 is yes, it is determined whether there is preset extended statistical information covering the data column in the data table.

[0066] Step S204: when the judgment in step S203 is yes, the selectivity corresponding to the predicate filtering condition is calculated according to the preset extended statistical information.

[0067] Step S205 , when the judgment in either step S202 or step S203 is no, a preset default value is determined according to the type of the predicate filtering condition as the selection rate corresponding to the predicate filtering condition.

[0068] Step S206: When there are multiple predicate filtering conditions, select a corresponding calculation formula according to the logical predicates between the predicate filtering conditions.

[0069] Step S207: Substitute the selectivity corresponding to each predicate filtering condition into the calculation formula to obtain the final selectivity corresponding to the database operation statement.

[0070] Step S208, calculating the estimated cardinality value corresponding to the database operation statement according to the final selection rate.

[0071] When the comparison objects on both sides of the operator are data columns, this method can improve the accuracy of selectivity calculation by presetting extended statistical information, thereby improving the accuracy of cardinality estimation, allowing the optimizer to select a better execution plan and improve query efficiency.

[0072] In order to clearly illustrate the execution process of this embodiment, a specific example is provided as shown in Formula 1:

[0073] select * from var_test where b = c and d =e Formula 1

[0074] It is assumed that there are five columns a, b, c, d, and e in the var_test table, and extended statistical information var_test_stats(b,c) and var_test_stats(d,e) are pre-created in the data table.

[0075] Specifically, firstly, step S201 is executed: when the database receives the SQL statement as shown in Formula 1, the predicate filtering conditions b=c and d=e therein are obtained;

[0076] Since the comparison objects on both sides of the operator (=) in the predicate filter condition b=c are both data columns (data column b and data column c), and the comparison objects on both sides of the operator (=) in the predicate filter condition d=e are both data columns (data column d and data column e), step S202 is executed: since columns b, c, d, and e all belong to the var_test table, step S202 is judged as yes;

[0077] If the result of step S202 is yes, step S203 is executed: since the extended statistical information var_test_stats(b,c) and var_test_stats(d,e) exist in the var_test table, it is determined that the preset condition is met;

[0078] If the judgment in step S203 is yes, execute step S204: calculate the selectivity corresponding to the predicate filtering condition according to the preset extended statistical information, wherein the method of calculating the selectivity according to the preset extended statistical information includes but is not limited to: calculating the selectivity using a joint histogram and calculating the selectivity using inter-column correlation, etc. Technical personnel in this field can select the corresponding calculation method according to the specific recording method of the preset extended statistical information.

[0079] Execute steps S206-S207: When there are multiple predicate filtering conditions, select the corresponding calculation formula according to the logical predicate between the predicate filtering conditions; in this embodiment, the logical predicate is and, so the selectivity corresponding to the two predicate filtering conditions b=c and d=e calculated in step S204 is multiplied to obtain the final selectivity.

[0080] Execute step S208: obtain the total number of data rows in the data table corresponding to the database operation statement; multiply the final selection rate by the total number of data rows to obtain a cardinality estimation value.

[0081] Finally, in actual use, the cardinality estimation result of the SQL statement shown in Formula 1 before applying the solution of the present invention is 5 rows, while the actual result is 334 rows, which is a large difference; after applying the solution of the present invention, the cardinality estimation result is 334 rows, which is the same.

[0082] In some other optional embodiments, the predicate filter condition may also have one or more comparison objects on both sides of the operator being expressions. In the case of such a predicate filter condition, Figure 3 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 3 As shown, the cardinality estimation method of the database operation statement at least includes the following steps S301 to S308.

[0083] Step S301, obtaining predicate filtering conditions in a database operation statement.

[0084] Step S302: when at least one of the comparison objects is an expression, determine whether the data columns involved in the comparison objects belong to the same data table.

[0085] Step S303, when the judgment in step S302 is yes, determine whether there is preset expression statistical information corresponding to the expression in the data table.

[0086] Step S304: when the judgment in step S303 is yes, the selectivity corresponding to the predicate filtering condition is calculated according to the preset expression statistical information.

[0087] Step S305 , when the judgment in either step S302 or step S303 is no, a preset default value is determined according to the type of the predicate filtering condition as the selection rate corresponding to the predicate filtering condition.

[0088] Step S306: When there are multiple predicate filtering conditions, select a corresponding calculation formula according to the logical predicates between the predicate filtering conditions.

[0089] Step S307: Substitute the selectivity corresponding to each predicate filtering condition into the calculation formula to obtain the final selectivity corresponding to the database operation statement.

[0090] Step S308, calculating the estimated cardinality value corresponding to the database operation statement according to the final selection rate.

[0091] When the comparison objects on both sides of the operator are data columns, this method can improve the accuracy of selectivity calculation by presetting extended statistical information, thereby improving the accuracy of cardinality estimation, allowing the optimizer to select a better execution plan and improve query efficiency.

[0092] In order to clearly illustrate the execution process of this embodiment, a specific example is provided as shown in Formula 2:

[0093] select * from products where price * (1 - discount_rate) < 100 orprice < 100 Formula 2

[0094] Assume that in a products table containing product information, there are price columns and discount_rate columns. To query products with discounted prices less than 100 and products with original prices less than 100, you can use the SQL statement shown in Formula 2 to filter. Among them, price*(1-discount_rate) is an arithmetic expression that calculates the discounted price of the product and then compares it with 100.

[0095] In addition, expression statistics products_stats(price*(1-discount_rate)) is pre-created in the data table. The expression statistics include the minimum value, maximum value, average value, number of different values ​​(cardinality), and distribution histogram of the expression result.

[0096] Specifically, first, step S301 is executed: when the database receives the SQL statement shown in Formula 2, the predicate filtering condition price*(1-discount_rate)<100 is obtained;

[0097] Since there are expressions in the comparison objects on both sides of the operator (<) in the predicate filter condition price*(1-discount_rate)<100, the judgment in step S302 is yes;

[0098] If the result of step S302 is yes, step S303 is executed: since the expression statistical information products_stats(price*(1-discount_rate)) exists in the products table, it is determined that the preset condition is met;

[0099] If the judgment in step S303 is yes, step S304 is executed: the selectivity corresponding to the predicate filtering condition is calculated according to the preset expression statistical information. The method of calculating the selectivity according to the preset expression statistical information includes but is not limited to: calculating the selectivity based on a histogram and calculating the selectivity based on expression statistics, and those skilled in the art can select the corresponding calculation method according to the specific recording method of the preset expression statistical information.

[0100] For the predicate filtering condition (price<100), the corresponding selection rate can be directly calculated.

[0101] Execute steps S306-S307: When there are multiple predicate filtering conditions, select the corresponding calculation formula according to the logical predicates between the predicate filtering conditions; in this embodiment, the logical predicate is or, so the selection rates corresponding to the two predicate filtering conditions price*(1-discount_rate)<100 and price<100 calculated in step S304 are substituted into the calculation formula P(A or B)=P(A)+P(B)-P(A)*P(B) to calculate the final selection rate.

[0102] Execute step S308: obtain the total number of data rows in the data table corresponding to the database operation statement; multiply the final selection rate by the total number of data rows to obtain a cardinality estimation value.

[0103] Finally, in actual use, the cardinality estimation result of the SQL statement shown in Formula 2 before applying the solution of the present invention is 6 rows, while the actual result is 268 rows, which is quite different; after applying the solution of the present invention, the cardinality estimation result is 268 rows, which is the same.

[0104] In addition, the method of the present invention is also applicable to the case where both sides of the operator are expressions or one side is an expression and the other side is a single data column.

[0105] An optional example is: In a server performance monitoring table, there is a cpu_usage (CPU usage) column. Suppose there is a function that can dynamically calculate a CPU usage threshold calculate_threshold() based on the current server load, time and other factors. Now to find the records whose CPU usage exceeds this dynamic threshold, the SQL statement used is as follows:

[0106] select * from server_monitoring where cpu_usage > calculate_threshold() Formula 3

[0107] It is assumed that the data table has pre-created expression statistics containing both the cpu_usage column and the expression calculate_threshold().

[0108] Specifically, first, step S301 is executed: when the database receives the SQL statement shown in Formula 3, the predicate filtering condition cpu_usage>calculate_threshold() is obtained;

[0109] Since there are expressions in the comparison objects on both sides of the operator (>) in the predicate filter condition cpu_usage>calculate_threshold(), the judgment in step S302 is yes;

[0110] If the result of step S302 is yes, step S303 is executed: since there is expression statistics information including both the cpu_usage column and the expression calculate_threshold() in the data table, it is determined that the preset condition is met;

[0111] If the result of the judgment in step S303 is yes, step S304 is executed: the selection rate corresponding to the predicate filtering condition is calculated according to the preset expression statistical information.

[0112] Since there are no multiple predicate filtering conditions in the SQL statement shown in Formula 3, the selectivity calculated in step S304 is used as the final selectivity. Then, the total number of data rows in the data table corresponding to the database operation statement is obtained; the final selectivity is multiplied by the total number of data rows to obtain a cardinality estimation value.

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

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

[0115] This embodiment also provides a computer program product 10 , a computer readable storage medium 20 , and a computer device 30 . Figure 4 is a schematic diagram of a computer program product 10 according to an embodiment of the present invention, Figure 5 is a schematic diagram of a computer-readable storage medium 20 according to an embodiment of the present invention, Figure 6 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.

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

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

[0118] 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 via 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.

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

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

[0121] 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; Parsing the database operation statement to obtain a predicate filtering condition corresponding to the database operation statement; Determine whether the comparison objects on both sides of the operator in the predicate filtering condition meet the preset conditions, where the preset conditions are the conditions required for calculating the selection rate using statistical information; If so, the selection rate corresponding to the predicate filtering condition is calculated by selecting corresponding statistical information according to the specific type of the comparison object.

2. The method for estimating the cardinality of database operation statements according to claim 1, wherein: The step of judging whether the comparison objects on both sides of the comparison predicate in the predicate filtering condition meet the preset conditions includes: In the case where the comparison objects on both sides of the operator are data columns, determining whether the data columns belong to the same data table; In the case where the data columns belong to the same data table, determining whether there is preset extended statistical information in the data table that covers the data columns, the preset extended statistical information being used to record statistical information corresponding to a plurality of target columns in the data table that are correlated; If so, it is determined that the comparison object meets the preset condition.

3. The method for estimating the cardinality of database operation statements according to claim 2, wherein: The step of selecting corresponding statistical information according to the specific type of the comparison object and calculating the selectivity corresponding to the predicate filtering condition comprises: In the case that the comparison objects on both sides of the operator are data columns and meet the preset condition, the selectivity corresponding to the predicate filtering condition is calculated according to the preset extended statistical information.

4. The method for estimating the cardinality of database operation statements according to claim 1, wherein: The step of judging whether the comparison objects on both sides of the comparison predicate in the predicate filtering condition meet the preset conditions includes: When at least one of the comparison objects on both sides of the operator is an expression, determining whether the data columns involved in the comparison objects belong to the same data table; In the case where the data columns belong to the same data table, determining whether there is preset expression statistical information corresponding to the expression in the data table, the preset expression statistical information being used to record statistical information corresponding to the expression; If so, it is determined that the comparison object meets the preset condition.

5. The method for estimating the cardinality of database operation statements according to claim 4, wherein: The step of selecting corresponding statistical information according to the specific type of the comparison object and calculating the selectivity corresponding to the predicate filtering condition comprises: When at least one of the comparison objects on both sides of the operator is an expression and meets the preset condition, the selection rate corresponding to the predicate filtering condition is calculated according to the preset expression statistical information.

6. The method for estimating the cardinality of database operation statements according to claim 4, wherein: The step of judging whether the comparison objects on both sides of the comparison predicate in the predicate filtering condition meet the preset conditions includes: In the case where one of the comparison objects on both sides of the operator is an expression and the other is a single data column, determining whether the data columns involved in the comparison objects belong to the same data table; In the case where the data columns belong to the same data table, determining whether there is preset expression statistical information in the data table that includes both the expression and the single data column; If so, it is determined that the comparison object meets the preset condition.

7. The method for estimating the cardinality of database operation statements according to claim 1, wherein: After the step of determining whether the comparison objects on both sides of the operator in the predicate filtering condition meet the preset conditions, the following step is further included: In the case that the comparison object does not meet the preset condition, a preset default value is determined according to the type of the predicate filtering condition as the selection rate corresponding to the predicate filtering condition.

8. The method for estimating the cardinality of database operation statements according to claim 1, wherein: After the step of selecting corresponding statistical information according to the specific type of the comparison object to calculate the selectivity corresponding to the predicate filtering condition, the following step further includes: In the case where there are multiple predicate filtering conditions, selecting a corresponding calculation formula according to the logical predicates between the predicate filtering conditions; Substituting the selectivity corresponding to each of the predicate filtering conditions into the calculation formula to calculate 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.

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