A materialized view design method for database query optimization

Abstract

The present invention discloses a materialized view design method for database query optimization, comprising the following steps: for a PostgreSQL database, collecting historical query loads and corresponding patterns; constructing a Cosette pattern of the query load; using Cosette to perform equivalent identification, merging, and frequency statistics on subqueries; recommending the most frequently appearing candidate subqueries for actual materialization based on frequency and a greedy strategy; creating a materialized view in PostgreSQL, scoring and replacing the materialized view using a Newton's cooling law calculation formula; and rewriting the user's original query statement in the created materialized view environment for query optimization. The present invention conducts an in-depth analysis and research on view selection and view elimination strategies in materialized view design under a big data environment, proposes a view selection strategy based on a Cosette query statement equivalence prover, and proposes a materialized view scoring and elimination strategy based on Newton's cooling law. The efficiency and effectiveness of view design solutions under a big data environment are improved.

Description

Technical Field

[0001] The present invention belongs to the technical field of database query optimization, and in particular relates to a materialized view design method for database query optimization. Background Art

[0002] In the field of database query optimization, especially in online analytical processing (OLAP) systems, query workloads often contain overlapping subqueries, which incur significant duplicate storage and computational overhead. Building materialized views on these subqueries to avoid redundant computation and thus accelerate query performance is a common practice. To leverage the potential of materialized views, three key issues in the materialized view lifecycle must be addressed: 1) View design: Determine which views need to be materialized, including how to store, index, and deprecate them. 2) View maintenance: Efficiently update materialized views when base tables are updated. 3) View exploitation: Effectively leverage materialized views to accelerate query processing.

[0003] To optimize queries using materialized views, view selection is a crucial step in view design. This involves selecting a batch of materialized views from historical query workloads. However, existing materialized view selection relies primarily on manual selection based on expert experience. In a big data environment, manual selection is ineffective in effectively selecting candidate subqueries worth materializing.

[0004] Furthermore, manually eliminating views during view design is difficult. As materialized views are created, they consume more and more storage space. Materialized views built based on historical query loads may not benefit future query loads and need to be eliminated promptly to save storage space. Summary of the Invention

[0005] To address the shortcomings of the aforementioned background technologies, the present invention provides a view design method based on Cosette and Newton's cooling law. This method encompasses both a view selection strategy and a view elimination mechanism in view design. The view selection strategy selects candidate materialized views based on the Cosette query equivalence prover and uses a greedy strategy to further select views that require materialization. The view elimination mechanism scores and eliminates materialized views based on Newton's cooling law.

[0006] The object of the present invention is achieved through the following technical solutions:

[0007] A materialized view design method for database query optimization includes the following steps: step S1, collecting historical query loads and corresponding patterns for a PostgreSQL database; step S2, constructing a Cosette pattern of the query load;

[0008] In step S3, Cosette is used to identify equivalent subqueries, merge them, and perform frequency statistics. In step S4, the most frequently appearing candidate subqueries are recommended for actual materialization based on the frequency and greedy strategy.

[0009] Step S5: Create a materialized view in PostgreSQL, and score and replace the materialized view using Newton's law of cooling calculation formula;

[0010] Step S6: Under the created materialized view environment, the original query statement of the user is rewritten to optimize the query.

[0011] Specifically, the step S3 is to use the Cosette open API to perform logical equivalence identification and judgment on subqueries in the query load and combine and count the occurrence frequencies.

[0012] Specifically, step S3 further includes a step of reducing the number of API calls for pairwise equivalence judgment, including the following sub-steps:

[0013] Remove diverse selection predicates;

[0014] Sort by number of connections.

[0015] Specifically, step S3 further includes steps for improving the recognition effect, including the following sub-steps:

[0016] Sort the elements involved in different parts of the query statement in alphabetical order;

[0017] Sort the two operands on the left and right of the equal sign of each predicate in multiple join predicates in alphabetical order;

[0018] Sort multiple join predicates alphabetically.

[0019] Specifically, the Newton's law of cooling calculation formula in step S5 is:

[0020]

[0021] Among them, G(t n ) and G(t n-1 ) are t n and t n-1 The score of the materialized view at that moment, R match (t n ) is the hit reward function, R recommand (tn ) is the recommendation reward function; β′ is based on β, β is the cooling coefficient, β>0 and is a constant.

[0022] Specifically, the R match (t n ) The specific hit reward function is that when there is no hit at time t, the value returned by the function is 0. When the hit at time t is used for rewriting, its score is increased by 0 plus γ. Adjusting γ can adjust the proportion of the reward function in the score; the calculation formula is:

[0023]

