Materialized views in AnalyticDB for MySQL pre-compute and store query results so that repeated reads hit cached data instead of running expensive aggregations each time. The following cases show how three teams used materialized views to cut query latency, eliminate manual refresh pipelines, and stabilize databases under heavy concurrent load.
When to use materialized views
Materialized views work best when:
-
Queries involve complex operations (JOIN, GROUP BY, aggregations) and run frequently
-
Source data changes on a predictable schedule (hourly, nightly)
-
A large number of users query the same summarized data concurrently
-
Long report-generation times are causing missed business opportunities
Reduce query latency for Business Advisor
Background
Business Advisor is an Alibaba service used by tens of millions of merchants to analyze store operations. During sales promotions, traffic spikes make real-time analysis especially critical. The service tracks five metrics — products sold, buyers, visitors, shopping cart additions, and payment amount — broken down by store, product, and channel, and shows each merchant's ranking by industry and location. Business Advisor also shows the ranking trend of a store within a day and the changes in ranking throughout the day.
Business Advisor runs on AnalyticDB for MySQL and provides hourly analysis during sales promotions.
How materialized views helped
Without materialized views, each hourly query triggered real-time calculations involving JOIN and GROUP BY operations, taking at least 1 second per query. Displaying 24 hours of data required 24 queries — a total of 24 seconds. This exceeded acceptable latency thresholds.
By storing pre-computed hourly results in materialized views, each query reads from the view rather than recalculating from raw data. Query time dropped from 1 second to 100 milliseconds per query.
Automate data refresh for a marketing platform
Background
A customer provides automated marketing services for garment companies. Generating reports requires membership data, inventory data, and other sources. The volume of data and the complexity of the processing caused reports to be delayed, making marketing personnel miss time-sensitive opportunities. The large number of queries that are simultaneously processed may also cause instability in the database.
How materialized views helped
The team configured automatic refresh of materialized views on a nightly schedule. Because the views store pre-processed results, report queries run against already-aggregated data rather than raw tables.
Two outcomes:
-
Faster reports: Queries complete quickly because the heavy computation runs during the off-peak refresh window, not at query time.
-
No separate scheduler: The scheduled refresh mode in AnalyticDB for MySQL handles the refresh automatically, eliminating the need to maintain a separate task scheduling system.
Stabilize spot-check reporting for Cainiao
Background
Cainiao, part of Alibaba Group, processes large volumes of warehouse packages. Employees regularly review spot-check reports covering the previous seven days. Each report includes product information, spot check ratio, pass rate, and package count.
Generating these reports consumed significant computing resources. During peak events like Double 11, package volumes surged and report generation slowed further. Irregular, large queries from many concurrent users compounded the instability.
How materialized views helped
Cainiao created a materialized view that pre-computes seven-day spot-check data and refreshes it every hour. Employees query the view with a time-based filter instead of running full aggregations against raw tables.
CREATE MATERIALIZED VIEW xxx_report
REFRESH NEXT now + interval 1 hour
AS
SELECT ...
WHERE create_time >= select current_date() - interval 7 day