Cardinality estimation method and device, electronic equipment, storage medium and program product

Abstract

The embodiment of the invention provides a cardinal number estimation method and device, electronic equipment, a storage medium and a program product. Under the condition that correlation exists among multiple columns of the same data table, if a query statement comprises a target clause of a scalar and an array, the target clause of the scalar and the array is estimated; judging whether the target clauses are compatible with the function dependency list or the common value list according to the operators and the number of the target clauses, calculating the selection rate meeting the target clauses in the data table according to the function dependency degree of the columns when the target clauses are compatible with the function dependency list, and calculating the selection rate meeting the target clauses in the data table according to the function dependency degree of the columns when the target clauses are compatible with the common value list. And the selection rate meeting the target clause in the data table is calculated according to the common value selection rate, so that the accuracy of selection rate calculation is improved, and the accuracy of cardinality estimation is improved.

Description

Technical Field

[0001] The present application relates to the field of databases, and in particular to a cardinality estimation method, device, electronic device, storage medium and program product. Background Art

[0002] Cardinality estimation refers to the number of data rows that are expected to meet specific query conditions in a database query. Cardinality is crucial to determining the efficiency of the query plan and is the core link in database performance optimization. Cardinality estimation is based on selectivity. An accurate selectivity is conducive to the reliability of cardinality estimation, thereby helping to optimize database query performance.

[0003] When calculating the selectivity, the database will use the corresponding calculation method based on the expression type of the constraint in the query statement. When the constraint involves multiple columns of a table, you can create extended statistical information to clarify the correlation between multiple columns, avoid treating each column as independent, improve the accuracy of the selectivity calculation, and further improve the accuracy of the cardinality estimation.

[0004] However, currently, when the database uses extended statistical information to calculate selectivity, it only supports scenarios where simple clauses are processed. When the constraint condition is a more complex clause, such as scalarArrayOpExpr, it is impossible to perform logical judgment on whether it is compatible with extended statistical information types such as function dependency types and common value types. Therefore, the selectivity calculation can only rely on single-column statistical information, and cannot rely on extended statistical information, which affects the accuracy of the selectivity calculation. Summary of the invention

[0005] Embodiments of the present application provide a cardinality estimation method, device, electronic device, storage medium, and program product to improve the accuracy of selectivity calculation.

[0006] In a first aspect, an embodiment of the present application provides a cardinality estimation method, including:

[0007] In the case where there is a correlation between multiple columns of a data table, if the query statement includes a target clause, judging whether the target clause is compatible with an extended statistical information list according to an operator and a quantity of the target clause, the target clause includes an expression between a scalar and an array, and the extended statistical information list includes a function dependency list or a common value list;

[0008] When the target clause matches the functional dependency list, the selectivity of the data table that satisfies the target clause is calculated according to the functional dependencies between columns;

[0009] When the target clause matches the common value list, the selectivity of the items in the data table that satisfy the target clause is calculated according to the common value selectivity;

[0010] A cardinality is estimated according to the number of rows in the data table and the selectivity, where the cardinality is the number of rows in the data table that satisfy the target clause.

[0011] In a possible implementation, judging whether the target clause is compatible with the extended statistical information type according to the operator and the quantity of the target clause includes:

[0012] Determine whether the target clause satisfies a first preset condition, and if so, determine whether the target clause matches a functional dependency list;

[0013] The first preset condition includes:

[0014] The operators in the target clause include a first preset operator in the first operator, and / or a second operator; the first operator is used to compare the scalar with any element in the array, and the first preset operator includes any operator among equal, greater than, less than, greater than or equal to, and less than or equal to; the second operator is used to check whether the scalar exists in the array;

[0015] The target clause includes at least two columns;

[0016] The column identifier in the target clause is on the left side of the operator and the constant is on the right side of the operator;

[0017] In a possible implementation, judging whether the target clause is compatible with the extended statistical information list according to the operator and the quantity of the target clause includes:

[0018] determining whether the target clause satisfies a second preset condition, and if so, determining that the target clause is compatible with a common value list;

[0019] The second preset condition includes:

[0020] The target clause includes a second preset operator;

[0021] The target clause includes at least two columns;

[0022] The column identifier in the target clause is on the left side of the operator and the constant is on the right side of the operator;

[0023] The second preset operator includes any operator of equal to, not equal to, greater than, less than, greater than or equal to, and less than or equal to.

[0024] In a possible implementation manner, calculating the selectivity of the data table that satisfies the target clause according to the functional dependency between columns includes:

[0025] Obtaining a single-column selectivity of each sub-condition in the target clause, and obtaining a minimum selectivity from a plurality of the single-column selectivities;

[0026] Calculate a first product of the minimum selectivity and the function dependency, and a second product of the function independence and the single-column selectivity of each sub-condition, wherein the function independence and the function dependency add up to one;

[0027] The selectivity of the data table that satisfies the target clause is determined according to the sum of the first product and the second product.

[0028] In a possible implementation manner, the selecting and calculating the selectivity of the data table that satisfies the target clause according to the common values ​​includes:

