Methods, apparatus, electronic devices and storage media for generating materialized views

Abstract

This application relates to a method, apparatus, electronic device, and storage medium for generating materialized views. The method involves: filtering target query patterns from database query logs; generating candidate materialized view creation statements corresponding to the target query patterns; creating candidate materialized views based on these statements in a resource-isolated sandbox environment and quantitatively evaluating the candidate materialized views; deciding whether to approve and deploy the candidate materialized views; and conducting a health assessment of deployed materialized views, performing corresponding optimization operations based on the assessment results. This achieves automated and precise selection of high-value optimization targets, reducing reliance on senior experts. Quantitative evaluation of candidate materialized views ensures that deployed materialized views provide clear benefits. Furthermore, multi-dimensional health assessments of deployed materialized views automatically identify and eliminate inefficient materialized views, enabling continuous and automated optimization and recycling of computing and storage resources.

Description

Technical Field

[0001] This application relates to the field of computer technology, and in particular to a method, apparatus, electronic device, and storage medium for generating materialized views. Background Technology

[0002] With the development of the Internet, data analysis and interactive reports have extremely high requirements for query response time. Existing technologies generally use materialized views to pre-calculate and store query results to accelerate queries.

[0003] However, existing materialized view designs rely on expert experience and lack reliable quantitative verification before deployment, resulting in high labor costs and low query performance. The lack of continuous monitoring of the efficiency of deployed views causes the performance optimization effect of materialized views to gradually diminish over time. Summary of the Invention

[0004] This application provides a method, apparatus, electronic device, and storage medium for generating materialized views, in order to solve the problem of how to achieve a fully automated process for uploading and recycling materialized views, thereby improving query performance while continuously optimizing computing and storage resources.

[0005] In a first aspect, this application provides a method for generating a materialized view, the method comprising: Filter the target query pattern from the database query log; Based on the target query pattern and its associated data table metadata, generate candidate materialized view creation statements; In a resource-isolated sandbox environment, candidate materialized views are created based on the candidate materialized view creation statement, and the candidate materialized views are quantitatively evaluated. Based on the quantitative evaluation results, a decision is made on whether the candidate materialized view should be approved for deployment. Perform a health assessment on the materialized views that have been launched, and perform corresponding optimization operations on the materialized views based on the health assessment results.

[0006] In one possible implementation, filtering the target query pattern from the database query log includes: The set of slow query statements is determined from the database query log based on preset slow query filtering rules; For each slow query statement in the slow query statement set, perform abstract syntax tree parsing to obtain the corresponding query statement structure; Slow query statements with the same query statement structure are grouped into a slow query statement subset, resulting in multiple slow query statement subsets; each slow query statement subset corresponds to a query pattern. Based on the subset of slow query statements corresponding to each query pattern, a composite popularity score is determined for each query pattern, and the target query pattern is selected based on the composite popularity score.

[0007] In one possible implementation, determining a composite popularity score for each of the query patterns includes: For each of the query patterns, perform the following processing: Retrieve query records of all slow query statements corresponding to the query pattern from the database query log; The query cost data for the query mode is determined based on the query records; wherein the query cost data includes at least: query frequency, average time consumption, and average data scan volume; The composite popularity score corresponding to the query pattern is obtained by weighting each data item in the query cost data.

[0008] In one possible implementation, generating candidate materialized view creation statements based on the target query pattern and its associated data table metadata includes: Obtain the metadata information of the data table associated with the slow query statement corresponding to the target query pattern; Prompt words are constructed based on the metadata information and preset database constraint rules; The prompt words are input into a pre-trained large language model so that the large language model outputs candidate materialized view creation statements; The candidate materialized view creation statements output by the large language model are validated, and the candidate materialized view creation statements that pass the validation are determined as the final candidate materialized view creation statements.

[0009] In one possible implementation, the quantitative evaluation of the candidate materialized views includes: Execute a first query operation that does not use the candidate materialized view to obtain first execution performance data; Execute a second query operation using the candidate materialized view to obtain second execution performance data; Based on the first execution performance data and the second execution performance data, verify the consistency of the execution results of the first query operation and the second query operation; If the execution results of the first query operation and the second query operation are found to be consistent, the candidate materialized view is quantitatively evaluated based on the first execution performance data and the second execution performance data.

[0010] In one possible implementation, the quantitative evaluation result includes evaluation values ​​across multiple dimensions; the step of deciding whether to approve the candidate materialized view for deployment based on the quantitative evaluation result includes: The evaluation value of each dimension is compared with the preset threshold corresponding to that dimension; If the evaluation value of each dimension meets the corresponding preset threshold, the decision is made to approve the candidate materialized view to go online. If the evaluation value of any dimension fails to meet the corresponding preset threshold, the decision is made not to approve the candidate materialized view for online deployment.

[0011] In one possible implementation, the health assessment of the online materialized view includes: Periodically retrieve the health assessment index data of the online materialized views from the database query log; The health score of the online materialized view is determined based on the health assessment index data. The health level is determined based on the health score of the materialized view that has been launched and the preset grading rules.

[0012] In one possible implementation, performing corresponding optimization operations on the online materialized view based on the health assessment results includes: If the health assessment result indicates that the online materialized view is at the first health level, the normal operation of the online materialized view shall be maintained. An alarm is triggered if the health assessment result indicates that the online materialized view is at the second health level. If the health assessment result indicates that the online materialized view is at the third health level, the online materialized view is shut down, and a decommissioning process is performed on the online materialized view after the observation period ends.

[0013] Secondly, this application provides an apparatus for generating materialized views, the apparatus comprising: The target query pattern filtering module is used to filter out target query patterns from the database query logs; The view creation statement generation module is used to generate candidate materialized view creation statements based on the target query pattern and its associated data table metadata. The view quantification evaluation module is used to create candidate materialized views based on the candidate materialized view creation statement in a resource-isolated sandbox environment, and to perform quantification evaluation on the candidate materialized views. The view deployment decision module is used to decide whether the candidate materialized view is approved for deployment based on the quantitative evaluation results. The view health assessment module is used to assess the health of the online materialized views and perform corresponding optimization operations on the online materialized views based on the health assessment results.

[0014] In one possible implementation, the target query pattern filtering module includes: The slow query statement set determination unit is used to determine the slow query statement set from the database query log based on preset slow query filtering rules; The query statement structure acquisition unit is used to perform abstract syntax tree parsing on each slow query statement in the slow query statement set to obtain the corresponding query statement structure. The slow query statement subset determination unit is used to group slow query statements with the same query statement structure into a slow query statement subset, thereby obtaining multiple slow query statement subsets; each slow query statement subset corresponds to a query pattern. The target query pattern filtering unit is used to determine a composite popularity score for each query pattern based on the slow query statement subset corresponding to each query pattern, and to filter out target query patterns based on the composite popularity score.

[0015] In one possible implementation, the target query pattern filtering unit is specifically used for: For each of the query patterns, perform the following processing: Retrieve query records of all slow query statements corresponding to the query pattern from the database query log; The query cost data for the query mode is determined based on the query records; wherein the query cost data includes at least: query frequency, average time consumption, and average data scan volume; The composite popularity score corresponding to the query pattern is obtained by weighting each data item in the query cost data.

[0016] In one possible implementation, the view creation statement generation module is specifically used for: Obtain the metadata information of the data table associated with the slow query statement corresponding to the target query pattern; Prompt words are constructed based on the metadata information and preset database constraint rules; The prompt words are input into a pre-trained large language model so that the large language model outputs candidate materialized view creation statements; The candidate materialized view creation statements output by the large language model are validated, and the candidate materialized view creation statements that pass the validation are determined as the final candidate materialized view creation statements.

