Optimized query method based on multi-table statistical information cardinality estimation

Abstract

The invention discloses an optimized query method based on multi-table statistical information cardinality estimation. The method comprises the following steps: constructing a star structure in an offline stage, and converting historical multi-table connection query into virtual key connection between nodes in the star structure; calculating and counting a structure cardinal number and a degree sequence in the star-shaped structure through a merged degree sequence, and compressing and outputting a finally obtained degree sequence through a streaming degree sequence; at the present stage, according to the connection condition of the multi-table connection query input in real time, the multi-table connection query is decomposed into a plurality of star structure instances, and after the degree sequence obtained in each corresponding offline stage is queried, the final cardinality upper bound estimation value of the whole query is obtained through middle structure merging iteration. According to the method, on the premise that the upper bound is estimated, the precision and efficiency of multi-table connection cardinal number estimation are remarkably improved, and the method is suitable for a query optimization scene of a relational database.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of information processing, and particularly relates to an optimized query method based on cardinality estimation of multi-table statistical information. BACKGROUND

[0002] In a relational database, a user sends a query request to the database through a structured query language (SQL), and one of the core functions of the database is to execute the query and return the result. The database is usually equipped with a cost-based query optimizer for selecting the optimal execution plan. The core component of the optimizer is a cardinality estimator, which is used to predict the number (i.e., the cardinality) of the intermediate results and the final results of the query before the query is actually executed. Existing methods have poor accuracy or high delay. For example, a machine learning method needs a large amount of training. SUMMARY

[0003] The present application proposes an optimized query method based on cardinality estimation of multi-table statistical information to solve the above problems in the prior art. The method collects statistical information of key multi-table connection modes by constructing a star structure, thereby directly reducing the error accumulation in iterative estimation from the source. At the same time, by using an efficient statistical information collection algorithm and a fast online estimation algorithm for the star structure, the overhead of offline construction and online estimation is controlled within an acceptable range while ensuring accuracy and upper bound.

[0004] The present application is implemented by the following technical solutions:

[0005] The present application relates to an optimized query method based on cardinality estimation of multi-table statistical information. In the offline stage, a star structure is constructed, and a multi-table connection query is converted into a virtual key connection between nodes in the star structure. The structural cardinality and the degree sequence in the star structure are calculated by merging the degree sequence, and the final degree sequence is obtained by outputting the final degree sequence through a streaming degree sequence compression. In the online stage, the connection conditions of the multi-table connection query are decomposed into several star structure instances according to real-time input, and the degree sequence obtained in the offline stage is queried for each corresponding instance. After the intermediate structure is merged and iterated, the final cardinality upper bound estimation value of the entire query is obtained.

[0006] When any base table in the star structure has a predicate, the present application preferably performs predicate filtering and then connection structure estimation.

[0007] The present application relates to an optimized query system for implementing the above method, which comprises a statistical information collection unit and a cardinality estimation unit. The statistical information collection unit collects statistical information according to the database table structure information to obtain star structure statistical information. The cardinality estimation unit performs star structure dual connection conversion processing according to the input query, calls the statistical information collected by the statistical information collection unit, and estimates the cardinality of the query.

[0008] Technical effects

[0009] The present application converts the connection of multiple tables into the connection between nodes in a star structure by virtual keys, reduces the complexity of connection, uses degree sequence groups to depict the statistical characteristics of the star structure, supports compact upper bound estimation, realizes efficient collection through merged degree calculation and FastCompress degree sequence compression algorithm, decouples the estimation of single table rows and multiple table rows, so that the present application can be used with existing single table cardinality estimation methods to process predicate query scenarios. Compared with the prior art, the present application improves the accuracy of cardinality estimation, enables the query optimizer to more accurately select a plan, and thus improves query performance. BRIEF DESCRIPTION OF DRAWINGS

[0010] Figure 1 is a flowchart of the present application;

[0011] Figure 2 is a schematic diagram of a star structure;

[0012] Figure 3 is a schematic diagram of a dual connection;

[0013] Figure 4 is a schematic diagram of a statistical information structure (left) and its meaning (right);