[0029] Obtaining a first value in the array that appears in the common value list, wherein the common value list includes values ​​in the data table whose frequency is greater than a preset frequency;

[0030] Matching the bit marks corresponding to the first value in the bitmap with an identifier, the total number of bits in the bitmap is the same as the total number of values ​​in the array, and each bit in the bitmap corresponds to a value in the array;

[0031] Determine a common value selection rate according to a ratio of the number of bits of the marking matching identifier in the bitmap to the total number of values ​​in the common value list;

[0032] Determining a selection rate of uncommon values ​​according to a ratio of the number of bits in the bitmap that are not marked with a matching identifier to the total number of values ​​in the common value list;

[0033] The selectivity of the target clause is determined according to the sum of the common value selectivity and the uncommon value selectivity.

[0034] In a possible implementation manner, the number of the target clauses is multiple;

[0035] The estimating the cardinality according to the number of rows in the data table and the selectivity includes:

[0036] Determining a final selectivity of the data table according to the product of the selectivity of each target clause;

[0037] Estimate the cardinality based on the final selectivity and number of rows of the data table in question.

[0038] In a second aspect, the present application provides a cardinality estimation device, comprising:

[0039] a judgment module, configured to, when there is a correlation between multiple columns of a data table, if a query statement includes a target clause, judge whether the target clause is compatible with an extended statistical information list according to an operator and a quantity of the target clause, the target clause including an expression between a scalar and an array, and the extended statistical information list including a function dependency list or a common value list;

[0040] A first calculation module, configured to calculate a selectivity of the target clause in the data table according to the functional dependencies between columns when the target clause is compatible with the functional dependency list;

[0041] A second calculation module, configured to calculate a selectivity of the data table satisfying the target clause according to the common value selectivity when the target clause is compatible with the common value list;

[0042] An estimation module is used to estimate a cardinality according to the number of rows in the data table and the selectivity, where the cardinality is the number of rows in the data table that satisfy the target clause.

[0043] In a third aspect, an embodiment of the present application provides an electronic device, including: a memory, a processor;

[0044] The memory stores computer-executable instructions;

[0045] The processor executes the computer-executable instructions stored in the memory, so that the processor executes the above first aspect and / or various possible implementations of the first aspect.

[0046] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium, in which computer-executable instructions are stored. When the computer-executable instructions are executed by a processor, they are used to implement the first aspect above and / or various possible implementations of the first aspect.

[0047] In a fifth aspect, an embodiment of the present application provides a computer program product, including a computer program, which, when executed by a processor, implements the above first aspect and / or various possible implementation methods of the first aspect.

[0048] The cardinality estimation method, device, electronic device, storage medium and program product provided by the embodiments of the present application, when there is a correlation between multiple columns of a data table, if the query clause includes a target clause of a scalar and an array, it is judged whether the target clause is compatible with the function dependency list or the common value list according to the operator and quantity of the target clause; when the target clause is compatible with the function dependency list, the selectivity of the data table that satisfies the target clause is calculated according to the functional dependency between the columns; when the target clause matches the common value list, the selectivity of the data table that satisfies the target clause is calculated according to the common value selectivity, thereby improving the accuracy of the selectivity calculation, and then improving the accuracy of the cardinality estimation. BRIEF DESCRIPTION OF THE DRAWINGS

[0049] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present application and, together with the description, serve to explain the principles of the present application.

[0050] Figure 1 A schematic diagram of a flow chart of a cardinality estimation method provided in one embodiment of the present application;

[0051] Figure 2 A schematic diagram of a flow chart of a cardinality estimation method provided in another embodiment of the present application;

[0052] Figure 3 A flowchart of a cardinality estimation method provided in yet another embodiment of the present application;

[0053] Figure 4 A schematic diagram of the structure of a cardinality estimation device provided in an embodiment of the present application;

[0054] Figure 5 A schematic diagram of the structure of the electronic device provided in this application.

[0055] The above drawings have shown clear embodiments of the present application, which will be described in more detail later. These drawings and text descriptions are not intended to limit the scope of the present application in any way, but to illustrate the concept of the present application to those skilled in the art by referring to specific embodiments. DETAILED DESCRIPTION

[0056] Exemplary embodiments will be described in detail herein, examples of which are shown in the accompanying drawings. When the following description refers to the drawings, the same numbers in different drawings represent the same or similar elements unless otherwise indicated. The implementations described in the following exemplary embodiments do not represent all implementations consistent with the present application. Instead, they are merely examples of devices and methods consistent with some aspects of the present application as detailed in the appended claims.

[0057] When SQL (Structured Query Language) constraints involve multiple columns in the same data table, by creating extended statistics, you can clarify the correlation between the columns and avoid treating the columns as independent, thereby improving the accuracy of selectivity calculation and further ensuring the accuracy of cardinality estimation.

[0058] For example, the SQL statement is:

[0059] select*SELECT*FROM t1 WHERE a=1AND b>5;

[0060] This query statement involves columns a and b of the t1 data table, so extended statistics can be created:

[0061] CREATE STATISTICS t1_stat(dependencies)ON a,b FROM t1;

[0062] The extended statistics list created here is a function dependency list, and the selection rate calculation formula is:

