Database index and materialized view self-adaptive management system and method based on continuous SQL (Structured Query Language) analysis

Abstract

The invention discloses a database index and materialized view self-adaptive management system and method based on continuous SQL (Structured Query Language) analysis, and belongs to the technical field of database management systems. According to the method, a full-automatic life cycle management framework of'monitoring-analysis-decision-execution-feedback 'is constructed, a query log is analyzed in real time and key features are extracted by utilizing an SQL auditing analysis engine, an operation execution engine automatically executes corresponding DDL operation, and a model is iteratively optimized through a closed-loop feedback module. According to the method, the high-frequency query delay can be reduced by 60%-85%, the write operation throughput is improved by 30% or above, the storage space is saved by 25%-40%, meanwhile, DBA manual intervention is reduced by 90%, and dynamic self-adaptive management of indexes and materialized views is effectively achieved.

Description

TECHNICAL FIELD

[0001] The present application relates to the field of database management systems, in particular to a database index and materialized view adaptive management system and method based on continuous SQL analysis. BACKGROUND

[0002] In the field of database management, indexes and materialized views are key technologies to improve query performance, but the management methods in existing technologies have significant defects: Dependence on human experience, prone to management errors: the creation of indexes and materialized views depends on the experience of database administrators, often resulting in miscreation or omission. For example, in the order table of an e-commerce platform, the DBA created an index for the "logistics order number fuzzy query" with less than 10 queries per month, resulting in storage space waste and write performance degradation; while for the "user ID + order date" combined query with more than 100,000 queries per day, no index was established, causing query delay of more than 5 seconds.

[0003] Lack of automatic cleaning of abandoned resources: with the development of business, some indexes or materialized views will become invalid due to changes in query patterns, but existing systems cannot automatically identify and clean them, resulting in waste of storage space and more than 50% increase in write operation delay. For example, in the historical transaction table of a financial system, the materialized view for "transactions before 2020" has not been queried for half a year due to business focus shift, but still occupies 100GB of storage space, and each data write needs to consume an additional 20% of IO resources for synchronization update of the materialized view.

[0004] Static management cannot respond to dynamic changes: traditional recommendation tools rely on statistical information at fixed time points and cannot capture dynamic changes in query patterns in real time. SUMMARY

[0005] The purpose of the present application is to provide a database index and materialized view adaptive management system and method based on continuous SQL analysis, aiming to realize full-automatic life cycle management of indexes and materialized views, improve database performance and optimize resource utilization.

[0006] The present application is implemented as follows: a database index and materialized view adaptive management system based on continuous SQL analysis, comprising: a SQL audit analysis engine for real-time parsing of database query logs, extracting high-frequency query patterns, high-cost query features and data update frequency; An intelligent decision engine integrates a utility score model to calculate the utility value of an index / materialized view by a formula U=(Query_Time_Saved×Freq) / (Storage_Cost+Update_Overhead), and generates a creation recommendation or a retirement recommendation of the index / materialized view based on the utility value and a preset threshold; An operation execution engine automatically generates and executes corresponding DDL operation instructions according to the recommendation output by the intelligent decision engine, and supports a full-automatic execution mode and an approval mode requiring administrator review; A closed-loop feedback module monitors the query delay change, storage space release amount and write throughput change after the DDL operation is executed, and feeds back the monitoring result to the intelligent decision engine for iterative optimization of the utility score model; A deep integration interface directly interacts with a metadata system of an underlying distributed database to obtain state information and a query execution plan of an index / materialized view in real time.

[0007] Preferably, the high-frequency query pattern extracted by the SQL audit analysis engine includes JOIN clause features and AGGREGATION clause features; the high-cost query feature is a query feature with an execution time exceeding a preset threshold; and the data update frequency includes UPDATE operation frequency and DELETE operation frequency of a base table.

[0008] Preferably, in the utility score model: Query_Time_Saved is obtained based on analysis of a query execution plan, and is the maximum query time that can be optimized by an index / materialized view; Storage_Cost is dynamically calculated according to actual space occupation of a distributed storage node; Update_Overhead is obtained by monitoring DML operation frequency of a base table.

[0009] Preferably, the operation execution engine also supports a sandbox test mode, in which the operation execution engine executes the DDL operation instruction in a shadow library, verifies the performance influence, and then applies the DDL operation instruction to a production library.

