This topic describes customer stories that show how users in various industries use materialized views to reduce query latency, automatically refresh view data, and present spot check data.
Reduce query latency for business advisor
Background information
Business advisor is an important service provided by Alibaba for tens of millions of merchants. Business advisor helps merchants analyze store operations at the earliest opportunity. During sales promotions, a large amount of data is generated and traffic spikes occur. Data analysis is especially significant in such scenarios.
Business advisor provides hourly analysis capabilities based on AnalyticDB for MySQL. It can be used during sales promotions. Business advisor displays an overall analysis of five major metrics, which include the number of products sold, the number of buyers, the number of visitors, the number of people who add products to the shopping cart, and the payment amount. The data of the metrics is generated from stores, products, and channels. Business advisor shows the ranking of a merchant based on the industry and location. Business advisor shows the ranking trend of a store within a day and the changes in ranking throughout the day.
Business effects
If a frontend application performs a query on data every hour, AnalyticDB for MySQL performs calculations on the hourly data in real time. These calculations include complex operations such as JOIN and GROUP BY and take at least 1 second. It requires 24 seconds to display the data of the previous 24 hours. This latency is longer than expected.
Materialized views can be used to reduce this latency. If you store hourly presentation results in materialized views, you need only to query materialized views. This reduces the time consumed by each query to 100 milliseconds.
Automatically refresh view data for automated marketing services
Background information
A customer provides automated marketing services for garment companies. In most cases, a variety of information such as membership and inventory information is required to generate reports. The amount of information is large and takes a long time to process, which leads to reports being delayed and causes marketing personnel to lose out on valuable opportunities. The large number of queries that are simultaneously processed may also cause instability in the database.
Business effects
The customer configured automatic refresh of materialized views. The views are refreshed at a specific point in time every night. Data contained in the materialized views is already processed. This accelerates queries and makes it easy to generate reports. The customer no longer needs to maintain a separate task scheduling system by using the scheduled refresh mode, which reduces costs.
Present spot check data for Cainiao
Background information
Cainiao is a significant part of Alibaba Group that handles numerous packages. Packages in warehouses must undergo spot checks. Relevant personnel view spot check reports generated within the last seven days on a regular basis. These reports contain information such as product information, spot check ratio, pass rate, and number of packages. Large amounts of computing resources are consumed to generate reports. When the number of packages surges due to events such as the Double 11 shopping festival, it takes even longer to generate reports. When a large number of personnel view the reports, large queries sent to databases on an irregular basis also cause instability in the system.
Business effects
Materialized views are used to present the spot check data in the last seven days and are refreshed on an hourly basis. When employees need to view the spot check data, they need only to query the materialized views by adding time-related filter conditions to query statements. The following code block shows an example of query statements:
CREATE MATERIALIZED VIEW xxx_report
REFRESH NEXT now + interval 1 hour
AS
SELECT ...
WHERE create_time >= select current_date() - interval 7 day