[0063] Selectivity=f*Min(sel_a,sel_b)+(1-f)*sel_a*sel_b

[0064] Among them, f is the functional dependency, sel_a is the selectivity of column a, and sel_b is the selectivity of column b.

[0065] However, when the constraints of the SQL statement involve scalarArrayOpExpr, such as:

[0066] SELECT*FROM t1 WHERE a IN(1,51)AND b>5;

[0067] Although the extended statistics of columns a and b have been created, since the functional dependency describes a relational data table, if for each value in a column, the value in another column is unique, then there is a functional dependency. However, the scalarArrayOpExpr clause is for the relationship between scalars and arrays. Currently, there is no judgment logic to determine whether the scalarArrayOpExpr clause is compatible with functional dependencies. Therefore, the extended statistics cannot be used, and only the single-column statistics can be used. The selectivity calculation formula is:

[0068] Selectivity = sel_a*sel_b

[0069] Similarly, when the extended statistics list is a multi-column Most Common Value (MCV), by recording the most common values ​​and their frequency of occurrence in a multi-column combination, this frequency information can imply some kind of association between the columns. For example, if a city and country combination appears frequently, this may indicate some kind of geographical or logical correlation between the two columns. However, since there is currently no judgment logic to determine whether the scalarArrayOpExpr clause is compatible with multi-column common values, extended statistics cannot be used and only single-column statistics can be used.

[0070] To this end, the present application provides a cardinality estimation method. When there is a correlation between multiple columns of a data table, if a target clause is included in a query statement, whether the target clause is compatible with a function dependency list or a common value list is determined based on the operators and quantity of the target clause. Thus, the function dependency list or the common value list can be used to calculate the selectivity of the data table that satisfies the target clause, thereby improving the accuracy of the selectivity calculation and further improving the accuracy of the cardinality estimation.

[0071] The embodiment of the present application provides a scenario illustration of cardinality estimation, in which an electronic device, when there is a correlation between multiple columns of the same data table in a database, if a target clause is included in a query statement, determines whether the target clause is compatible with a function dependency list or a common value list based on the operator and quantity of the target clause. When the target clause is compatible with the function dependency list, the electronic device calculates the selectivity of satisfying the target clause in the data table based on the functional dependency between columns; when the target clause is compatible with the common value list, the electronic device calculates the selectivity of satisfying the target clause in the data table based on the common value selection. Then, the electronic device estimates the cardinality based on the number of rows and selectivity of the data table, and the cardinality is the number of rows in the data table that satisfy the target clause.

[0072] The technical solution of the present application and how the technical solution of the present application solves the above-mentioned technical problems are described in detail below with specific embodiments. The following specific embodiments can be combined with each other, and the same or similar concepts or processes may not be repeated in some embodiments. The embodiments of the present application will be described below in conjunction with the accompanying drawings.

[0073] Figure 1 A flowchart of the cardinality estimation method provided for this application is shown in FIG. Figure 1 As shown, the method includes:

[0074] S101. In the case where there is a correlation between multiple columns of a data table, if a query statement includes a target clause, determine whether the target clause is compatible with the extended statistical information list according to the operator and quantity of the target clause.

[0075] Among them, the target clause includes expressions between scalars and arrays, and the extended statistics list includes a function dependency list and a common value list. The function dependency list means that in a data table, the value of one attribute or a group of attributes determines the value of another attribute or a group of attributes. The function dependency list provides a clear relationship between columns, indicating that the values ​​of certain columns can uniquely determine the values ​​of other columns. The common value list records the most common values ​​and their frequencies in the data table. By analyzing the most common values ​​in the data table, a certain correlation between columns can be inferred. A scalar is a single numerical entity. Usually, the data represents a single numerical value, which can be an integer, floating point number, character, etc.

[0076] For example, you can draw a scatter plot of two or more columns of values. If the points show a linear trend, such as most of the points are concentrated on a straight line, then the two or more columns may be correlated. You can also use a heat map to visualize the correlation matrix, which can quickly identify the correlation between multiple columns. For example, the target clause can include a clause of type scalarArrayOpExpr, such as:

[0077] SELECT*FROM t1 WHERE a IN(1,51)AND b>5

[0078] Among them, FROM indicates that the data comes from the data table t1; WHERE introduces a conditional clause to filter records; a IN(1,51) is the first condition, which means selecting records with values ​​of 1 or 51 in column a; b>5 is the second condition, which means selecting records with values ​​of column b greater than 5; SELECT* means selecting all columns that meet these two conditions.

[0079] In some embodiments, it is determined whether the target clause satisfies a first preset condition, and if so, it is determined that the target clause is compatible with the function dependency list. The first preset condition includes: the operator in the target clause includes the first preset operator in the first operator (ANY), and / or the second operator (IN); the target clause includes at least two columns; the column identifier in the target clause is on the left side of the operator and the constant is on the right side of the operator. The first operator is used to compare a scalar with any element in an array, and the first preset operator includes any operator symbol among equal to (=), greater than (>), less than (<), greater than or equal to (≥), and less than or equal to (≤); the second operator is used to check whether the scalar exists in the array.