[0014] Figure 5 is a schematic diagram of JOBM Query9 in the embodiment;

[0015] Figure 6 is a schematic diagram of the effect of the embodiment. DETAILED DESCRIPTION

[0016] As shown in Figure 1 , the present embodiment relates to an optimized query method based on multiple table statistical information cardinality estimation, which comprises:

[0017] Step 1) For a database object with multiple table connections, a star structure as shown in Figure 2 is constructed, that is, each table contains a common field and the ordinary multiple table connection query is converted into a virtual key connection between nodes in the star structure, specifically: a property node representing an equal attribute set and multiple table nodes connected to the property node through an equal connection condition form a star topology.

[0018] As shown in Figure 3 , the conversion comprises:

[0019] Step a parses the original SQL into the connection relationship diagram in Figure 3 (ii) by extracting the connection relationship.

[0020] For example: The original SQL is SELECT COUNT(*) FROM t1, t2, t3, t4 WHERE t1.a1=t2.a1AND t2.a1=t3.a1 AND t3.a2=t4.a2;

[0021] Step b: Based on the connection relationship diagram, organize the connection attributes into a star-shaped structure diagram by nodes.

[0022] like Figure 3 (iii) Subgraphs t1, t2, t3, a1 and t3, t4, a2 are shown.

[0023] Step c, based on the results obtained in step b, extracts all tables associated with each attribute and treats them as a star schema, resulting in... Figure 3 (iv) Schematic diagram of star-shaped structure connection relationship.

[0024] Step d involves converting the star schema back to the query function based on the results obtained in step c. Figure 3 The dual join corresponding to the original SQL in (v).

[0025] For example: `SELECT COUNT(*) FROM (select *, t3.rowid as uid from t1, t2, t3where t1.a1 = t2.a1 and t2.a1 = t3.a1) as star_a1, (select *, t3.rowid as uid from t3, t4 where t3.a2 = t4.a2) as star_a2 WHERE star_a1.uid = star_a2.uid`, where `rowid` represents a virtual unique key for a table. Since dual join modeling is only used for cardinality estimation and is not actually performed, this virtual unique key does not need to actually exist.

[0026] Step 2) as Figure 4 As shown, in the offline stage, the structural cardinality and degree sequence in the star-shaped structure shown in step 1 are calculated in advance using a merged degree sequence, and the final degree sequence is output through streaming degree sequence compression. Specifically, this includes:

[0027] 2.1. Scan the single-table data of each attribute in the star schema and sort them respectively;

[0028] 2.2. Employing a multi-way merge strategy, based on the ordered data of each table obtained in step 2.1, the same values ​​on the same attribute corresponding to different tables are matched using the merge method. The star schema cardinality is calculated by adding the product of the number of times the same value appears on each attribute as the contribution of the same value to the cardinality and the contribution of the same value to the rowid degree sequence of each table. For all subsets of the table set participating in the merge, the degree sequence of their star schema can also be calculated together in the same way.

[0029] The contribution of the same value to the rowid degree sequence of each table refers to the product of the number of times the value appears in the attribute in all tables other than the target table in the star schema. This product is the degree 'd' of the value in the rowid degree sequence of the target table. Since the value may also appear multiple times in the target table (count of occurrences), its contribution to the rowid degree sequence of the target table is reflected by inserting count degree values ​​'d' into the degree sequence.

[0030] 2.3 By setting a precision coefficient c, a series of buckets are created to store the degree value and the frequency of its occurrence, where the degree value stored in the i-th bucket ranges from... Within the interval, the degree values ​​obtained in step 2.2 are dynamically allocated to the series buckets until step 2.2 is completed and all degree values ​​have been added. Then, all degree values ​​in the series buckets are converted to the maximum degree value in the bucket, and the output can be used as the compressed degree sequence.

[0031] During the merge calculation process, each calculated degree value can be immediately assigned to the corresponding bucket. Only the count of the bucket and the current maximum degree value need to be updated, without materializing the entire degree sequence.

[0032] This step reduces the space complexity of storing the degree sequence from O(k) to O(log D), where k refers to the length of the degree sequence and D refers to the range of degree values. Theoretically, it has been proven that the upper bound of its error is c², achieving a good balance between accuracy and efficiency.

