AnalyticDB:Materialized views

What are materialized views?

Materialized views are internal tables in AnalyticDB for MySQL that store precomputed results from single-table or multi-table queries. When you query a materialized view, the database returns the precomputed results directly without accessing the base tables. This significantly improves query response speed.

Only the XIHE compute engine can create materialized views. You cannot use the Spark engine to create materialized views.

Even when you query the base tables directly, materialized views can improve performance. When AnalyticDB for MySQL detects that a user query fully or partially matches the query defined in a materialized view, it automatically rewrites the query on the base tables to a query on the materialized view. This uses the precomputed results to accelerate the query.

Note that materialized views reflect the data state after the most recent refresh and may not contain the latest data from the base tables. Materialized views do not automatically refresh data every time they are queried. You can set up an auto-refresh schedule, trigger an auto-refresh when a base table is overwritten, or perform a manual refresh to ensure data timeliness and accuracy.

The following table describes the features of materialized views.

For more information about the limits and feature update history of materialized views, see Limits and Feature update history.

When to create a materialized view

Materialized views are ideal for accelerating queries that run periodically or repeatedly.

• Multiple queries contain the same subquery

  If multiple queries contain the same subquery that uses resource-intensive aggregate functions, create a materialized view to precompute and store all results of the subquery. When a query contains that subquery, the AnalyticDB for MySQL query rewrite feature automatically queries the materialized view. This accelerates the query and reduces resource consumption.

• Periodic multi-table joins

  To periodically join multiple tables, create a materialized view for the join operation. The materialized view precomputes and stores the join results and automatically refreshes the data at scheduled intervals. When a query includes this join operation, the results are returned directly from the materialized view without re-executing the multi-table join. This greatly improves query response speed.

• Data lake query acceleration

  When a user queries an external table, AnalyticDB for MySQL can automatically rewrite the query on the external table to a query on the materialized view. This accelerates data lake analysis.

Major feature update history for materialized views

• V3.1.4 and later: Query rewrite for materialized views is supported.

• V3.1.4.7 and later:

  • The OR REPLACE keyword is supported. If a materialized view with the same name exists, the original materialized view is deleted and a new one is created.

  • The maximum number of materialized views is increased from 8 to 64.

• V3.1.9.0 and later: Incremental refresh for single-table materialized views is supported.

• V3.1.9.3 and later: Elastic materialized views are supported for Enterprise Edition, Basic Edition, and Data Lakehouse Edition clusters. You can use job-based resource groups to create and refresh these views.

• V3.2.0.0 and later: Incremental refresh for multi-table materialized views is supported.

• V3.2.2.1 and later: For materialized views that support incremental refresh, the query body supports the MAX, MIN, AVG, APPROX_DISTINCT, and COUNT(DISTINCT) functions.

• V3.2.3.0 and later: For materialized views that support incremental refresh, partitioned tables are supported as base tables.

• V3.2.3.1 and later: For materialized views that support incremental refresh, the TRUNCATE operation is supported on base tables.

• V3.2.5.0 and later:

  • For materialized views that support incremental refresh, the INSERT OVERWRITE operation is supported on base tables.

  • Binary logging (binlog) can be enabled for materialized views. For materialized views that support incremental refresh, other materialized views can be used as base tables, and the UNION ALL statement is supported.

• V3.2.6.0 and later: For materialized views that support incremental refresh, XUANWU_V2 tables are supported as materialized views or base tables.

You can perform the following operations on materialized views:

• Create a materialized view

• Perform a full refresh of a materialized view

• Refresh a materialized view

• Manage materialized views

• Query a materialized view

For customer use cases of materialized views, see Customer use cases for materialized views.