[0080] Accordingly, when the operator is ALL or inequality, since it involves comparison of multiple values, and functional dependency focuses on the precise value relationship (such as how a column depends on another column), it cannot provide a precise relationship and is not compatible with the functional dependency list. In addition, extended statistics indicate the correlation between multiple columns of the same data table, so when the number of column attributes of the same data table involved in the expression in the constraint condition is less than 2, it is not compatible with the functional dependency list.

[0081] At the same time, since the IN clause can be equivalently written as an OR clause (for example, a IN(1,51) is equivalent to a=1OR a=51), the following supports the judgment of the existence of an OR clause equivalent to IN in the constraint condition:

[0082] The operands in the OR clause are processed recursively, because this is only to achieve compatibility between the OR clause equivalent to IN and the function dependency list. Therefore, only when all conditions point to the same attribute (that is, the columns come from the same data table), IN is equivalent to OR and can use the extended statistical information of the function dependency.

[0083] Based on this, it is possible to determine whether the target clause is compatible with the function dependency list, so that when the target clause is compatible with the function dependency list, the function dependency list can be used to more accurately calculate the selectivity and further estimate the cardinality.

[0084] In some embodiments, it is determined whether the target clause satisfies a second preset condition, and if so, it is determined that the target clause is compatible with the common value list. The second preset condition includes: the target clause includes a second preset operator; the target clause includes at least two columns; the column identifier in the target clause is on the left side of the operator and is on the right side of the operator. The second preset operator includes any operator of equal to (=), not equal to (!=), greater than (>), less than (<), greater than or equal to (≥), and less than or equal to (≤).

[0085] It should be noted that extended statistics indicate the correlation between multiple columns of the same data table. Therefore, when the number of column attributes of the same data table involved in the constraint expression is less than 2, it is not compatible with the common value column data table. Currently, the operators supported under this condition are limited to =, !=, >, <, >=, <=. The column must be on the left side of the operator and the constant on the right side. If the constant is on the left side, it loses the meaning of matching with the common value list because common values ​​are designed for columns, not constants.

[0086] Based on this, it is possible to determine whether the target clause is compatible with the common value list, so that when the target clause is compatible with the common value list, the common value list can be used to more accurately calculate the selectivity and further estimate the cardinality.

[0087] When the target clause is compatible with the function dependency list, step S102 is executed; when the target clause is compatible with the common value list, step S103 is executed.

[0088] S102: When the target clause is compatible with the functional dependency list, the selectivity of the data table that satisfies the target clause is calculated according to the functional dependencies between columns.

[0089] Functional Dependency Degree is a metric used to quantify the ability of one set of columns (determinants) to determine another set of columns (determined factors).

[0090] For example, the degree of functional dependency can be determined by uniqueness analysis. If each unique value combination of the determinant corresponds to a unique value combination of the determined factor, then there is a complete functional dependency. The ratio of the number of unique value combinations of the determinant (called the cardinality) to the number of rows in the entire data set can be calculated. This ratio can be used to estimate the degree of functional dependency. A ratio close to 1 indicates that the degree of functional dependency is high.

[0091] Functional dependence can also be determined through statistical analysis. The number of different values ​​of the determined factor for each combination of determinant values ​​is counted. If the values ​​of the determined factors are unique or vary little in most cases, the functional dependence is high.

[0092] In practical applications, in database systems, the calculation of functional dependencies may involve complex statistical analysis and data scanning. Database systems can use sampling, histograms, and other statistical tools to estimate functional dependencies without scanning the entire data set.

[0093] In order to further verify that the selectivity obtained in this embodiment is more accurate, verification is performed in the following manner:

[0094] First create the extended statistics:

[0095] CREATE STATISTICS func_deps_stat(dependencies)ON a,b FROM functional_dependencies;

[0096] Then analyze the query execution plan:

[0097] Explain analyze SELECT*FROM functional_dependencies WHERE aIN(1,51)AND b='1'.

[0098] The selectivity of the target clause in the data table is calculated without using functional dependencies. The results are as follows:

[0099] Seq Scan on functional_dependencies(cost=0.00.106.00rows=2width=82)(actual time=0.015..0.594rows=100loops=1)

[0100] Filter:((a=ANY('{1,51}'::integer[]))AND(b='1'::text))

[0101] Rows Removed by Filter:4900

[0102] Planning Time: 0.483ms

[0103] Execution Time: 0.614ms.

[0104] Among them, rows = 2 is the number of rows estimated by the optimizer in the planning stage, that is, when analyzing the query, the optimizer expects that there will be about 2 rows of data that meet the query condition `(a IN(1,51)AND b='1')`. rows = 100 is the number of rows returned when the query is actually executed, that is, during the actual execution, 100 rows of data meet the query condition. There is a big gap between the two.

[0105] The selectivity of the target clause in the data table is calculated using functional dependencies. The results are as follows:

[0106] Seq Scan on functional_dependencies(cost=0.00.106.00rows=2width=82)(actual time=0.015..0.594rows=100loops=1)

[0107] Filter:((a=ANY('{1,51}'::integer[]))AND(b='1'::text))