[0024] Specifically, the R recommand (t n The recommendation reward function is as follows: at time t, if a recommendation service occurs, if the current materialized view is recommended again, a reward is given; if it is not recommended, the score remains unchanged; the calculation formula is:

[0025]

[0026] Beneficial effects of the present invention:

[0027] This paper conducts in-depth analysis and research on view selection and elimination strategies in materialized view design in big data environments. It proposes a view selection strategy based on the Cosette query statement equivalence prover and a materialized view scoring and elimination strategy based on Newton's cooling law. This improves the efficiency and effectiveness of view design solutions in big data environments. BRIEF DESCRIPTION OF THE DRAWINGS

[0028] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the embodiments or the description of the prior art. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on the structures shown in these drawings without paying any creative work.

[0029] Figure 1 This is a schematic diagram of materialized view registration and rewriting of the present invention;

[0030] Figure 2 This is a flow chart of candidate subquery judgment and merging based on Cosette of the present invention;

[0031] Figure 3 This is a view scoring and elimination flow chart process based on Newton's law of cooling of the present invention. DETAILED DESCRIPTION

[0032] It should be understood that the specific embodiments described herein are only used to explain the present invention and are not intended to limit the present invention.

[0033] In order to have a clearer understanding of the technical features, purposes and beneficial effects of the present invention, the technical solutions of the present invention are now described in detail below. Obviously, the implementation cases described are part of the embodiments of the present invention, not all of them, and should not be understood as limiting the scope of the implementation of the present invention. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative work are within the scope of protection of the present invention.

[0034] Example 1:

[0035] In this embodiment, Figure 1 The following example shows a query optimization based on a materialized view. To complete the materialized rewrite, you must first register the created materialized view with the optimizer. A query statement will be optimized in the optimizer. When the query hits the materialized view and the query is rewritten, the final execution plan will use some data from the materialized view (caching), thereby improving the execution speed. This is a typical example of trading space for time. The specific steps for implementation are as follows:

[0036] S1. Collect historical query loads and corresponding schemas for the PostgreSQL database. This is done by recording and collecting historical query statements at the query call point. Schema data for tables in the database can be obtained through query statements. For PostgreSQL, for example, this can be obtained by querying "information_schema" or "\d table name."

[0037]

[0038] S2. Construct the Cosette model for query load. Figure 2As mentioned above, when Cosette performs a logical equivalence check between two subqueries, it needs to input database schema information. This schema information is similar to the existing database table creation statements, but slightly different and unique to Cosette. The existing database schema needs to be converted to a Cosette-compatible schema. For example, the IMDb dataset Cosette Schema shows the schema definition of the IMDb dataset in Cosette, which consists of four main parts: 1) The schema definition of the IMDb tables in Cosette, such as title_schema and info_type_schema, which are the schema definitions for the title and info_type tables, respectively, containing column field names and types; 2) Cosette table creation statements, tabletitle(title_schema) and tableinfo_type(info_type_schema), which are the creation statements for the title and info_type tables, respectively; and 3) the definition statements for the queries to be checked for equivalence, queryq1`sql` and queryq2`cmpSql`, which are the definition statements for q1 and q2, respectively. 4) Determine the task definition, verifyq1q2; the task needs to verify the equivalence of q1 and q2.

[0039] The following is the Cosette Schema of the IMDb dataset:

[0040]

[0041]

[0042]

[0043] S3. Use Cosette to identify the equivalent of subqueries, merge and count the frequencies. The specific implementation method of step S3 is: after building the Cosette mode, use the Cosette open API to identify and judge the logical equivalence of each subquery in the query load and merge and count the frequency of occurrence. As shown, this is an interface call example of Cosette API. There are 3 parameters in total: 1) the URL call address of Cosette API; 2) "api_key" in the data dictionary, which is the authorization key obtained after logging into Cosette; 3) "query" in the data dictionary, that is, the above-mentioned Cosette Schema, which requires specifying q1 and q2 to be compared. There are two return situations, one is equality, as shown in the example of successful return of Cosette operation, and the other is inequality, as shown in the example of failed return of Cosette operation. In the case of inequality, it mainly contains counterexamples (counterexamples) that make the two query statements unequal.

[0044] The following is an example of calling the Cosette API:

[0045]

[0046] The following is an example of a successful Cosette operation response:

[0047]

[0048] The following is an example of a Cosette operation failure response:

[0049]

[0050]

