Principles and Applications of Materialized Views in Database Query Optimization

Principles and Applications of Materialized Views in Database Query Optimization

Problem Description
A Materialized View is a precomputation technique in databases that persists query results as a physical table and keeps the data synchronized with the base tables through periodic refreshes. Unlike ordinary views (logical views), materialized views actually store data, thus they can significantly improve the performance of complex queries, especially for aggregate queries, multi-table joins, and similar scenarios. This problem will provide an in-depth analysis of the working principles, applicable scenarios, refresh strategies, and optimization practices of materialized views.

I. Core Differences Between Materialized Views and Ordinary Views

  1. Ordinary Views:
    • Essentially virtual tables, they only store the query definition (e.g., CREATE VIEW v AS SELECT * FROM t).
    • The underlying SQL is executed dynamically each time they are queried, incurring no extra storage overhead, but performance depends on real-time computation from the base tables.
  2. Materialized Views:
    • Persist query results as physical tables (e.g., CREATE MATERIALIZED VIEW mv AS SELECT * FROM t).
    • Queries directly access the data in the materialized view, avoiding repeated computation, but require additional storage space and a refresh mechanism to ensure data consistency.

II. Underlying Principles of Materialized Views

  1. Data Precomputation:

    • The database executes the defining query of the materialized view and writes the results to physical storage (e.g., disk).
    • For example, an aggregate query on a sales table sales: 
      CREATE MATERIALIZED VIEW sales_summary AS  
SELECT product_id, SUM(amount) AS total_sales  
FROM sales  
GROUP BY product_id;
      A subsequent query like SELECT * FROM sales_summary can then proceed without scanning the entire table or recalculating the aggregation.

  2. Query Rewrite Optimization:

    • The database optimizer automatically rewrites queries targeting the base tables to queries against the materialized view (requires query equivalence).
    • For example, the query SELECT product_id, SUM(amount) FROM sales GROUP BY product_id might be rewritten to directly scan sales_summary.

  3. Refresh Mechanism:

    • Complete Refresh: Re-executes the full query defining the materialized view. Suitable for scenarios with small data volumes or low refresh frequency.
    • Incremental Refresh: Only synchronizes changes from the base tables (e.g., capturing inserts, updates, deletes via logs). Requires database support (e.g., Oracle's FAST REFRESH).

III. Applicable Scenarios for Materialized Views

  1. Complex Aggregate Queries:
    • Such as reporting queries in data warehouses, where precomputation reduces query latency.
  2. Optimizing Cross-Table Joins:
    • Materializing the results of multi-table joins avoids frequently performing the join operations.
  3. Distributed Environments:
    • In sharded database architectures, materialized views can consolidate dispersed data, providing a unified query interface.

IV. Refresh Strategies for Materialized Views

  1. Scheduled Refresh:
    • Refreshes fully or incrementally at fixed time intervals (e.g., daily at midnight).
  2. Transaction-Consistent Refresh:
    • Synchronizes refreshes upon commit of base table transactions (e.g., Oracle's ON COMMIT mode), ensuring strong consistency but increasing transaction overhead.
  3. Manual Refresh:
    • Triggered by command (e.g., REFRESH MATERIALIZED VIEW mv), suitable for scenarios with low requirements for data real-timeness.

V. Limitations and Considerations for Materialized Views

  1. Storage Overhead: Materialized views consume physical storage space, requiring a trade-off between storage cost and performance gain.
  2. Data Latency: Non-real-time refreshed materialized views may return stale data; the refresh strategy must be chosen based on business requirements.
  3. Maintenance Complexity: Changes to the base table structure (e.g., adding or dropping columns) may require rebuilding the materialized view.

VI. Practical Case: Application of Materialized Views in E-commerce

  1. Requirement: Quickly query the total daily sales per product, avoiding real-time scans of a billion-row order table.
  2. Solution: 
    -- Create materialized view  
CREATE MATERIALIZED VIEW daily_sales AS  
SELECT order_date, product_id, SUM(amount) AS daily_total  
FROM orders  
GROUP BY order_date, product_id;  

-- Schedule daily refresh  
REFRESH MATERIALIZED VIEW daily_sales;
  3. Effect:
    • The original query time was reduced from minutes to milliseconds, but the materialized view occupies additional storage space and introduces a one-day data latency.

Summary
Materialized views, based on the principle of trading space for time, precompute and persist the results of frequent complex queries, making them a core technique for database query optimization. In practical applications, it is necessary to choose a refresh strategy based on the business scenario and to balance storage and consistency considerations.