[0108] Rows Removed by Filter:4900

[0109] Planning Time: 0.483ms

[0110] Execution Time: 0.614ms

[0111] (5 lines of record).

[0112] The expected number of rows and the actual number of rows are both 100. Obviously, when the target clause satisfies the functional dependency, the selectivity can be determined more accurately.

[0113] S103. When the target clause is compatible with the common value list, the selectivity of the data table that satisfies the target clause is calculated according to the common value selectivity.

[0114] Common value selection rate refers to the frequency or proportion of a particular value appearing in a set of data or a data set.

[0115] In order to further verify that the selectivity obtained in this embodiment is more accurate, verification is performed in the following manner:

[0116] First create the extended statistics:

[0117] CREATE STATISTICS mcv_lists_stats(mcv)ON a,b,c FROM mcv lists;

[0118] Then analyze the query execution plan:

[0119] explain analyze SELECT*FROM mcv_lists WHERE a IN(1,2,51,52)AND b IN('1','2').

[0120] The selectivity of the data table that satisfies the target clause is calculated without using common values. The results are as follows:

[0121] Seq Scan on mcv_lists(cost=0.00.118.50rows=8width=82)(actual time=0.034..1.311rows=200loops=1)

[0122] Filter:(((b)::text=ANY('{1,2}'::text[]))AND(a=ANY('{1,2,51,52}'::integer[]))

[0123] Rows Removed by Filter:4800

[0124] Planning Time: 0.935ms

[0125] Execution Time:1.349ms

[0126] (5 lines of record).

[0127] Among them, rows = 8 is the number of rows estimated by the optimizer in the planning stage, that is, when analyzing the query, the optimizer expects that there will be about 8 rows of data that meet the query condition a IN (1, 2, 51, 52) AND b IN ('1', '2'). rows = 100 is the number of rows returned when the query is actually executed, that is, during the actual execution process, 100 rows of data meet the query condition. There is a big gap between the two.

[0128] The selectivity of the target clause in the data table is calculated using common values. The results are as follows:

[0129] Seq Scan on mcv_lists(cost=0.00.118.50rows=200width=82)(actualtime=0.155..0.952rows=200loops=1)

[0130] Filter:(((b)::text=ANY('{1,2}'::text[]))AND(a=ANY('{1,2,51,52}'::integer[]))

[0131] Rows Removed by Filter:4800

[0132] Planning Time:2.628ms

[0133] Execution Time: 0.968ms

[0134] (5 lines of record).

[0135] The expected number of rows and the actual number of rows are both 200. Obviously, when the target clause meets the common value, the selectivity can be determined more accurately.

[0136] S104. Estimate the cardinality according to the number of rows and selectivity of the data table, where the cardinality is the number of rows in the data table that satisfy the target clause.

[0137] In some embodiments, there are multiple target clauses, and the final selectivity of the data table can be determined based on the product of the selectivity of each target clause, and the cardinality can be estimated based on the final selectivity and the number of rows of the data table, so that when the constraint includes multiple clauses, the number of rows that satisfy all clauses can be obtained.

[0138] The cardinality estimation method provided in the present application, when there is a correlation between multiple columns of a data table, if the query statement includes a target clause, determines whether the target clause is compatible with a function dependency list or a common value list based on the operators and quantity of the target clause, thereby being able to use the function dependency list or the common value list to improve the accuracy of the selectivity calculation, thereby improving the accuracy of the cardinality estimation.

[0139] Figure 2 A flowchart of a cardinality estimation method provided by another embodiment of the present application is shown in FIG. Figure 2 As shown, step S102, when the target clause is compatible with the functional dependency list, calculating the selectivity of the data table that satisfies the target clause according to the functional dependency between columns, may include the following steps:

[0140] S201. Obtain the single-column selectivity of each sub-condition in the target clause, and obtain the minimum selectivity from multiple single-column selectivities.

[0141] For example, the target clause may include multiple sub-conditions, for example, the clause SELECT*FROM t1WHERE a IN(1,51)AND b>5 includes two sub-conditions, a IN(1,51) and b>5. The selectivity of a sub-condition refers to the selectivity of the sub-condition on a single column.

[0142] After obtaining the single-column selectivity of each sub-condition in the target clause, the minimum selectivity is selected.

[0143] S202, calculating a first product of the minimum selectivity and the function dependency, a second product of the function independence and the single column selectivity of each sub-condition, and the function dependency and the function independence are added to one.

[0144] S203: Determine the selectivity of the data table that satisfies the target clause according to the sum of the first product and the second product.

[0145] For example, the selectivity of the target clause can be obtained according to the following formula:

[0146] Selectivity*=f*Min(sel_a,sel_b)+(1-f)*sel_a*sel_b;

[0147] Among them, Selectivity* represents the selectivity of the target clause, f represents the function dependency, sel_a represents the single-column selectivity of the sub-condition corresponding to a, sel_b represents the single-column selectivity of the sub-condition corresponding to b, and 1-f represents the function independence.

[0148] The cardinality estimation method provided in the embodiment of the present application can more accurately determine the selectivity of the target clause in the data table based on the single-column selectivity and function dependency, thereby accurately estimating the cardinality.

[0149] Figure 3 A flowchart of a cardinality estimation method provided in another embodiment of the present application is shown in FIG. Figure 3 As shown, step S103, when the target clause is compatible with the common value list, calculating the selectivity of the data table that satisfies the target clause according to the common value selectivity, may include the following steps:

[0150] S301, obtaining the first value in the array that appears in a common value list, where the common value list includes values ​​in the data table whose frequency is greater than a preset frequency.

[0151] For example, when it is determined that the target clause is compatible with the common value list, a corresponding common value list can be created for the above-mentioned data table (any data table in the database), and the values ​​in the data table whose frequency is greater than the preset frequency are written into the common value list. The preset frequency can be determined according to actual conditions.

[0152] It should be noted that the first value may include one or more values. For example, if the value includes three values, two of which appear in the common value list, then the first value includes two values.

[0153] S302: Match the bit mark corresponding to the first value in the bitmap with the mark, the total number of bits in the bitmap is the same as the total number of values ​​in the array, and each bit in the bitmap corresponds to a value in the array.

[0154] For example, a bitmap may be created according to the number of values ​​in the array, the number of bits in the bitmap is the same as the number of values ​​in the array, and each bit in the bitmap corresponds to one of the values. The first value in the value list that appears in the common value list is obtained, and a matching mark is placed on the bit corresponding to the first value in the bitmap. For example, if the first value includes two values, which correspond to the first and third values ​​in the array, respectively, a matching mark may be placed on the first and third bits in the bitmap.

[0155] S303: Determine the common value selection rate according to the ratio of the number of bits marking the matching identifier in the bitmap to the total number of values ​​in the common value list.

[0156] For example, if the number of bits in the bitmap that identifies the matching identifier is 2 and the total number of values ​​in the common value list is 10, then the common value selection rate is 1 / 5.

[0157] S304: Determine the uncommon value selection rate according to the ratio of the bitmap without a matching identifier in the bitmap to the total number of values ​​in the common value list.

[0158] For example, if the number of unmatched bits in the bitmap is 3 and the total number of values ​​in the common value list is 10, then the selection rate of uncommon values ​​is 3 / 10.

[0159] S305: Determine the selectivity of the target clause according to the sum of the common value selectivity and the uncommon value selectivity.

[0160] For example, Selectivity*=mcv_sel+other_sel, where Selectivity* represents the selectivity of the target clause, mcv_sel represents the selectivity of common values, and other_sel represents the selectivity of uncommon values.

[0161] The cardinality estimation method provided in the embodiment of the present application determines the common value selectivity and the uncommon value selectivity based on the common value column data table and the bitmap, so that the selectivity of satisfying the target clause in the data table can be more accurately determined based on the common value selectivity and the uncommon value selectivity, thereby accurately estimating the cardinality.

[0162] Figure 4 The schematic diagram of the structure of the cardinality estimation provided for this application is as follows: Figure 4 As shown, the cardinality estimation 40 provided in this embodiment includes:

[0163] A judgment module 41 is used for judging whether the target clause is compatible with the extended statistical information list according to the operator and quantity of the target clause when there is a correlation between multiple columns of the data table, if the query statement includes a target clause, the target clause includes an expression between a scalar and an array, and the extended statistical information list includes a function dependency list or a common value list;

[0164] A first calculation module 42, configured to calculate a selectivity of the target clause in the data table according to the functional dependencies between columns when the target clause is compatible with the functional dependency list;

[0165] A second calculation module 43, configured to calculate a selectivity of the target clause in the data table according to the selectivity of the common values ​​when the target clause is compatible with the common value list;

[0166] The estimation module 44 is used to estimate the cardinality according to the number of rows in the data table and the selectivity, where the cardinality is the number of rows in the data table that satisfy the target clause.

[0167] In a possible implementation, the judgment module 41 is specifically used to judge whether the target clause satisfies a first preset condition, and if so, determine that the target clause is compatible with the function dependency list; the first preset condition includes: the operator in the target clause includes a first preset operator in the first operator, and / or a second operator; the first operator is used to compare the scalar with any element in the array, and the first preset operator includes any operator among equal, greater than, less than, greater than or equal to, and less than or equal to; the second operator is used to check whether the scalar exists in the array; the target clause includes at least two columns; the column identifier in the target clause is on the left side of the operator and the constant is on the right side of the operator;

[0168] In a possible implementation, the judgment module 41 is specifically used to judge whether the target clause satisfies a second preset condition, and if so, determines that the target clause is compatible with a common value list; the second preset condition includes: the target clause includes a second preset operator; the target clause includes at least two columns; the column identifier in the target clause is on the left side of the operator and the constant is on the right side of the operator; the second preset operator includes any operator selected from equal to, not equal to, greater than, less than, greater than or equal to, and less than or equal to.

[0169] In a possible implementation, the first calculation module 42 is specifically used to obtain the single-column selectivity of each sub-condition in the target clause, and obtain the minimum selectivity from multiple single-column selectivity; calculate a first product of the minimum selectivity and function dependency, and a second product of function independence and the single-column selectivity of each sub-condition, wherein the function independence and the function dependency are added together to be one; and determine the selectivity in the data table that satisfies the target clause based on the sum of the first product and the second product.

[0170] In a possible implementation, the second calculation module 43 is specifically used to obtain the first value in the array that appears in the common value list, the common value list includes values ​​in the data table whose frequency is greater than a preset frequency; mark the bit corresponding to the first value in the bitmap with a matching identifier, the total number of bits of the bitmap is the same as the total number of values ​​in the array, and each bit in the bitmap corresponds to a value in the array; determine the common value selection rate according to the ratio of the number of bits marked with matching identifiers in the bitmap to the total number of values ​​in the common value list; determine the uncommon value selection rate according to the ratio of the number of bits not marked with matching identifiers in the bitmap to the total number of values ​​in the common value column data table; and determine the selectivity of the target clause according to the sum of the common value selection rate and the uncommon value selection rate.

[0171] In a possible implementation, there are multiple target clauses; the estimation module 44 is specifically used to determine the final selectivity of the data table according to the product of the selectivity of each target clause; and estimate the cardinality according to the final selectivity and the number of rows of the data table.

[0172] The cardinality estimation device provided in this embodiment can execute the method provided in the above method embodiment, and its implementation principle and technical effect are similar, so this embodiment will not be described in detail here.

[0173] Figure 5 This is a schematic diagram of the structure of the electronic device provided in this application. Figure 5 As shown, the electronic device 50 provided in this embodiment includes: at least one processor 501 and a memory 502. Optionally, the device 50 also includes a communication component 503. The processor 501, the memory 502 and the communication component 503 are connected via a bus 504.

[0174] In a specific implementation process, at least one processor 501 executes the computer-executable instructions stored in the memory 502, so that at least one processor 501 executes the above method.

[0175] The specific implementation process of the processor 501 can be found in the above method embodiment, and its implementation principle and technical effect are similar, so this embodiment will not be repeated here.

[0176] In the above embodiments, it should be understood that the processor may be a central processing unit (CPU), or other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), etc. A general-purpose processor may be a microprocessor or any conventional processor. The steps of the method disclosed in the invention may be directly implemented as being executed by a hardware processor, or may be executed by a combination of hardware and software modules in the processor.

[0177] The memory may include a high-speed memory (Random Access Memory, RAM), and may also include a non-volatile memory (Non-volatile Memory, NVM), such as at least one disk memory.

[0178] The bus can be an Industry Standard Architecture (ISA) bus, a Peripheral Component Interconnect (PCI) bus, or an Extended Industry Standard Architecture (EISA) bus, etc. The bus can be divided into an address bus, a data bus, a control bus, etc. For the convenience of data representation, the bus in the drawings of the present application is not limited to only one bus or a list of buses.

[0179] The present application also provides a computer program product, including a computer program, which implements the above method when executed by a processor.

[0180] The present application also provides a computer-readable storage medium, in which computer-executable instructions are stored. When a processor executes the computer-executable instructions, the above method is implemented.

[0181] The above-mentioned readable storage medium can be implemented by any list of volatile or non-volatile storage devices or their combination, such as static random access memory (SRAM), electrically erasable programmable read-only memory (EEPROM), erasable programmable read-only memory (EPROM), programmable read-only memory (PROM), read-only memory (ROM), magnetic memory, flash memory, magnetic disk or optical disk. The readable storage medium can be any available medium that can be accessed by a general or special purpose computer.

[0182] An exemplary readable storage medium is coupled to a processor so that the processor can read information from the readable storage medium and write information to the readable storage medium. Of course, the readable storage medium can also be a component of the processor. The processor and the readable storage medium can be located in an application specific integrated circuit (Application Specific Integrated Circuits, referred to as: ASIC). Of course, the processor and the readable storage medium can also exist in the device as discrete components.

[0183] The division of units is only a logical function division, and there may be other divisions in actual implementation, such as multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be an indirect coupling or communication connection through some interface, device or unit, which can be electrical, mechanical or other forms.

[0184] The units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed on multiple network units. Some or all of the units may be selected according to actual needs to achieve the purpose of the solution of this embodiment.

[0185] In addition, each functional unit in each embodiment of the present invention may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit.

[0186] If the function is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention, or the part that contributes to the prior art, or the part of the technical solution, can be embodied in the form of a software product. The computer software product is stored in a storage medium, including several instructions for a computer device (which can be a personal computer, server, or network device, etc.) to perform all or part of the steps of the methods of each embodiment of the present invention. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), disk or optical disk, etc. Various media that can store program codes.

