AnalyticDB:Materialized views

What are materialized views?

Materialized views are internal tables of AnalyticDB for MySQL that store the precomputed results of single-table or multi-table queries. When you query the data of a materialized view, AnalyticDB for MySQL returns the precomputed query results without the need to access base tables. This significantly improves query response time.

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

If you query the base tables of a materialized view, AnalyticDB for MySQL also helps improve the query performance. When AnalyticDB for MySQL detects that a query completely or partially matches the query in the materialized view, AnalyticDB for MySQL uses the materialized view to rewrite the query and then returns the precomputed query results to improve query response time.

Materialized views display data from the most recent refresh and may not reflect the latest data of base tables. Materialized views do not automatically refresh data after each query. To ensure the timeliness and accuracy of the data in materialized views, you can configure refresh trigger mechanisms for materialized views, such as automatic refresh at scheduled intervals, automatic refresh when base tables are overwritten, and manual refresh.

The following table describes the features of materialized views.

For information about the limits and feature updates of materialized views, see the "Limits" and "Feature updates of materialized views" sections of this topic.

Applicable scenarios

Materialized views are suitable for scenarios in which you want to accelerate periodic or repeated queries.

• Multiple queries that contain the same subquery

  If multiple queries contain the same subquery that uses resource-intensive aggregate functions, you can create a materialized view to precompute and store all query results of the subquery. If you execute a query that contains the subquery, AnalyticDB for MySQL uses the query rewrite feature to query the materialized view. This improves query response time and reduces computing resource usage.

• Periodic multi-table join operations

  If you want to periodically join multiple tables, you can create a materialized view based on the tables. The materialized view precomputes and stores the join results of the tables and automatically refreshes data in a periodic manner. If you perform a join operation on the tables, AnalyticDB for MySQL returns results from the materialized view without the need for repeated multi-table join operations. This significantly improves query response time.

• Data lake query acceleration

  If you perform a query on an external table, AnalyticDB for MySQL uses the materialized view that is created based on the external table to rewrite the query. This accelerates data lake analysis.

Feature updates of materialized views

• V3.1.4 and later: The query rewrite feature is supported for materialized views.

• V3.1.4.7 and later:

  • The OR REPLACE keyword is supported. If you want to create a materialized view that uses the same name as an existing materialized view, AnalyticDB for MySQL creates the new materialized view and deletes the original materialized view.

  • The maximum number of materialized views that you can create for an AnalyticDB for MySQL cluster is increased from 8 to 64.

• V3.1.9.0 and later: Single-table materialized views support fast refresh.

• V3.1.9.3 and later: You can use job resource groups to create and refresh elastic materialized views for Enterprise Edition, Basic Edition, and Data Lakehouse Edition clusters.

• V3.2.0.0 and later: Multi-table materialized views support fast refresh.

• V3.2.2.1 and later: For materialized views that support fast refresh, materialized view queries support the MAX(), MIN(), AVG(), APPROX_DISTINCT(), and COUNT(DISTINCT) functions.

• V3.2.3.0 and later: You can use partitioned tables as base tables to create a materialized view that supports fast refresh.

• V3.2.3.1 and later: You can execute the INSERT OVERWRITE and TRUNCATE statements on the base tables of a materialized view that supports fast refresh.

You can perform the following operations on materialized views:

• Create a materialized view

• Refresh materialized views

• Manage materialized views

• Query the data of a materialized view

For information about customer stories of materialized views, see Customer stories.