[0017] In one possible implementation, the view quantification evaluation module is specifically used for: Execute a first query operation that does not use the candidate materialized view to obtain first execution performance data; Execute a second query operation using the candidate materialized view to obtain second execution performance data; Based on the first execution performance data and the second execution performance data, verify the consistency of the execution results of the first query operation and the second query operation; If the execution results of the first query operation and the second query operation are found to be consistent, the candidate materialized view is quantitatively evaluated based on the first execution performance data and the second execution performance data.

[0018] In one possible implementation, the quantitative evaluation result includes evaluation values ​​across multiple dimensions; the view-on-line decision module is specifically used for: The evaluation value of each dimension is compared with the preset threshold corresponding to that dimension; If the evaluation value of each dimension meets the corresponding preset threshold, the decision is made to approve the candidate materialized view to go online. If the evaluation value of any dimension fails to meet the corresponding preset threshold, the decision is made not to approve the candidate materialized view for online deployment.

[0019] In one possible implementation, the view health assessment module is specifically used for: Periodically retrieve the health assessment index data of the online materialized views from the database query log; The health score of the online materialized view is determined based on the health assessment index data. The health level is determined based on the health score of the materialized view that has been launched and the preset grading rules.

[0020] In one possible implementation, the view health assessment module is specifically used for: If the health assessment result indicates that the online materialized view is at the first health level, the normal operation of the online materialized view shall be maintained. An alarm is triggered if the health assessment result indicates that the online materialized view is at the second health level. If the health assessment result indicates that the online materialized view is at the third health level, the online materialized view is shut down, and a decommissioning process is performed on the online materialized view after the observation period ends.

[0021] Thirdly, this application provides an electronic device, including: a processor and a memory, wherein the processor is configured to execute a materialized view generation program stored in the memory to implement the materialized view generation method described in any one of the first aspects.

[0022] Fourthly, this application provides a storage medium storing one or more programs that can be executed by one or more processors to implement the method for generating materialized views as described in the first aspect.

[0023] Compared with the prior art, the technical solution provided in this application has the following advantages: The method provided in this application filters out target query patterns from database query logs; generates candidate materialized view creation statements corresponding to the target query patterns; creates candidate materialized views based on the candidate materialized view creation statements in a resource-isolated sandbox environment, and performs quantitative evaluation on the candidate materialized views; then decides whether the candidate materialized views are approved for deployment; performs health assessment on the deployed materialized views, and performs corresponding optimization operations based on the assessment results. This achieves automated and precise screening of high-value optimization targets, replacing inefficient manual screening; generates view creation statements based on a large language model, reducing reliance on senior experts; ensures clear benefits for deployed materialized views through quantitative evaluation of candidate materialized views before deployment; and automatically identifies and eliminates inefficient materialized views through multi-dimensional health assessment of deployed materialized views, achieving continuous and automated optimization and recycling of computing and storage resources. Attached Figure Description

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

[0025] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, for those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0026] One or more embodiments are illustrated by way of example with reference numerals in the accompanying drawings. These illustrations do not constitute a limitation on the embodiments. Elements with the same reference numerals in the drawings are denoted as similar elements. Unless otherwise stated, the figures in the drawings are not to be limited by scale.

[0027] Figure 1 A flowchart illustrating an embodiment of a method for generating a materialized view provided in this application; Figure 2A flowchart illustrating an embodiment of another method for generating a materialized view provided in this application; Figure 3 A flowchart illustrating an embodiment of a method for generating a materialized view provided in this application. Figure 4 A structural block diagram of a materialized view generation apparatus provided in an embodiment of this application; Figure 5 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application. Detailed Implementation

[0028] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer, the technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0029] The following disclosure provides numerous different embodiments or examples for implementing various structures of this application. To simplify the disclosure, specific examples of components and arrangements are described below. These are merely examples and are not intended to limit the scope of this application. Furthermore, reference numerals and / or letters may be repeated in different examples. Such repetition is for simplification and clarity and does not in itself indicate a relationship between the various embodiments and / or arrangements discussed.

[0030] To achieve a fully automated process for deploying and recycling materialized views, improving query performance while continuously optimizing computing and storage resources, this application provides a method, apparatus, electronic device, and storage medium for generating materialized views. This enables automated and precise screening of high-value optimization targets, replacing inefficient manual screening. It generates view creation statements based on a large language model, reducing reliance on senior experts. Quantitative evaluation of candidate materialized views before deployment ensures clear benefits after deployment. Furthermore, multi-dimensional health assessments of deployed materialized views automatically identify and eliminate inefficient ones, achieving continuous and automated optimization and recycling of computing and storage resources.

[0031] Figure 1 A flowchart illustrating an embodiment of a method for generating a materialized view provided in this application includes the following steps: Step 101: Filter out the target query pattern from the database query log.

[0032] Database query logs refer to files or tables that record information about each query execution within a database system. They typically include fields such as the SQL statement text, execution time, scanned data volume, and user identifier. Essentially, they are logs of all query operations automatically recorded by the database during actual business operations. This includes information such as who performed the query (UserID), what was queried (SQL text), the query duration (execution time), and the amount of data scanned (number of rows scanned), serving as the primary basis for filtering.

[0033] The target query pattern can refer to an abstract representation of a class of queries that are identified by algorithms from massive query logs, have high optimization value (such as being frequently executed, taking a long time, and consuming a lot of resources) and belong to the same business logic.

[0034] In one embodiment, a set of slow query statements that meet preset rules is periodically collected from the database query log. An SQL parser is used to convert the text of the slow query statements into an abstract syntax tree (AST), and this AST is then subjected to deep normalization to obtain the query statement structure of the slow query statement. The hash value of this query statement structure is then calculated as the unique query fingerprint of the slow query statement. The query statement structure can be understood as a structured logical representation of the query statement structure, serving as the source and description object for the query fingerprint. The query fingerprint can be understood as a machine-readable, irreversible digital fingerprint, primarily used for efficient comparison and clustering, and is a digital, hashed representation of the query statement structure.

[0035] Furthermore, slow queries with the same query fingerprint are grouped into the same query pattern. For example, if two slow query SQL statements have the same query fingerprint, it means that the query statement structure is the same and belongs to the same query statement structure, i.e., they belong to the same query pattern. Therefore, it can be understood as using fingerprints as the aggregation unit to group slow queries with the same fingerprint into the same query pattern; or it can be understood as using query statement structure as the unit to group slow queries with the same query statement structure into the same query pattern. Because fingerprints are a digital representation, using fingerprints for comparison and aggregation can more quickly aggregate slow queries that meet the conditions into the same query pattern.

[0036] Then, based on preset slow query rules, the query patterns that ultimately satisfy the preset slow query rules are selected from the multiple aggregated query patterns as high-value target query patterns. Furthermore, the final selected target query patterns can be multiple query patterns that satisfy the preset rules. If there are multiple target query patterns, they can be grouped according to the user identifier corresponding to the target query pattern, i.e., determining who is using this target query pattern to perform the relevant query operation. Target query patterns associated with the same user are grouped into the same business group for processing. Subsequently, the generation and deployment of materialized views can be performed on a per-business-group basis. This application embodiment does not impose any limitations on this.