[0187] Those skilled in the art can understand that all or part of the steps of implementing the above-mentioned method embodiments can be completed by hardware related to program instructions. The aforementioned program can be stored in a computer-readable storage medium. When the program is executed, the steps of the above-mentioned method embodiments are executed; and the aforementioned storage medium includes: ROM, RAM, disk or optical disk and other media that can store program codes.

[0188] Finally, it should be noted that those skilled in the art will readily conceive of other embodiments of the present invention after considering the specification and practicing the invention disclosed herein. The present invention is intended to cover any variations, uses or adaptations of the present invention, which follow the general principles of the present invention and include common knowledge or customary technical means in the art not disclosed by the present invention, are not limited to the precise structure described above and shown in the drawings, and may be modified and changed in various ways without departing from the scope thereof. The scope of the present invention is limited only by the appended claims.

Claims

1. A cardinality estimation method, characterized in that: include: In the case where there is a correlation between multiple columns of a data table, if the query statement includes a target clause, judging whether the target clause is compatible with an extended statistical information list according to an operator and a quantity of the target clause, the target clause includes an expression between a scalar and an array, and the extended statistical information list includes a function dependency list or a common value list; When the target clause is compatible with the functional dependency list, the selectivity of the data table that satisfies the target clause is calculated according to the functional dependencies between columns; When the target clause is compatible with the common value list, calculating the selectivity of the data table that satisfies the target clause according to the common value selectivity; A cardinality is estimated according to the number of rows in the data table and the selectivity, where the cardinality is the number of rows in the data table that satisfy the target clause.