[0033] Step 3) In the online phase, the cardinality upper bound estimation algorithm specifically includes:

[0034] 3.1 The multi-table join query to be processed is decomposed into several star structure instances according to its join conditions using the method in step 1, and the corresponding degree sequence group statistics are obtained from the offline storage of the star structure-degree sequence calculated in step 2.

[0035] 3.2 Perform structural merging iteration: In each iteration, find two star structures or intermediate structures that are related by rowid of any table and merge them into a new intermediate structure. At the same time, the updated cardinality upper bound is the sum of the dot product of the degree sequences of the two merged objects on the join table, the degree sequence of the updated join table is obtained by multiplying the degree values ​​one by one, and the degree sequence of the updated edge table is derived by worst-case assumption.

[0036] 3.3 Continue executing step 3.2 until there are no more star schemas or intermediate schemas that can be merged, and obtain the final cardinality upper bound estimate for the entire query.

[0037] This step significantly reduces error accumulation in iterative estimation by progressively transforming multi-table joins into virtual key joins and merging between star schemas, and rigorously guarantees that the estimation result is always an upper bound of the true cardinality based on degree sequence theory.

[0038] Step 4) When any base table in the obtained star schema contains a predicate, the predicate filtering is transformed into a join with a single table that has undergone predicate filtering, and then the join structure is estimated again, specifically including:

[0039] 4.1 Transform the original SQL into a star schema and join it with a single table that has been filtered by predicates.

[0040] 4.2 Obtain the degree sequence group of the star structure from the statistical information calculated in step 2.

[0041] 4.3 Estimate the cardinality of a single table, card, using any existing method, and transform it into a degree sequence containing card with a degree value of 1.

[0042] 4.4 Based on the degree sequence obtained in steps 4.2 and 4.3, perform cardinality estimation in the manner described in step 3.

[0043] For example, the original SQL is SELECT COUNT(*) FROM t1, t2 WHERE t1.a1=t2.a1 AND t1.a1= 1. This can be transformed into a star schema join with a single table that has been filtered by predicates, such as: SELECT COUNT(*) FROM(select *, rowid from t1, t2 where t1.a1 = t2.a1) AS star_a1, (select rowid from t1 where t1.a1 = 1) AS predicate_t1 WHERE star_a1.rowid = predicate_t1.rowid.

[0044] Step 5) Based on the results obtained in Step 4, guide the optimizer to perform join order enumeration or cost-based optimization, helping the optimizer to select a better join order for multi-table join queries and improve query performance.

[0045] Through practical application scenario experiments, under the environment of CPU model 13th Gen Intel(R) Core(TM) i7-13700K 3.40 GHz and 32G memory, the optimized query method based on cardinality estimation of multi-table statistical information of this invention was run by injecting subquery cardinality into the Duckdb database. Two benchmark tests, IMDB-JOBM and STATS-CEB, were performed and compared with the default Duckdb method. The results are shown in Table 1.

[0046] Table 1

[0047] Taking the first query in the STATS benchmark as an example: `select * FROM badges as b, users as u WHERE b.UserId = u.Id AND u.UpVotes >= 0;`, this is originally a two-table join SQL statement. However, in this embodiment, the SQL statement is transformed into a dual join with only a star schema. Therefore, the cardinality of the join result between tables `b` and `u` can be directly obtained from the statistics. Afterward, the single-table row estimation decoupling method is used to estimate the impact of the predicate `u.UpVotes>=0` on the cardinality to obtain the result. Specifically, in this scenario, since the selectivity of this predicate is 1, this embodiment can obtain a completely accurate cardinality.

[0048] Let's take the 9th query from the JOBM (an IMDB dataset) benchmark as an example. Figure 5 As shown, this is a query involving a 6-table join. Using existing methods, this would require five join result cardinality estimations. However, in this embodiment, the query can be identified and decomposed into three core star schemas: the first is a star schema centered on the `movie_id` attribute, containing tables `title(t)`, `movie_info(mi)`, `movie_info_idx(mi_idx)`, and `movie_companies(mc)`; the second is a star schema centered on the `company_id` attribute, containing tables `mc` and `cn`; and the third is a star schema centered on the `company_type_id` attribute, containing tables `mc` and `ct`.