[0037] For example, in an e-commerce data analysis scenario, the system logs may contain tens of thousands of queries. After the filtering process described in the above embodiments, the system may identify two high-frequency target query patterns: Query Pattern A (corresponding to an aggregation query): SELECT product_category, DATE(order_time), SUM(amount) FROM orders GROUPBY product_category, DATE(order_time). Query Pattern B (corresponding to a multi-table join query): SELECT u.region, COUNT(*) FROM orders o JOIN users u ON o.user_id = u.id WHERE o.status=completed GROUP BY u.region. These two patterns, due to their frequent execution and involvement of large amounts of data aggregation or joining, meet the preset slow query rules and are therefore identified as target query patterns with high optimization value.

[0038] For another example, after parsing and normalizing the selected slow query statements, corresponding query fingerprints are obtained. Using these fingerprints as the aggregation unit, slow queries with the same fingerprint are grouped into the same query pattern, resulting in multiple query patterns. Based on preset slow query rules, the system selects a query pattern A (corresponding to the aggregate query) with high optimization value from these multiple query patterns: SELECT product_category, DATE(order_time), SUM(amount) FROM orders GROUP BY product_category, DATE(order_time). This query pattern A is then determined as the final target query pattern.

[0039] The following explanations will use a single target query pattern that satisfies the preset slow query rules as the unit. The following explanations will no longer cover cases where the target query pattern contains multiple query patterns, or cases where services are grouped based on user identifiers. Instead, the explanations will focus on a single target query pattern; the same applies to service groups composed of multiple target query patterns with the same user identifier.

[0040] Step 102: Based on the target query pattern and its associated data table metadata, generate candidate materialized view creation statements.

[0041] Data table metadata refers to information describing the structure of a database table, i.e., table definition (DDL), including table name, column names, column data types, primary key, partition information, etc. It may also contain statistical information, such as the total number of rows in the table and the number of distinct values ​​(NDV) of a certain column.

[0042] The candidate materialized view creation statement refers to the SQL statement automatically generated by the system based on the target query pattern to create a materialized view. Essentially, it can be understood as a SQL statement conforming to database syntax (CREATE MATERIALIZED VIEW ...) that defines a pre-computed and stored query result set.

[0043] Materialized views pre-calculate and store the results of high-frequency, complex queries in the database (such as multi-table joins and aggregation queries). When similar queries are requested later, the database optimizer can directly read the pre-stored results without repeating complex calculations, significantly reducing query response time, data scanning, and computational resource consumption. This efficiently supports scenarios with high query speed requirements, such as real-time data analysis and data reporting. Essentially, materialized views pre-calculate and store the result sets of complex queries, allowing subsequent identical queries to quickly read data directly from this result set without repeating cumbersome join and aggregation operations, thus transparently achieving a significant acceleration in query performance.

[0044] In one embodiment, the names of the data tables involved in the target query pattern are obtained, and the metadata information of these data tables is extracted from the database. Structured prompts are generated by combining the task description and database constraints. These structured prompts are then input into a large language model to guide its reasoning, step-by-step analysis, design, and output of candidate materialized view creation statements. The candidate materialized view creation statements generated by the large language model are then subjected to syntax parsing and basic rule checks to ensure their correct structure and compliance with rewriting specifications, resulting in the final candidate materialized view creation statements.

[0045] For example, the system automatically extracts metadata information from all tables involved in the target query pattern and constructs prompts such as: The following information is structured into an "instruction" for the large language model: Task: "Design a materialized view for the slow query statement corresponding to the following target query pattern to speed up the query." Query pattern: Provides the normalized query statement structure text corresponding to the slow query SQL statement of the target query pattern. Related table structure: Provides metadata information of the data tables. Constraints and requirements: Specifies that the generated materialized view must conform to database specifications. The large language model ultimately outputs one or more candidate materialized view creation statements: `CREATE MATERIALIZED VIEW ... AS SELECT ...` statement. Then, the database statements generated by the large language model are checked for syntax and basic rules to ensure their correct format.

[0046] Step 103: In a resource-isolated sandbox environment, create candidate materialized views based on the candidate materialized view creation statement, and perform quantitative evaluation on the candidate materialized views.

[0047] A resource-isolated sandbox environment can refer to a test database environment that is completely isolated from the production environment. It has independent computing and storage resources and is used to conduct performance testing securely without affecting online business.

[0048] Quantitative evaluation can refer to accurately calculating the benefits and costs brought by materialized views by running comparative experiments to obtain measurable performance indicators (such as query time and scanned data volume).

[0049] The above comparative experiment can be a dual-run experiment: run the same query without materialized views (Group A) and with materialized views (Group B) on the same environment and data to compare the results.

[0050] In one embodiment, experiments A and B are run separately in a resource-isolated sandbox environment. Experiment A: With materialized view rewriting disabled, the slow query SQL statement corresponding to the target query pattern is executed, and the execution time T_base and scan data volume IO_base are recorded. Experiment B: In the sandbox, the candidate materialized view creation statement generated in the above steps is executed to construct the candidate materialized view. Then, the materialized view rewriting function is enabled, and the same slow query SQL statement is executed again. The new execution time T_mv and scan data volume IO_mv are recorded. Based on the experimental results of the two groups (such as execution time and scan volume), the above candidate materialized views are quantitatively evaluated to obtain the quantitative evaluation results.

[0051] For example, a sandbox test was conducted on the candidate materialized view `mv_orders_daily_category`. Experiment A: A direct grouping and aggregation query was performed on the large `orders` table, taking 12 seconds and scanning 10GB of data. Experiment B: The query was automatically rewritten by the optimizer, directly reading results from the smaller aggregation table `mv_orders_daily_category`, taking 2 seconds and scanning 0.5GB of data. Based on the experimental data obtained above, the candidate materialized view was quantitatively evaluated. Specifically, the query performance metric R_speed = 12 ÷ 2 = 6, and the resource saving rate R_io = (10 - 0.5) ÷ 10 = 0.95 (95%). Furthermore, the storage cost C_storage occupied by the candidate materialized view was estimated to be 1GB from the database. The above query performance metric, resource saving rate, and storage cost constitute the quantitative evaluation results of the candidate materialized view. This is merely an example, and the embodiments of this application do not impose limitations.

[0052] Step 104: Based on the quantitative evaluation results, decide whether to approve the candidate materialized view for online deployment.

[0053] In one embodiment, the process of obtaining the quantitative evaluation results from the above steps and automatically determining whether to allow the candidate materialized view to be deployed to the production environment based on the preset quantitative evaluation threshold and quantitative evaluation rules is described.

[0054] For example, based on the above steps, a series of admission criteria that must be met simultaneously can be set. For instance, the query result sets in the databases retrieved by the two sets of experiments must be identical, thus proving that the SQL queries executed by the two sets of experiments are the same, satisfying the control variable method. Furthermore, the quantitative evaluation result obtained from the above steps on the candidate materialized view must be greater than a preset threshold. For example, the query performance index must be greater than the query performance index threshold; the resource saving rate R_io must be greater than the I / O threshold; and the storage cost occupied by the candidate materialized view cannot be too high. Once all the above admission criteria are met, the candidate materialized view can be approved for deployment to the production environment. This is merely an example, and the embodiments of this application do not impose limitations.

[0055] Step 105: Perform a health assessment on the online materialized views and perform corresponding optimization operations on the online materialized views based on the health assessment results.

[0056] Health assessment refers to the continuous, multi-dimensional performance monitoring and scoring of materialized views already in effect in a production environment. Essentially, it can be understood as continuously quantifying the value status of materialized views in the production environment through a comprehensive scoring model.