[0051] Each equivalence judgment is a comparison between two query statements. If no processing is done, the time complexity of the comparison in a query load of N is O(n 2As the number of query statements requiring evaluation increases, the number of Cosette calls increases dramatically, significantly increasing the time overhead. To improve speed, queries can be categorized based on query characteristics. Query statements belonging to different categories will not be equivalent, reducing the need to verify the equivalence of queries between different categories. Before categorization, queries in historical query workloads require some preprocessing and normalization. First, remove the selection predicates from the query statements. Because a column has a wide range of values, different selection predicates represent semantically equivalent queries. Second, sort the query statements, including the order of projected columns, data source tables, and join predicates. This sorting facilitates optimization of the subsequent materialized view selection algorithm, improving efficiency and resulting in more intuitive results. For example, sort the operands to the left and right of the equal sign in each of the three join predicates in alphabetical order.

[0052] After normalization, queries can be classified based on the number of join predicates in the query statements. Queries with the same number of join predicates are grouped together, and the number of occurrences of the query statements is recorded and counted. At this point, queries with the same number of join predicates have been grouped together. To further identify queries with different wordings, such as the logically equivalent SEELCT t1.column FROM tab as t1 and SELECT t2.column FROM tab as t2, the Cosette interface can be used within a category to identify equivalent queries, further remove duplicates, and finally merge logically equivalent queries and their frequencies. The specific process is as follows: Figure 1 shown.

[0053] S4. Recommend the most frequently appearing candidate subqueries for actual materialization based on frequency and greedy strategy. Based on the query statements after equivalent identification and merging and the corresponding frequency statistics, recommend the system-set k candidate subqueries from high to low as the recommended materialized views.

[0054] S5. In PostgreSQL, create materialized views and use Newton's law of cooling to score and replace them. As query loads change over time, a materialized view may generate revenue for the system over a period of time. However, over time, the original materialized view may no longer support new query loads through query rewrites based on the materialized view, thus failing to reduce computational overhead. Therefore, a scoring mechanism is required to evaluate the profitability trends of materialized views. The effect of materialized views selected based on historical query statements on the database and workload changes dynamically and is affected by multiple factors: 1) Time factor: If a materialized view is not used for materialized rewrite, it is a cold materialized view and its importance gradually decreases over time, from a newly recommended hot materialized view to a cold materialized view; 2) Recommendation factor: If a materialized view is frequently recommended by the view selection strategy, it indicates that it frequently appears in the current query workload and its importance needs to be increased; 3) Rewrite factor: If a materialized view is frequently rewritten, it indicates that it not only appears in the existing query workload but is also selected by the optimizer, and is more likely to bring benefits, so its importance needs to be increased.

[0055] Based on this, Figure 3 As shown in the figure, we propose a materialized view scoring mechanism to rank the importance of materialized views. The materialized view score is influenced by factors such as the passage of time, the materialized view's effectiveness on query workloads, and the subsequent recommendation of the materialized view. The effectiveness of a materialized view refers to whether it can be used for query rewrites. If a materialized view is used for query rewrites, its score increases; otherwise, its score remains unchanged. Subsequent recommendation of a materialized view is similar, with two main scenarios: if it is recommended, its score increases; if it is not recommended, its score remains unchanged. Newton's law of cooling is a law in physics that describes how the temperature of an object changes over time. When an object comes into contact with its surroundings, its internal energy transfers to the surrounding environment, causing the object's temperature to gradually decrease. This law applies to most physical systems and is widely used in engineering and industry, such as refrigeration systems, air conditioning systems, and chillers. Newton's law of cooling describes how a high-temperature object gradually cools in a low-temperature environment. It states that the rate of temperature change is proportional to the temperature difference between the object and the surrounding environment. That is, the greater the object's temperature above the surrounding environment, the faster its temperature decreases. The formula for this law is as follows:

[0056] T′(t)=-β(T(t)-H)

[0057] Among them, T(t) is the function of temperature (T) on time (t). In calculus, T(t) is the derivative of the temperature function, which represents the rate of temperature change (cooling or heating). H represents the room temperature, and T(t)-H is the temperature between the current temperature and the room temperature. The constant β (β>0) represents the proportional relationship between the room temperature and the cooling rate. The minus sign in front indicates cooling. Different substances have different β values. By further solving this formula, we can get the following formula:

[0058]

[0059] Where t0 is the current time, and T0 is the object's current temperature. In the real physical world, an object's temperature is affected by many factors, but materialized views only consider metrics like time, hit rate, and recommendation status, ignoring factors like ambient temperature. Therefore, to make the formula applicable to materialized views, set the ambient temperature to 0, resulting in the following formula:

[0060]

[0061]

[0062] To better reflect the relationship between the number of materialized views, the space occupied by materialized views, and the score, the cooling coefficient β in the above formula is adjusted based on the preset number of materialized views, the space occupied by materialized views, and the existing number and space occupied. As the number and space occupied by existing materialized views increase, the more resources occupied by materialized views in the system become, and the faster the score decreases.

[0063] In addition, whether the materialized view is hit or not, whether it is recommended or not, and other indicators should also be reflected in the scoring mechanism. match (t) This hit reward function, when there is no hit at time t, the value returned by the function is 0. When the hit at time t is used for rewriting, its score is increased by 0 plus γ. Adjusting this value can adjust the proportion of the reward function in the score.

[0064] Is it recommended to introduce materialized views into R again? recommand (t) This recommendation reward function, at time t, if a recommendation service occurs, if the current materialized view is recommended again, then a reward is given; if it is not recommended, then the score remains unchanged, which can be obtained as follows:

[0065]

[0066]

[0067] The scoring formula after introducing the reward function is:

[0068]

[0069] Changing T from temperature to G (Grade) and further adjusting the time subscript yields the final scoring formula:

[0070]

[0071] Among them, G(t n ) and G(t n-1 ) are t n and t n-1 The score of the materialized view at that moment, R match (t n ) and R recommand (t n ) is the above reward function, β′ is based on β, and β is the cooling coefficient. Adjusting this constant can change the rate of change of the materialized view score.

[0072] S6. Under the created materialized view environment, the user's original query statement is rewritten to perform query optimization.

[0073] The basic principles, main features, and advantages of the present invention are shown and described above. Those skilled in the art should understand that the present invention is not limited to the above embodiments. The above embodiments and descriptions are merely illustrative of the principles of the present invention. Various changes and modifications may be made to the present invention without departing from the spirit and scope of the present invention. Such changes and modifications are intended to fall within the scope of the present invention. The scope of the present invention is defined by the appended claims and their equivalents.

[0074] It should be noted that for the aforementioned various method embodiments, for the sake of simplicity, they are all expressed as a series of action combinations, but those skilled in the art should be aware that this application is not limited by the order of the actions described, because according to this application, some steps can be performed in other orders or simultaneously. Secondly, those skilled in the art should also be aware that the embodiments described in the specification are all preferred embodiments, and the actions and units involved are not necessarily required by this application.

[0075] In the above embodiments, the description of each embodiment has its own focus. For parts that are not described in detail in a certain embodiment, reference can be made to the relevant descriptions of other embodiments.

[0076] Those skilled in the art will appreciate that all or part of the processes in the above-described method embodiments can be implemented by instructing related hardware through a computer program. The program can be stored in a computer-readable storage medium, and when executed, the program can include the processes in the above-described method embodiments. The storage medium can be a magnetic disk, an optical disk, a ROM, a RAM, or the like.

[0077] The above disclosure is merely a preferred embodiment of the present invention and certainly cannot be used to limit the scope of the present invention. Therefore, equivalent changes made according to the claims of the present invention are still within the scope of the present invention.

Claims

1. A materialized view design method for database query optimization, characterized in that: The following steps are involved: Step S1: For the PostgreSQL database, collect historical query loads and corresponding patterns; Step S2, constructing a Cosette pattern for the query load; Step S3: Use Cosette to perform equivalent identification, merging, and frequency statistics on subqueries; Step S4: recommend the most frequently appearing candidate subqueries for actual materialization based on the frequency and greedy strategy; Step S5: Create a materialized view in PostgreSQL, and score and replace the materialized view using Newton's law of cooling calculation formula; Step S6: Under the created materialized view environment, the original query statement of the user is rewritten to optimize the query.

2. A materialized view design method for database query optimization according to claim 1, characterized in that: The step S3 specifically uses the Cosette open API to perform logical equivalence identification and judgment on the sub-queries in the query load and combine and count the occurrence frequencies.

3. A materialized view design method for database query optimization according to claim 2, characterized in that: The step S3 further includes a step of reducing the number of API calls for pairwise equivalence judgment, including the following sub-steps: Remove diverse selection predicates; Sort by number of connections.

4. A materialized view design method for database query optimization according to claim 2, characterized in that: The step S3 further includes steps for improving the recognition effect, including the following sub-steps: Sort the elements involved in different parts of the query statement in alphabetical order; Sort the two operands on the left and right of the equal sign of each predicate in multiple join predicates in alphabetical order; Sort multiple join predicates alphabetically.

5. The materialized view design method for database query optimization according to claim 1, characterized in that: The Newton's law of cooling calculation formula in step S5 is: in, and They are and The score of the materialized view at that moment, is the hit reward function, is the recommendation reward function; based on , is the cooling coefficient, And it is a constant.

6. A materialized view design method for database query optimization according to claim 5, characterized in that: described The specific hit reward function is: If there is no hit, the function returns the value , when the moment When a hit is used to rewrite, its score is increased by 0 ,Adjustment The proportion of the reward function in the scoring can be adjusted; The calculation formula is: 。 7. A materialized view design method for database query optimization according to claim 5, characterized in that: described The recommendation reward function is specifically: ,If a recommendation service occurs, if the current materialized view is recommended again, it will be rewarded; if it is not recommended, the score will remain unchanged; The calculation formula is: 。

Images