[0049] When performing cardinality estimation, the cardinality and degree sequences of the three star-shaped structures are first obtained from the statistical information. Then, an iterative merging phase is initiated: the three star-shaped structures are merged using the RowId of the common table `mc`. During each merging process, the new upper bound of the cardinality is obtained by multiplying the degree sequences of the two structures in the `mc` table; simultaneously, the statistical information of the merged new structure is derived. After two iterations, all structures are merged into one, and the final cardinality is the upper bound of the cardinality of the merged structure. This example demonstrates that this embodiment transforms the cardinality estimation of a six-table connection into a single merging operation of two star-shaped structures. Compared to the traditional method, which requires five consecutive intermediate result estimations, this significantly reduces the error accumulation stage.

[0050] like Figure 6 As shown, this embodiment tested the cardinality estimation accuracy of Duckdb, SafeBound, and this embodiment on two test sets, STATS and IMDB. The final experimental results show that this embodiment (StarCE in the figure) has a certain accuracy advantage (the vertical axis in the figure is the estimation error. The closer the error is to the middle line, the higher the accuracy. Above the middle line, the estimated value is higher than the true cardinality. Below the middle line, the estimated value is lower than the true cardinality).

[0051] In summary, compared with existing technologies, this invention significantly improves estimation accuracy: such as Figure 6 As shown, on the STATS and IMDB datasets, StarCE's relative error is significantly lower than that of FactorJoin, SafeBound, and the built-in estimator in DuckDB. Particularly in STATS, it achieves completely accurate estimation of over 50% of subqueries, excluding the predicate. Furthermore, this invention guarantees a strict upper bound; the degree sequence structure ensures that the estimated value is an upper bound of the true value, effectively preventing the optimizer from selecting inferior plans. Thirdly, this invention significantly improves data collection efficiency: through parallelization and streaming compression, the statistical information collection speed is faster than SafeBound on both the STATS and IMDB datasets.

[0052] The above-described specific implementations can be partially adjusted by those skilled in the art in different ways without departing from the principles and purpose of the present invention. The scope of protection of the present invention is defined by the claims and is not limited to the above-described specific implementations. All implementation schemes within the scope of the claims are bound by the present invention.

Claims

1. An optimized query method based on cardinality estimation using multi-table statistical information, characterized in that, In the offline phase, a star schema is constructed and historical multi-table join queries are transformed into virtual key joins between nodes in the star schema. The structural cardinality and degree sequence in the star schema are calculated and statistically analyzed using merge-order degree sequence, and the final degree sequence is output by streaming degree sequence compression. At this stage, based on the join conditions of the multi-table join query input in real time, it is decomposed into several star structure instances. After querying the degree sequence obtained in the corresponding offline stage for each instance, the final cardinality upper bound estimate of the entire query is obtained through intermediate structure merging and iteration.

2. The optimized query method based on cardinality estimation of multi-table statistical information as described in claim 1, characterized in that, When any base table in the star schema contains a predicate, predicate filtering is performed first, followed by connection structure estimation.