[0057] In one embodiment, a health score is periodically calculated for each materialized view that has been deployed to the production environment. Specifically, the operational status data of the deployed materialized views is acquired as health index data, and a health score is calculated based on the health index data. A health level is determined based on the health score, and the health level is used as the health assessment result. Corresponding optimization operations are then formulated for each deployed materialized view with a different health level.

[0058] For example, health metric data for each materialized view is periodically collected from the database system tables, such as: the number of queries hit, the total time saved by hit queries, the storage size occupied, and the refresh time. The above health metric data is then weighted and summed to obtain a final health score. A health score greater than 80 is designated as the first health level; a health score between 30 and 80 is designated as the second health level; and a health score less than 30 is designated as the third health level.

[0059] For another example, no optimization operations can be performed on online materialized views with a first health level; alarm information can be triggered for online materialized views with a second health level; and online materialized views with a third health level can be directly shut down, with a retirement process initiated after the observation period. This is merely an example, and the embodiments of this application do not limit the specific optimization operations.

[0060] The method provided in this application involves: filtering target query patterns from database query logs; generating candidate materialized view creation statements corresponding to the target query patterns; creating candidate materialized views based on the candidate materialized view creation statements in a resource-isolated sandbox environment and quantitatively evaluating the candidate materialized views; deciding whether to approve the candidate materialized views for deployment; and conducting a health assessment of deployed materialized views and performing corresponding optimization operations based on the assessment results. This achieves automated and precise screening of high-value optimization targets, replacing inefficient manual screening; generating view creation statements based on a large language model reduces reliance on senior experts; quantitatively evaluating candidate materialized views before deployment ensures clear benefits for deployed materialized views; and performing multi-dimensional health assessments of deployed materialized views automatically identifies and eliminates inefficient materialized views, achieving continuous and automated optimization and recycling of computing and storage resources.

[0061] Figure 2 A flowchart illustrating an embodiment of another method for generating materialized views provided in this application is shown. Figure 1 Based on the illustrated process, this section mainly describes how to determine the target query pattern and generate candidate materialized view creation statements based on the target query pattern, including the following steps: Step 201: Determine the set of slow query statements from the database query log based on the preset slow query filtering rules.

[0062] Slow query filtering rules can be a set of predefined conditions used to identify inefficient query statements in the database query log. These conditions are typically based on performance metrics such as execution time, amount of data scanned, or number of rows returned.

[0063] Database query logs refer to a collection of information automatically recorded by the database system about all executed queries. They typically include fields such as SQL text, execution time, scanned data volume, user ID, and timestamp.

[0064] The slow query statement set can refer to the collection of all SQL statements that meet the slow query filtering rules and their related execution information extracted from the log according to the filtering rules.

[0065] In one embodiment, the system periodically (e.g., every minute, every hour) scans the query log table. For each log record, its T_base (execution time) and IO_base (number of rows scanned) fields are checked. If T_base > execution time threshold (e.g., 5 seconds) or IO_base > number of rows scanned threshold (e.g., 1 million rows), the original SQL text in that log record is added to the slow query statement set.

[0066] For example, in an e-commerce order database, a default slow query rule could be "queries with an execution time > 3 seconds or a number of rows scanned > 500,000 are considered slow queries." The system selects 1,000 records from the logs; these queries collectively constitute the slow query statement set.

[0067] Step 202: Perform abstract syntax tree parsing on each slow query statement in the slow query statement set to obtain the corresponding query statement structure.

[0068] Abstract Syntax Tree (AST) parsing refers to using an SQL parser to convert text-formatted SQL statements into a tree-like data structure (AST) representing their syntax structure. Tree nodes represent SQL keywords, table names, column names, operators, etc.

[0069] The query statement structure can refer to the query logic "skeleton" represented by the normalized AST. Normalization aims to eliminate differences that do not affect semantics, such as replacing specific constant values ​​with parameters, placeholders, and uniform aliases.

[0070] In one embodiment, the SQL parser is invoked to parse each slow query SQL into an original abstract syntax tree (AST), and the AST is subjected to deep normalization to obtain the query statement structure corresponding to the query SQL statement.

[0071] For example, normalization can include constant parameterization: replacing specific constant values ​​with placeholders "?". For instance, WHERE user_id = 123 becomes WHERE user_id = ?. Alias ​​removal: replacing aliases for tables and columns with their original or normalized names. Finally, the conditions or expressions in the SQL statement are normalized and sorted.

[0072] For example, the slow query statement `SELECT user_id FROM orders WHERE create_time>2024-01-01 AND status = 1` can be represented as `SELECT user_id FROM orders WHERE create_time>? AND status = ?` after AST parsing and normalization (constant parameterization). Furthermore, another SQL statement, `SELECT user_id FROM orders WHERE status = 2 AND create_time>2024-02-01`, has the same structure after normalization (only the parameter values ​​are different), therefore they have the same query statement structure.

[0073] Step 203: Group slow query statements with the same query statement structure into a slow query statement subset, resulting in multiple slow query statement subsets; each slow query statement subset corresponds to a query pattern.

[0074] Having the same query structure means that after the normalization process described above, the ASTs obtained from two or more SQL statements are completely identical in terms of topology and node type, as illustrated in the examples above.

[0075] A subset of slow queries can refer to a collection of all instances of slow queries with the same query structure. A query pattern can refer to an abstract concept, representing a class of queries with the same logical structure but potentially different specific parameters. Each subset of slow queries corresponds to a unique query pattern.

[0076] In one embodiment, for each query statement structure obtained in the above steps, a unique hash value (also known as a fingerprint) is calculated. All slow query statements are grouped according to the hash value corresponding to their query syntax structure. Slow query statements with the same hash value are grouped into the same group. This group is a subset of slow query statements, that is, a query pattern.

[0077] For example, the purpose of converting the query statement structure into a hash value is to better group the queries. This is because the query statement structure is an SQL statement structure, and grouping them by a specific statement structure is inefficient. A hash value, on the other hand, is a digital representation of the query statement structure. It can be understood as a specific numerical value. By using a numerical value as the unit, we can quickly find slow query statements with the same value, that is, the same query statement structure, and thus obtain a subset of slow query statements composed of these slow query statements.

[0078] For example, suppose the slow query statement set includes: Statement 1, `SELECT user_id, SUM(amount) FROM orders GROUP BY user_id;` (executed 100 times); Statement 2, `SELECT user_id, AVG(amount) FROM orders GROUP BY user_id;` (executed 5 times); Statement 3, `SELECT user_id, SUM(amount) FROM orders GROUP BY user_id;` (executed once). After the above steps, Statement 1 and Statement 3 have the same query statement structure (both summing by user_id), while Statement 2 has a different structure (it calculates the average). After calculating the hash value corresponding to each of the above statements, Statement 1 and 3 have the same hash value (let's say Hash_A), while Statement 2 has a different hash value (let's say Hash_B). This yields two slow query statement subsets (i.e., query patterns): Slow query statement subset / pattern A: includes Statement 1 and 3, corresponding to the "total amount grouped by user" pattern. Slow query statement subset / pattern B: contains statement 2, corresponding to the "calculate average amount by user group" pattern.

[0079] Step 204: Based on the slow query statement subset corresponding to each query pattern, determine the composite popularity score for each query pattern, and filter out the target query pattern based on the composite popularity score.