2. The method according to claim 1, characterized in that The step of judging whether the target clause is compatible with the extended statistical information list according to the operator and the quantity of the target clause includes: Determine whether the target clause satisfies a first preset condition, and if so, determine whether the target clause is compatible with the functional dependency list; The first preset condition includes: The operators in the target clause include a first preset operator in the first operator, and / or a second operator; the first operator is used to compare the scalar with any element in the array, and the first preset operator includes any operator among equal, greater than, less than, greater than or equal to, and less than or equal to; the second operator is used to check whether the scalar exists in the array; The target clause includes at least two columns; The column identifiers in the target clause are on the left side of the operator and the constants are on the right side of the operator.

3. The method according to claim 1, characterized in that The step of judging whether the target clause is compatible with the extended statistical information list according to the operator and the quantity of the target clause includes: determining whether the target clause satisfies a second preset condition, and if so, determining that the target clause is compatible with a common value list; The second preset condition includes: The target clause includes a second preset operator; The target clause includes at least two columns; The column identifier in the target clause is on the left side of the operator and the constant is on the right side of the operator; The second preset operator includes any operator of equal to, not equal to, greater than, less than, greater than or equal to, and less than or equal to.

4. The method according to claim 1, characterized in that: The calculating the selectivity of the data table that satisfies the target clause according to the functional dependency between columns includes: Obtaining a single-column selectivity of each sub-condition in the target clause, and obtaining a minimum selectivity from a plurality of the single-column selectivities; Calculate a first product of the minimum selectivity and the function dependency, and a second product of the function independence and the single-column selectivity of each sub-condition, wherein the function independence and the function dependency add up to one; The selectivity of the data table that satisfies the target clause is determined according to the sum of the first product and the second product.