[0010] Preferably, the deep integration interface accesses an information_schema library of an underlying distributed database to obtain metadata in an INDEXES table and a MATERIALIZED_VIEWS table in real time; and the deep integration interface can analyze a PLANFRAGMENT generated by a front end of the underlying distributed database to locate a performance bottleneck of a scanning operator.

[0011] Preferably, the deep integration interface is also used to assign an independent WorkloadGroup to the background automation task of the system to realize resource isolation and avoid affecting production queries.

[0012] The application also discloses a database index and materialized view life cycle management method based on continuous SQL analysis, comprising the following steps: (1) continuously collecting the execution plan, actual time consumption and data update frequency of a base table of a SQL query; (2) dynamically evaluating the value of an index / materialized view based on a multi-dimensional utility scoring model through a formula U=(Query_Time_Saved*Freq) / (Storage_Cost+Update_Overhead); (3) when the occurrence frequency of a query is greater than a threshold value_F, the query cost is greater than a threshold value_C, the update frequency of an associated field is less than a threshold value_U and there is no matching index / materialized view, generating a creation recommendation of an index / materialized view; (4) when the utility value U of an index / materialized view is less than a threshold value_LowUtil or the storage occupation of the index / materialized view is greater than a threshold value_Storage and the usage times in the past 7 days are 0, generating a discard recommendation of the index / materialized view; (5) verifying the optimization effect of a DDL operation corresponding to the creation recommendation or the discard recommendation in a sandbox environment and then executing the DDL operation; (6) monitoring the performance index changes after the execution of the DDL operation and iteratively optimizing the multi-dimensional utility scoring model based on the performance index changes.

[0013] Preferably, in the step of continuously collecting the execution plan, actual time consumption and data update frequency of a SQL query, the data update frequency of the base table is obtained through an __internal_schema.tablet_meta metadata table of a bottom-layer distributed database.

[0014] Preferably, in the multi-dimensional utility scoring model, Query_Time_Saved is obtained based on the analysis of a query execution plan, Storage_Cost is dynamically calculated according to the actual space occupation of a distributed storage node and Update_Overhead is obtained by quantifying the DML operation frequency of a base table.

[0015] Preferably, the step of verifying the optimization effect of a DDL operation corresponding to the creation recommendation or the discard recommendation in a sandbox environment specifically comprises: executing the DDL operation in a shadow database, comparing the query delay, storage space occupation and write throughput before and after the execution to verify the optimization effect.

[0016] Compared with the prior art, the application has the beneficial effects of: The application significantly improves performance: high-frequency query delay is reduced by 60%-85% by precisely matching high-frequency high-cost queries; invalid index update overhead is eliminated, and write operation throughput is improved by more than 30%. For example, after an e-commerce platform applies the application, the "user order query" delay is reduced from 3 seconds to 0.45 seconds, and the order table write throughput is improved from 1000 per second to 1350 per second.

[0017] The application efficiently utilizes resources: 25%-40% of storage space is saved by automatically cleaning up abandoned indexes / MVs. For example, a financial system cleaned up 12 abandoned materialized views by using the application, releasing 150 GB of storage space, accounting for 32% of the original total storage.

[0018] The application reduces operation and maintenance costs: the full life cycle of indexes / MVs is automatically managed, and the amount of manual intervention by DBAs is reduced by 90%. BRIEF DESCRIPTION OF DRAWINGS

[0019] Figure 1 is a schematic diagram of the system architecture of the application; Figure 2 is a schematic diagram of the closed-loop management process of the application; DETAILED DESCRIPTION

[0020] In the application, unless otherwise explicitly specified and limited, the terms "mounting", "connection", "connection", "fixing" and the like should be broadly understood, for example, can be fixedly connected, or can be detachably connected, or can be integrated; can be mechanically connected, or can be electrically connected; can be directly connected, or can be indirectly connected through an intermediate medium; can be the internal communication of two elements or the interaction relationship between two elements. For those skilled in the art, the specific meanings of the above terms in the application can be understood according to the specific circumstances.

[0021] The application will be further described below in conjunction with the drawings and specific embodiments: Embodiment 1: Full-automatic creation of high-frequency query index This embodiment is applied to the order database of an e-commerce platform, and the goal is to automatically create indexes for high-frequency high-cost queries.