[0080] The composite popularity score is a comprehensive numerical value used to quantify the optimization value and urgency of a query pattern. It is calculated by weighted aggregation of multiple performance cost metrics within that pattern.

[0081] The target query pattern can refer to the set of query patterns with the highest optimization value that are selected after being sorted according to the composite popularity score and proceeded to the next step of generating materialized views.

[0082] In one embodiment, a composite popularity score can be determined for each query pattern in the following manner: Specifically, the following processing is performed for each query pattern: query records of all slow query statements corresponding to the query pattern are obtained from the database query log; query cost data for the query pattern is determined based on the query records; wherein, the query cost data includes at least: query frequency, average execution time, and average data scan volume; the data items in the query cost data are weighted to obtain the composite popularity score corresponding to the query pattern. Query patterns whose composite popularity scores meet a preset composite popularity score threshold are determined as target query patterns.

[0083] For example, the query cost data mentioned above can refer to quantitative indicators reflecting the execution cost of a query pattern, mainly including: query frequency (number of occurrences), average execution time, and average data scan volume (such as the number of rows scanned). The query frequency mentioned above can be the total number of times the same query pattern (i.e., queries with the same normalized AST fingerprint) is actually executed in the database within a specified statistical time window.

[0084] Average execution time is the arithmetic mean of the actual execution times of all execution instances of the same query pattern within a specified statistical time window. Execution time typically refers to the wall clock time elapsed from the database server starting to execute the query to returning complete results to the client, usually measured in milliseconds or seconds. It directly reflects the response latency of the query pattern to the end user or application, and is the most intuitive performance metric. Calculating the average can smooth out individual anomalies caused by data fluctuations or system load, providing a more stable reflection of typical performance.

[0085] Average data scan volume is the average amount of underlying data read (scanned) by all execution instances of the same query pattern within a specified statistical time window to complete a query. It is a core metric for measuring query resource consumption and I / O cost. A larger scanned data volume generally means more disk I / O bandwidth, memory, and CPU cycles consumed.

[0086] In addition, the composite popularity score can be calculated based on the query cost data using the following formula (i): Formula (1); Among them, w1, w2, and w3 are pre-set weight coefficients, which correspond to the various indicator data in the query cost data.

[0087] For example, suppose the cost data for query pattern A and query pattern B are as follows: Query pattern A: Frequency = 105 times, average time = 15 seconds, average number of rows scanned = 8 million rows. Query pattern B: Frequency = 5 times, average time = 3 seconds, average number of rows scanned = 500,000 rows. Weights are set: w1 = 0.3, w2 = 0.5, w3 = 0.2. Scores are calculated (for simplicity, the number of rows scanned is in millions): Query pattern A scores 10.5; Query pattern B scores 2.08. Assuming the preset composite popularity score threshold is 8, then query pattern A meets the criteria and is determined as the target query pattern.

[0088] Step 205: Based on the target query pattern and its associated data table metadata, generate candidate materialized view creation statements.

[0089] In one embodiment, the metadata information of the data table associated with the slow query statement corresponding to the target query pattern is obtained; prompt words are constructed based on the metadata information and preset database constraint rules; the prompt words are input into a pre-trained large language model so that the large language model outputs candidate materialized view creation statements; the candidate materialized view creation statements output by the large language model are verified, and the candidate materialized view creation statements that pass the verification are determined as the final candidate materialized view creation statements.

[0090] For example, the system automatically extracts metadata information from all tables involved in the target query pattern and constructs prompts such as: The following information is structured into an "instruction" for the large language model: Task: "Please design a materialized view for the slow query statement corresponding to the following target query pattern to speed up the query." Query pattern: Provides the normalized query statement structure text corresponding to the slow query SQL statement of the target query pattern. Related table structure: Provides metadata information of the data tables. Constraints and requirements: Specifies that the generated materialized view must conform to database specifications. The large language model ultimately outputs one or more candidate materialized view creation statements: `CREATE MATERIALIZED VIEW ... AS SELECT ...` statement. Then, the database statements generated by the large language model are checked for syntax and basic rules to ensure correct formatting. The candidate materialized view creation statements that pass the check are determined as the final candidate materialized view creation statements.

[0091] Step 206: In a resource-isolated sandbox environment, create candidate materialized views based on the candidate materialized view creation statement, and perform quantitative evaluation on the candidate materialized views.

[0092] Step 207: Based on the quantitative evaluation results, decide whether to approve the candidate materialized view for online deployment.

[0093] Step 208: Perform a health assessment on the online materialized views, and perform corresponding optimization operations on the online materialized views based on the health assessment results.

[0094] For steps 206-208 above, please refer to the above. Figure 1 Detailed description of the relevant embodiments.

[0095] pass Figure 2 The description of the illustrated embodiment demonstrates a high degree of automation and intelligence in database query performance optimization by constructing a closed-loop system from automatic slow query identification to intelligent materialized view generation. It can automatically and accurately extract high-value slow query patterns from massive query logs and intelligently generate compliant and secure candidate materialized view creation statements based on data table structures and large language models. This transforms the traditional high-cost, high-risk, and high-latency optimization process, which relies on manual analysis, design, and verification by senior experts, into an efficient, reliable, and scalable automated process. This not only significantly reduces the human and technical barriers but also fundamentally eliminates the risk of "negative optimization" through scientific quantitative evaluation and admission mechanisms.

[0096] Figure 3 A flowchart illustrating an embodiment of another method for generating a materialized view provided in this application is shown. Figure 1 Based on the illustrated process, this section mainly describes how to quantitatively evaluate candidate materialized views and optimize materialized views that have passed the evaluation and are already online, including the following steps: Step 301: Filter out the target query pattern from the database query log.

[0097] Step 302: Based on the target query pattern and its associated data table metadata, generate candidate materialized view creation statements.

[0098] Step 303: In a resource-isolated sandbox environment, create candidate materialized views based on the candidate materialized view creation statement.

[0099] For steps 301-303 above, please refer to the above. Figure 1 Detailed description of the relevant embodiments.

[0100] Step 304: Quantitatively evaluate the candidate materialized views: Execute the first query operation without using the candidate materialized views to obtain the first execution performance data; execute the second query operation using the candidate materialized views to obtain the second execution performance data.

[0101] The first query operation can refer to the native query executed on the example slow query SQL statement in the target query pattern in a resource-isolated sandbox environment with materialized view query rewriting disabled.

[0102] The second query operation can refer to a query executed on the same example slow query SQL statement, with a candidate materialized view already created and query rewriting enabled. The database optimizer should automatically route the query to the materialized view for acceleration. That is, the database's query optimizer can automatically rewrite the query to scan the materialized view.

[0103] Execution performance data is a set of metrics that quantifies the system resources and time consumed by a single query operation. It typically includes at least execution time and data scan volume, and may also include CPU utilization and memory consumption.

[0104] In one embodiment, one or more representative original SQL statements are selected from the slow query statement subset corresponding to the target query pattern as test cases; ensure that the sandbox database has synchronized the production data subset or full data of the relevant base tables. In the database session, parameters are set to disable materialized view rewriting, the test SQL is executed, and performance data such as T_base (execution time without materialized views) and IO_base (number of scanned rows or data volume without materialized views) are collected using database performance monitoring tools or built-in functions. The candidate materialized view creation statement generated in the above steps, the `CREATEMATERIALIZED VIEW` statement, is executed in the sandbox, successfully creating a candidate materialized view. The materialized view rewriting function is enabled, and the same test SQL is executed. At this time, the optimizer should automatically identify and utilize the newly created materialized view. Performance data such as the execution time T_mv after using the materialized view and the scanned data volume IO_mv after using the materialized view are collected.