5. The method according to claim 1, characterized in that The selecting and calculating the selectivity of the data table that satisfies the target clause according to the common values ​​includes: Obtaining a first value in the array that appears in the common value list, wherein the common value list includes values ​​in the data table whose frequency is greater than a preset frequency; Matching the bit marks corresponding to the first value in the bitmap with an identifier, the total number of bits in the bitmap is the same as the total number of values ​​in the array, and each bit in the bitmap corresponds to a value in the array; Determine a common value selection rate according to a ratio of the number of bits of the marking matching identifier in the bitmap to the total number of values ​​in the common value list; Determining a selection rate of uncommon values ​​according to a ratio of the number of bits in the bitmap that are not marked with a matching identifier to the total number of values ​​in the common value list; The selectivity of the target clause is determined according to the sum of the common value selectivity and the uncommon value selectivity.

6. The method according to any one of claims 1 to 5, characterized in that The number of the target clauses is multiple; The estimating the cardinality according to the number of rows in the data table and the selectivity includes: Determining a final selectivity of the data table according to the product of the selectivity of each target clause; Estimate the cardinality based on the final selectivity and number of rows of the data table in question.

7. A cardinality estimation device, characterized in that: include: A judgment module, used for judging whether the target clause is compatible with the extended statistical information list according to the operator and quantity of the target clause when there is a correlation between multiple columns of the data table, if the query statement includes a target clause, the target clause includes an expression between a scalar and an array, and the extended statistical information list includes a function dependency list or a common value list; A first calculation module, configured to calculate a selectivity of the target clause in the data table according to the functional dependencies between columns when the target clause is compatible with the functional dependency list; A second calculation module, configured to calculate a selectivity of the target clause in the data table according to the common value selectivity when the target clause is compatible with the common value list; An estimation module is used to estimate a cardinality according to the number of rows in the data table and the selectivity, where the cardinality is the number of rows in the data table that satisfy the target clause.

8. An electronic device, characterized in that: include: Memory, processor; The memory stores computer-executable instructions; The processor executes the computer-executable instructions stored in the memory, so that the processor performs the method according to any one of claims 1 to 6.

9. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer-executable instructions, which are used to implement the method according to any one of claims 1 to 6 when executed by a processor.

10. A computer program product, characterized in that The invention comprises a computer program, which implements the method according to any one of claims 1 to 6 when being executed by a processor.

Images