[0022] Data Collection: The SQL audit analysis engine captures query logs from the orders table in real time and finds that queries with the structure "WHERE user_id=ANDcreate_time>" occur an average of 100,000 times per day (frequency > threshold_F = 50,000 times), with an average execution time of 8 seconds (cost > threshold_C = 3 seconds). Monitoring the __internal_schema.tablet_meta table shows that the user_id and create_time fields are updated an average of 500 times per day (update frequency < threshold_U = 1,000 times), and there is currently no matching index.

[0023] Decision-making process: The intelligent decision engine calculates the index utility value U corresponding to the query as U = (8 seconds × 100,000 times) / (5GB storage cost + 500 update costs) = 800,000 seconds / (5GB + 500 times). The result is far higher than the threshold, and a creation recommendation is generated.

[0024] Execution: The operation execution engine automatically generates the DDL statement "ALTERTABLE orders ADD INDEX idx_user_create(user_id,create_time)USING INVERTED", which is executed directly in the production database because the system is configured to be in fully automatic mode.

[0025] Results feedback: The closed-loop feedback module monitored that the query latency decreased from 8 seconds to 1.2 seconds (a reduction of 85%), storage usage increased by 5GB, and write throughput did not decrease significantly. The feedback results verified that the decision was effective, and the model parameters remained unchanged.

[0026] Example 2: Elimination of the approval model for waste disposal views This embodiment is applied to the transaction database of a financial system, with the goal of cleaning up materialized views that have not been used for a long time.

[0027] Data collection: The SQL audit analysis engine monitored that the materialized view mv_historical_trans (based on the transaction table) had been used 0 times in the past 7 days and its storage usage was 100GB (>threshold_Storage=50GB).

[0028] Decision-making process: The intelligent decision engine calculates its utility value U=0 (because there is no query benefit), which meets the elimination conditions, and generates elimination recommendations.

[0029] Execution: The operation execution engine generates the DDL statement "DROPMATERIALIZEDVIEWmv_historical_trans". Since the system is configured in approval mode, a work order is generated and submitted to the DBA for review. After the DBA confirms, the operation is executed.

[0030] Effect feedback: The closed-loop feedback module monitors the release of 100 GB of storage space (28% saved), and the transaction table write delay is reduced from 200 ms to 120 ms (40% improvement). The feedback result verifies that the decision is effective, and the model iteratively optimizes the storage threshold parameter.

[0031] Example 3: Materialized view creation verification in sandbox test mode This embodiment is applied to the commodity database of a fresh food platform, and the goal is to verify the effect of materialized view creation in a sandbox environment before going online.

[0032] Data collection: The SQL audit analysis engine finds that the "GROUP BY category HAVING sales > 1000" aggregation query occurs 50,000 times per day (frequency > threshold_F = 30,000 times), with an average execution time of 10 seconds (cost > threshold_C = 5 seconds), and the category field of the commodity table is updated 200 times per day (< threshold_U = 500 times).

[0033] Decision process: The intelligent decision engine generates a recommendation to create a materialized view, with an utility value U = (10 seconds x 50,000 times) / (10 GB storage cost + 200 times update overhead), which meets the creation conditions.

[0034] Execution operation: The operation execution engine executes "CREATE MATERIALIZED VIEW mv_category_sales AS SELECT category, SUM(sales) FROM products GROUP BY category HAVING SUM(sales) > 1000" in the shadow database, and the verification shows that the query delay is reduced to 1.5 seconds, the storage occupancy is 10 GB, and the write performance is not significantly affected.

[0035] Effect feedback: After the sandbox test passes, the system executes the operation in the production database, and the closed-loop feedback module monitors that the performance in the production environment is consistent with that in the sandbox, and the model confirms that it is effective.

[0036] The above is only a preferred embodiment of the present application and is not intended to limit the present application. For those skilled in the art, the present application can have various modifications and changes. Any modification, equivalent replacement, improvement, etc. made within the spirit and principles of the present application shall be included in the protection scope of the present application.

Claims

1. An adaptive management system for database indexes and materialized views based on continuous SQL analysis, characterized in that, include: The SQL audit analysis engine is used to parse database query logs in real time and extract high-frequency query patterns, high-cost query characteristics, and data update frequency. The intelligent decision engine integrates a utility scoring model and calculates the utility value of the index / materialized view using the formula U=(Query_Time_Saved×Freq) / (Storage_Cost+Update_Overhead). Based on the utility value and a preset threshold, the intelligent decision engine generates creation or elimination recommendations for the index / materialized view. The operation execution engine automatically generates and executes corresponding DDL operation instructions based on the recommendations output by the intelligent decision engine, supporting both fully automatic execution mode and approval mode requiring administrator review. The closed-loop feedback module monitors the changes in query latency, storage space release, and write throughput after the DDL operation is executed, and feeds the monitoring results back to the intelligent decision engine for iterative optimization of the utility scoring model. It has a deeply integrated interface that interacts directly with the metadata system of the underlying distributed database to obtain the status information of indexes / materialized views and query execution plans in real time.