[0105] For example, suppose the test SQL statement (i.e., the slow query SQL statement corresponding to the target query pattern) is: SELECT product_category, DATE(order_time), SUM(amount) FROM orders WHERE order_status='paid' GROUP BY product_category, DATE(order_time); Executing the first query operation on the above SQL statement (without using a materialized view) yields the following first performance data: execution time T_base = 15.2 seconds; number of rows scanned IO_base = 8,500,000 rows. Then, enabling a materialized view and executing the second query operation on the above SQL statement (using a materialized view) yields the following second performance data: execution time T_mv = 1.8 seconds; number of rows scanned IO_mv = 120,000 rows.

[0106] Step 305: Based on the first execution performance data and the second execution performance data, verify the consistency of the execution results of the first query operation and the second query operation.

[0107] Consistency of execution results means that the data result sets returned by two query operations (without using a materialized view and with using a materialized view) are logically completely equal. This is the fundamental premise that materialized views can replace the original query to speed up the query, and it must be strictly guaranteed.

[0108] In one embodiment, when executing the first and second query operations, not only is performance data recorded, but the complete result sets returned by the queries are also saved separately (e.g., stored in a temporary table or file). A highly reliable method is used to compare the two result sets; if they match, the verification passes; if they do not match, the evaluation process for the candidate view is immediately terminated and marked as "results inconsistent, failure".

[0109] For example, capture the result sets obtained from the first and second query operations respectively. Calculate the hash values ​​of the two result sets separately. The hash value of the result set from the first query operation is a1b2c3d4e5, and the hash value of the result set from the second query operation is a1b2c3d4e5. The two hash values ​​obtained are exactly the same, therefore the execution results are consistent.

[0110] Step 306: If the execution results of the first query operation and the second query operation are consistent, the candidate materialized view is quantitatively evaluated based on the first execution performance data and the second execution performance data.

[0111] Quantitative evaluation refers to the calculation of a series of quantifiable benefit indicators through formulas, based on ensuring the accuracy of the results, in order to objectively measure the performance improvement and resource savings of candidate materialized views.

[0112] In one embodiment, first execution performance data and second execution performance data are obtained; query performance metrics and resource saving rates are determined based on the two sets of execution performance data, respectively. If the query performance metrics and resource saving rates are greater than their respective preset threshold values, the evaluation result of the candidate materialized view is determined to be qualified.

[0113] For example, the above query performance metrics can be determined based on the execution time corresponding to two query operations, as shown in Formula (II) below. The resource saving rate can be determined based on the amount of data scanned corresponding to two query operations, as shown in Formula (III) below: Formula (II); Formula (3); The above T_base represents the execution time without using a materialized view, and IO_base represents the number of scanned rows or the amount of data without using a materialized view; T_mv represents the execution time after using a materialized view, and IO_mv represents the amount of scanned data after using a materialized view.

[0114] For example, for a slow query that calculates sales by product category and date, the execution time T_base of the first query operation without using a materialized view is 15.2 seconds, and the data scan volume IO_base is 8.5 million rows. After creating and enabling the candidate materialized view, the execution time T_mv of the same query (the second query operation) drops to 1.8 seconds, and the data scan volume IO_mv is only 120,000 rows. Based on the above formula (II), the query performance index is calculated to be 8.44, and the resource saving rate is calculated to be 98.6% according to the above formula (III). The system compares these two core indicators with preset thresholds (such as the query performance index needing to be greater than 2.0 and the resource saving rate needing to be greater than 50%). Since both significantly exceed the thresholds, the quantitative evaluation result of the candidate materialized view is deemed qualified, indicating that it can bring significant performance improvement and resource saving.

[0115] Step 307: Based on the quantitative evaluation results, decide whether to approve the candidate materialized view for online deployment.

[0116] In one embodiment, the quantitative evaluation result may include evaluation values ​​of multiple different dimensions; the evaluation value of each dimension is compared with a preset threshold corresponding to the dimension; if the comparison shows that the evaluation value of each dimension meets the corresponding preset threshold, the decision is made to approve the candidate materialized view to go online; if the comparison shows that the evaluation value of any dimension does not meet the corresponding preset threshold, the decision is made not to approve the candidate materialized view to go online.

[0117] For example, after completing the quantitative evaluation, the system makes a decision on whether to deploy candidate materialized views based on preset multi-dimensional admission rules. Specifically, for the candidate view `mv_daily_sales`, the evaluation yields a query performance index of 8.44, a resource saving rate of 98.6%, and an estimated storage cost of 500MB. The system compares these evaluation values ​​with preset dimension thresholds (such as the query performance index threshold θ_speed=2.0 and the resource saving rate threshold θ_io=0.5). Since the actual evaluation values ​​for all dimensions meet the criteria (8.44>2.0 and 0.986>0.5) and the estimated benefit is greater than the resource cost, the system decides to approve the deployment of the candidate materialized view. Conversely, if its query performance index is only 1.5 (below the threshold of 2.0), even if other dimensions meet the criteria, the system will decide to reject the deployment and record the reason for "performance benefit not meeting the standard," thus constructing a safe admission mechanism based on quantitative data and fundamentally eliminating the risk of "negative optimization."

[0118] Step 308: Perform a health assessment on the online materialized views, and perform corresponding optimization operations on the online materialized views based on the health assessment results.

[0119] In one embodiment, the health of a materialized view that has been launched can be assessed in the following way: periodically retrieve the health assessment index data of the materialized view that has been launched from the database query log; determine the health score of the materialized view that has been launched based on the health assessment index data; and determine the health level based on the health score of the materialized view that has been launched and the preset level rules.

[0120] The health assessment metrics include at least: hit data, revenue data, and cost data. Hit data is a quantitative score for each deployed materialized view, determined based on time decay, measuring the frequency and freshness of queries hitting the materialized view over a period, with recent hits contributing more. Revenue data is the total time saved by deployed materialized views for all queries. Cost data is the storage size occupied by deployed materialized views on disk. For example, the above hit data can be calculated using the following time decay aggregation function, as shown in Formula (IV); Formula (IV); S_hit represents the hit data; hit_count(t) represents the number of times the materialized view is hit at a certain time point t (e.g., a certain day). Δt represents the time difference (e.g., number of days) between the current calculation time point and the hit time point t. λ represents the decay coefficient, which is related to the half-life (T_half), λ=ln(2) / T_half. For example, if the half-life T_half is set to 7 days, then the hit contribution a week ago is about half of the current hit contribution. The half-life can be preset by the user. Σ represents the sum of the decayed hit counts for all days within the statistical period (e.g., the past 30 days).

[0121] The health score of the above-mentioned materialized views can be calculated using the following formula (V): Formula (5); The w_hit, w_gain, and w_cost mentioned above are preset weighting coefficients for the three dimensions of hit data, revenue data, and cost data, respectively, used to adjust the importance of different dimensions in the total score. S_gain represents the revenue data, used to measure the cumulative performance gain brought by the materialized view; S_cost represents the cost data, used to measure the system resources consumed by the materialized view. HealthScore represents the health score of the deployed materialized view. Normalize is the normalization function.

[0122] For example, the above-mentioned revenue data S_gain can be calculated using the following formula (vi): Formula (VI); Here, (T_base - T_mv) represents the time saved in the query after rewriting with a materialized view; Scale_CPU represents the cores-per-second conversion factor. This value is equal to the number of concurrent CPU cores during query execution. For example, a 1-second reduction in time for a 16-core concurrent query is equivalent to freeing up 16 cores-per-second of cluster computing power.