3. The optimized query method based on cardinality estimation of multi-table statistical information according to claim 1, characterized in that, specifically... include: Step 1) For database objects with multiple table joins, construct a star schema, where each table contains a common field and ordinary multi-table join queries are transformed into virtual key connections between nodes in the star schema. Specifically, the star topology consists of an attribute node representing a set of equi-value attributes and multiple table nodes connected to the attribute node through equi-value join conditions. Step 2) In the offline stage, the structural cardinality and degree sequence in the star structure shown in Step 1 are calculated and statistically analyzed in advance using the merged degree sequence, and the final degree sequence is output by compression using the streaming degree sequence. Step 3) In the online phase, the upper bound of the base is estimated, specifically including: 3.1 The multi-table join query to be processed is decomposed into several star structure instances according to its join conditions using the method in step 1, and the corresponding degree sequence group statistics are obtained from the offline storage of the star structure-degree sequence calculated in step 2. 3.2 Perform structural merging iteration: In each iteration, find two star structures or intermediate structures that are related by the rowid of any table and merge them into a new intermediate structure. At the same time, the updated cardinality upper bound is the sum of the dot product of the degree sequences of the two merged objects on the join table, the degree sequence of the updated join table is obtained by multiplying the degree values ​​one by one, and the degree sequence of the updated edge table is derived by worst-case assumption. 3.3 Continue executing step 3.2 until there are no more star structures or intermediate structures that can be merged, to obtain the final cardinality upper bound estimate for the entire query; Step 4) When any base table in the obtained star schema contains a predicate, the predicate filtering is transformed into a join with a single table that has undergone predicate filtering, and then the join structure is estimated again, specifically including: 4.1 Transform the original SQL into a star schema join with a single table that has been filtered by predicates; 4.2 Obtain the degree sequence group of the star-shaped structure from the statistical information calculated in step 2; 4.3 Estimate the cardinality of a single table, card, using any existing method, and transform it into a degree sequence containing card with a degree value of 1; 4.4 Based on the degree sequence obtained in steps 4.2 and 4.3, perform cardinality estimation in the manner described in step 3; Step 5) Based on the results obtained in Step 4, guide the optimizer to perform join order enumeration or cost-based optimization, helping the optimizer to select a better join order for multi-table join queries and improve query performance.

4. The optimized query method based on cardinality estimation of multi-table statistical information according to claim 3, characterized in that, Step 2 specifically includes: 2.

1. Scan the single-table data of each attribute in the star schema and sort them respectively; 2.

2. Employing a multi-way merge strategy, based on the ordered data of each table obtained in step 2.1, the same values ​​on the same attribute corresponding to different tables are matched using the merge method. The star schema cardinality is calculated by adding the product of the number of times the same value appears on each attribute as the contribution of the same value to the cardinality and the contribution of the same value to the rowid degree sequence of each table. For all subsets of the table set participating in the merge, the degree sequence of their star schema can also be calculated together in the same way. The contribution of the same value to the rowid degree sequence of each table refers to the following: In the star schema, except for the target table for which the rowid degree sequence is calculated, the product of the number of times the value of this attribute appears in all other tables is the degree d of the value in the rowid degree sequence of the target table. At the same time, the value may also appear multiple times in the target table, with the number of occurrences counted. The contribution of the value to the rowid degree sequence of the target table is reflected by inserting count degree values ​​d into the degree sequence. 2.3 By setting a precision coefficient c, a series of buckets are created to store the degree value and the frequency of its occurrence, where the degree value stored in the i-th bucket ranges from... Within the interval, the degree values ​​obtained in step 2.2 will be dynamically allocated to the series buckets until step 2.2 is completed and all degree values ​​have been added. Then, all degree values ​​in the series buckets will be converted to the maximum degree value in the bucket, and the output can be used as a compressed degree sequence.

5. The optimized query method based on cardinality estimation of multi-table statistical information according to claim 3 or 4, characterized in that, During the merge calculation process, each calculated degree value can be immediately assigned to the corresponding bucket. Only the count of the bucket and the current maximum degree value need to be updated, without materializing the entire degree sequence.

6. The optimized query method based on cardinality estimation of multi-table statistical information according to claim 1, characterized in that, The transformation includes: Step a: Parse the original SQL into a join relationship diagram by extracting the join relationships; Step b: Based on the connection relationship diagram, organize the connection attributes into a star-shaped structure diagram by nodes; Step c: Based on the results obtained in step b, extract all tables connected to each attribute and treat them as a star schema to obtain a schematic diagram of the star schema connection relationship; Step d: Based on the results obtained in step c, transform the star schema back into a query to obtain the dual join corresponding to the original SQL.

7. An optimized query system for implementing the method of any one of claims 1-6, characterized in that, include: The system comprises a statistical information collection unit and a cardinality estimation unit. The statistical information collection unit collects statistical information based on the database table structure information to obtain star-structure statistical information. The cardinality estimation unit performs star-structure dual join transformation processing based on the input query, calls the statistical information collected by the statistical information collection unit, and estimates the cardinality of the query.

Images