2. The adaptive management system for database indexes and materialized views based on continuous SQL analysis according to claim 1, characterized in that, The high-frequency query patterns extracted by the SQL audit analysis engine include JOIN clause features and AGGREGATION clause features; The high-cost query characteristic is the query characteristic whose execution time exceeds a preset threshold; the data update frequency includes the frequency of UPDATE operations and DELETE operations on the base table.

3. The adaptive management system for database indexes and materialized views based on continuous SQL analysis according to claim 1, characterized in that, In the utility rating model: Query_Time_Saved is obtained based on the parsing of the query execution plan and represents the maximum query time that can be optimized for an index / materialized view. Storage_Cost is dynamically calculated based on the actual space usage of the distributed storage nodes; Update_Overhead is obtained by quantifying the frequency of DML operations on the base table.

4. The adaptive management system for database indexes and materialized views based on continuous SQL analysis according to claim 1, characterized in that, The operation execution engine also supports a sandbox testing mode. In the sandbox testing mode, the operation execution engine executes the DDL operation instructions in the shadow library, verifies the performance impact, and then applies the DDL operation instructions to the production library.

5. The adaptive management system for database indexes and materialized views based on continuous SQL analysis according to claim 1, characterized in that, The deep integration interface accesses the information_schema library of the underlying distributed database to obtain metadata from the INDEXES and MATERIALIZED_VIEWS tables in real time; and the deep integration interface can parse the PLANFRAGMENT generated by the front end of the underlying distributed database to locate the performance bottleneck of the scan operator.

6. The adaptive management system for database indexes and materialized views based on continuous SQL analysis according to claim 1, characterized in that, The deep integration interface is also used to allocate independent WorkloadGroups to the background automation tasks of the system in order to achieve resource isolation and avoid affecting production queries.

7. The database index and materialized view lifecycle management method based on continuous SQL analysis according to claim 1, characterized in that, Includes the following steps: (1) Continuously collect the execution plan, actual time consumption, and data update frequency of the base table for SQL queries; (2) Based on the multi-dimensional utility scoring model, the value of the index / materialized view is dynamically evaluated using the formula U=(Query_Time_Saved×Freq) / (Storage_Cost+Update_Overhead); (3) When the frequency of the query is greater than the threshold_F, the query cost is greater than the threshold_C, the update frequency of the associated field is less than the threshold_U, and there is no matching index / materialized view, the creation of the index / materialized view is recommended; (4) When the utility value U of the index / materialized view is less than the threshold_LowUtil, or the storage usage of the index / materialized view is greater than the threshold_Storage and the number of uses in the past 7 days is 0, generate a suggestion to eliminate the index / materialized view; (5) After verifying the optimization effect of the DDL operation corresponding to the creation of recommendation or elimination suggestion in the sandbox environment, execute the DDL operation; (6) Monitor the changes in performance indicators after the execution of the DDL operation, and iteratively optimize the multi-dimensional utility scoring model based on the changes in performance indicators.

8. The database index and materialized view lifecycle management method based on continuous SQL analysis according to claim 7, characterized in that, In the step of continuously collecting the execution plan, actual time consumption, and data update frequency of the base table for SQL queries, the data update frequency of the base table is obtained through the __internal_schema.tablet_meta metadata table of the underlying distributed database.

9. The database index and materialized view lifecycle management method based on continuous SQL analysis according to claim 7, characterized in that, In the multi-dimensional utility scoring model, Query_Time_Saved is obtained based on the parsing of the query execution plan, Storage_Cost is dynamically calculated based on the actual space occupancy of the distributed storage nodes, and Update_Overhead is obtained by quantifying the frequency of DML operations on the monitoring base table.

10. The database index and materialized view lifecycle management method based on continuous SQL analysis according to claim 7, characterized in that, The step of verifying the optimization effect of the DDL operation corresponding to the creation of recommendation or elimination suggestions in the sandbox environment specifically includes: executing the DDL operation in the shadow library, comparing the query latency, storage space usage and write throughput before and after execution to verify the optimization effect.

Images