[0123] The cost data S_cost mentioned above can be calculated using the following formula (VII): Formula (VII); Where C_storage represents the physical storage size occupied by the materialized view on disk. P_storage represents the weight coefficient per unit of storage. P_CPU represents the cumulative CPU time (cores per second) consumed during the Kth refresh task execution; P_CPU represents the cost per unit of computing power consumed. The aforementioned P_storage is a quantitative strategy indicator for the scarcity of storage resources within the company. If the company has ample hard drive storage space, it can be set to 1 to retain materialized views; if the company has limited hard drive storage space, it can be set to 100, and the system will automatically clean up zombie views occupying storage space. Additionally, P_CPU can be preset; essentially, it acts as a switch to control the computing power cost budget. Increasing it will automatically identify and clean up views that refresh frequently and waste computing power; decreasing it indicates that the current system's computing resources are sufficient, and materialized views will not be easily cleaned up.

[0124] In this embodiment, a health score greater than 80 can be defined as the first health level; a health score less than 30 can be defined as the third health level; and a health score between 30 and 80 can be defined as the second health level. This is merely an example, and this embodiment does not impose any limitations.

[0125] In addition, the following optimization operations can be performed on the materialized views that have been launched based on the health assessment results: if the health assessment results indicate that the materialized view has been launched at the first health level, maintain the normal operation of the materialized view; if the health assessment results indicate that the materialized view has been launched at the second health level, trigger an alarm; if the health assessment results indicate that the materialized view has been launched at the third health level, shut down the materialized view and perform a retirement process on the materialized view after the observation period ends.

[0126] For example, after a materialized view is launched, the system continuously performs health assessments and automated governance. Specifically, the view `mv_daily_sales` was frequently used for generating daily reports in its early stages. The system periodically calculated its health score to be as high as 85 points (out of 100), placing it in the first health level (green zone). Therefore, no actions were triggered, and it remained in normal operation. Several months later, due to business changes, the relevant reports were taken offline, and the view had no query hits for several consecutive days. The system reassessed it, and due to its hit rate score plummeting, the health score dropped to 25 points, falling into the third health level (red zone). The system then automatically executed the governance process: first, it disabled the query rewrite function of the view, temporarily disabling it; after entering an observation period (e.g., 7 days) to confirm no business impact, it was finally automatically deleted, reclaiming the storage resources it occupied. If its score is in the second health level (yellow zone, e.g., 65 points), the system will trigger an alarm to notify the administrator to pay attention, and use this case as a feedback signal to optimize subsequent AI recommendation strategies, thereby achieving dynamic resource reclamation and closed-loop system optimization.

[0127] pass Figure 3 The illustrated embodiment describes a system that, through scientific dual-run comparative experiments in a resource-isolated sandbox, precisely quantifies the performance gains and resource costs of candidate materialized views. It then makes automated deployment decisions based on multi-dimensional thresholds, thus eliminating the risk of negative optimization due to blind deployment. Simultaneously, the system dynamically evaluates and hierarchically manages online materialized views using a continuous health scoring model. It automatically identifies inefficient "zombie views" and executes a gradient operation from alerts to automatic removal, achieving precise reclamation and dynamic optimization of computing and storage resources. Ultimately, this solution transforms the materialized view optimization process from a traditional, static, one-off model relying on manual experience to a fully automated, data-driven, closed-loop, and sustainable intelligent operation and maintenance model. This significantly improves query performance and system stability while drastically reducing operation and maintenance costs and resource waste.

[0128] Figure 4 This application provides a structural block diagram of a materialized view generation apparatus, the apparatus comprising: The target query pattern filtering module 41 is used to filter out target query patterns from the database query log; The view creation statement generation module 42 is used to generate candidate materialized view creation statements based on the target query pattern and its associated data table metadata. The view quantification evaluation module 43 is used to create candidate materialized views based on the candidate materialized view creation statement in a resource-isolated sandbox environment, and to perform quantification evaluation on the candidate materialized views. The view deployment decision module 44 is used to decide whether the candidate materialized view is approved for deployment based on the quantitative evaluation results. The view health assessment module 45 is used to assess the health of the online materialized views and perform corresponding optimization operations on the online materialized views based on the health assessment results.

[0129] In one possible implementation, the target query pattern filtering module 41 includes: The slow query statement set determination unit is used to determine the slow query statement set from the database query log based on preset slow query filtering rules; The query statement structure acquisition unit is used to perform abstract syntax tree parsing on each slow query statement in the slow query statement set to obtain the corresponding query statement structure. The slow query statement subset determination unit is used to group slow query statements with the same query statement structure into a slow query statement subset, thereby obtaining multiple slow query statement subsets; each slow query statement subset corresponds to a query pattern. The target query pattern filtering unit is used to determine a composite popularity score for each query pattern based on the slow query statement subset corresponding to each query pattern, and to filter out target query patterns based on the composite popularity score.

[0130] In one possible implementation, the target query pattern filtering unit is specifically used for: For each of the query patterns, perform the following processing: Retrieve query records of all slow query statements corresponding to the query pattern from the database query log; The query cost data for the query mode is determined based on the query records; wherein the query cost data includes at least: query frequency, average time consumption, and average data scan volume; The composite popularity score corresponding to the query pattern is obtained by weighting each data item in the query cost data.

[0131] In one possible implementation, the view creation statement generation module 42 is specifically used for: Obtain the metadata information of the data table associated with the slow query statement corresponding to the target query pattern; Prompt words are constructed based on the metadata information and preset database constraint rules; The prompt words are input into a pre-trained large language model so that the large language model outputs candidate materialized view creation statements; The candidate materialized view creation statements output by the large language model are validated, and the candidate materialized view creation statements that pass the validation are determined as the final candidate materialized view creation statements.

[0132] In one possible implementation, the view quantification evaluation module 43 is specifically used for: Execute a first query operation that does not use the candidate materialized view to obtain first execution performance data; Execute a second query operation using the candidate materialized view to obtain second execution performance data; Based on the first execution performance data and the second execution performance data, verify the consistency of the execution results of the first query operation and the second query operation; If the execution results of the first query operation and the second query operation are found to be consistent, the candidate materialized view is quantitatively evaluated based on the first execution performance data and the second execution performance data.

[0133] In one possible implementation, the quantitative evaluation result includes evaluation values ​​across multiple dimensions; the view-on-line decision module 44 is specifically used for: The evaluation value of each dimension is compared with the preset threshold corresponding to that dimension; If the evaluation value of each dimension meets the corresponding preset threshold, the decision is made to approve the candidate materialized view to go online. If the evaluation value of any dimension fails to meet the corresponding preset threshold, the decision is made not to approve the candidate materialized view for online deployment.

[0134] In one possible implementation, the view health assessment module 45 is specifically used for: Periodically retrieve the health assessment index data of the online materialized views from the database query log; The health score of the online materialized view is determined based on the health assessment index data. The health level is determined based on the health score of the materialized view that has been launched and the preset grading rules.

[0135] In one possible implementation, the view health assessment module 45 is specifically used for: If the health assessment result indicates that the online materialized view is at the first health level, the normal operation of the online materialized view shall be maintained. An alarm is triggered if the health assessment result indicates that the online materialized view is at the second health level. If the health assessment result indicates that the online materialized view is at the third health level, the online materialized view is shut down, and a decommissioning process is performed on the online materialized view after the observation period ends.

[0136] like Figure 5 As shown in the figure, this application provides an electronic device, including a processor 111, a communication interface 112, a memory 113, and a communication bus 114, wherein the processor 111, the communication interface 112, and the memory 113 communicate with each other through the communication bus 114. Memory 113 is used to store computer programs; In one embodiment of this application, when the processor 111 executes a program stored in the memory 113, it implements the method for generating a materialized view provided in any of the foregoing method embodiments, including: Filter the target query pattern from the database query log; Based on the target query pattern and its associated data table metadata, generate candidate materialized view creation statements; In a resource-isolated sandbox environment, candidate materialized views are created based on the candidate materialized view creation statement, and the candidate materialized views are quantitatively evaluated. Based on the quantitative evaluation results, a decision is made on whether the candidate materialized view should be approved for deployment. Perform a health assessment on the materialized views that have been launched, and perform corresponding optimization operations on the materialized views based on the health assessment results.

[0137] This application also provides a computer-readable storage medium storing a computer program thereon, which, when executed by a processor, implements the steps of the materialized view generation method provided in any of the foregoing method embodiments.

[0138] The device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the modules can be selected to achieve the purpose of this embodiment according to actual needs.

[0139] Through the above description of the embodiments, those skilled in the art can clearly understand that each embodiment can be implemented using software plus a general-purpose hardware platform, or of course, using hardware. Based on this understanding, the above technical solutions, in essence or the parts that contribute to the related technology, can be embodied in the form of a software product. This computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, magnetic disk, optical disk, etc., and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute the methods described in the various embodiments or some parts of the embodiments.

[0140] It should be understood that the terminology used herein is for the purpose of describing particular exemplary embodiments only and is not intended to be limiting. Unless the context clearly indicates otherwise, the singular forms “a,” “an,” and “described” as used herein may also mean including the plural forms. The terms “comprising,” “including,” “containing,” and “having” are inclusive and therefore indicate the presence of the stated features, steps, operations, elements, and / or components, but do not exclude the presence or addition of one or more other features, steps, operations, elements, components, and / or combinations thereof. The method steps, processes, and operations described herein are not construed as requiring them to be performed in a particular order described or illustrated unless the order of performance is explicitly indicated. It should also be understood that additional or alternative steps may be used.

[0141] The above description is merely a specific embodiment of this application, enabling those skilled in the art to understand or implement this application. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of this application. Therefore, this application is not to be limited to the embodiments shown herein, but is to be accorded the widest scope consistent with the principles and novel features claimed herein.

Claims

1. A method for generating a materialized view, characterized in that, The method includes: Filter the target query pattern from the database query log; Based on the target query pattern and its associated data table metadata, generate candidate materialized view creation statements; In a resource-isolated sandbox environment, candidate materialized views are created based on the candidate materialized view creation statement, and the candidate materialized views are quantitatively evaluated. Based on the quantitative evaluation results, a decision is made on whether the candidate materialized view should be approved for deployment. Perform a health assessment on the materialized views that have been launched, and perform corresponding optimization operations on the materialized views based on the health assessment results.

2. The method according to claim 1, characterized in that, The step of filtering the target query pattern from the database query log includes: The set of slow query statements is determined from the database query log based on preset slow query filtering rules; For each slow query statement in the slow query statement set, perform abstract syntax tree parsing to obtain the corresponding query statement structure; Slow query statements with the same query statement structure are grouped into a slow query statement subset, resulting in multiple slow query statement subsets; each slow query statement subset corresponds to a query pattern. Based on the subset of slow query statements corresponding to each query pattern, a composite popularity score is determined for each query pattern, and the target query pattern is selected based on the composite popularity score.

3. The method according to claim 2, characterized in that, The determination of a composite popularity score for each query pattern includes: For each of the query patterns, perform the following processing: Retrieve query records of all slow query statements corresponding to the query pattern from the database query log; The query cost data for the query mode is determined based on the query records; wherein the query cost data includes at least: query frequency, average time consumption, and average data scan volume; The composite popularity score corresponding to the query pattern is obtained by weighting each data item in the query cost data.

4. The method according to claim 1, characterized in that, The step of generating candidate materialized view creation statements based on the target query pattern and its associated data table metadata includes: Obtain the metadata information of the data table associated with the slow query statement corresponding to the target query pattern; Prompt words are constructed based on the metadata information and preset database constraint rules; The prompt words are input into a pre-trained large language model so that the large language model outputs candidate materialized view creation statements; The candidate materialized view creation statements output by the large language model are validated, and the candidate materialized view creation statements that pass the validation are determined as the final candidate materialized view creation statements.

5. The method according to claim 1, characterized in that, The quantitative evaluation of the candidate materialized views includes: Execute a first query operation that does not use the candidate materialized view to obtain first execution performance data; Execute a second query operation using the candidate materialized view to obtain second execution performance data; Based on the first execution performance data and the second execution performance data, verify the consistency of the execution results of the first query operation and the second query operation; If the execution results of the first query operation and the second query operation are found to be consistent, the candidate materialized view is quantitatively evaluated based on the first execution performance data and the second execution performance data.

6. The method according to claim 1 or 5, characterized in that, The quantitative evaluation results include evaluation values ​​from multiple different dimensions; the decision on whether to approve the candidate materialized view for online deployment based on the quantitative evaluation results includes: The evaluation value of each dimension is compared with the preset threshold corresponding to that dimension; If the evaluation value of each dimension meets the corresponding preset threshold, the decision is made to approve the candidate materialized view to go online. If the evaluation value of any dimension fails to meet the corresponding preset threshold, the decision is made not to approve the candidate materialized view for online deployment.

7. The method according to claim 1, characterized in that, The health assessment of the already deployed materialized views includes: Periodically retrieve the health assessment index data of the online materialized views from the database query log; The health score of the online materialized view is determined based on the health assessment index data. The health level is determined based on the health score of the materialized view that has been launched and the preset grading rules.

8. The method according to claim 1 or 7, characterized in that, The optimization operations performed on the online materialized view based on the health assessment results include: If the health assessment result indicates that the online materialized view is at the first health level, the normal operation of the online materialized view shall be maintained. An alarm is triggered if the health assessment result indicates that the online materialized view is at the second health level. If the health assessment result indicates that the online materialized view is at the third health level, the online materialized view is shut down, and a decommissioning process is performed on the online materialized view after the observation period ends.

9. An apparatus for generating a materialized view, characterized in that, The device includes: The target query pattern filtering module is used to filter out target query patterns from the database query logs; The view creation statement generation module is used to generate candidate materialized view creation statements based on the target query pattern and its associated data table metadata. The view quantification evaluation module is used to create candidate materialized views based on the candidate materialized view creation statement in a resource-isolated sandbox environment, and to perform quantification evaluation on the candidate materialized views. The view deployment decision module is used to decide whether the candidate materialized view is approved for deployment based on the quantitative evaluation results. The view health assessment module is used to assess the health of the online materialized views and perform corresponding optimization operations on the online materialized views based on the health assessment results.

10. An electronic device, characterized in that, include: A processor and a memory, the processor being configured to execute a materialized view generation program stored in the memory to implement the materialized view generation method according to any one of claims 1-8.

11. A storage medium, characterized in that, The storage medium stores one or more programs, which can be executed by one or more processors to implement the method for generating the materialized view according to any one of claims 1-8.

Images