创建物化视图_云原生数据仓库AnalyticDB(AnalyticDB)-阿里云帮助中心

多表Join和复杂聚合计算通常消耗较多计算资源，且查询耗时长。AnalyticDB for MySQL物化视图可以解决此类问题。物化视图会将用户定义的查询提前计算好并将查询结果存储起来。查询时可以直接从物化视图中读取预先计算好的查询结果，从而加快查询响应速度。本文主要介绍如何创建物化视图。

前提条件

内核为3.1.3.4及以上版本。

查询内核版本的方法

查看企业版、基础版或湖仓版集群的内核版本，请执行SELECT adb_version();。如需升级内核版本，请联系技术支持。
请在云原生数据仓库AnalyticDB MySQL控制台的集群信息页面的配置信息区域，查看和升级数仓版集群的内核版本。操作详情请参见查看和升级版本。

创建物化视图

权限要求

创建物化视图的用户需要具备以下所有权限：

物化视图所在数据库的创建表的权限（CREATE权限）。
物化视图所有基表的相关列（或整个表）的SELECT权限。
如需创建自动刷新的物化视图，还需要具备以下两个权限：
- 从任意IP（即'%'）连接AnalyticDB for MySQL的权限。
- 物化视图或物化视图所在数据库所有表的INSERT权限，否则物化视图中的数据无法刷新。

准备用于创建物化视图的基表

下文以customer表和sales表为例，指导您快速创建物化视图。

CREATE TABLE customer (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(255),
    is_vip Boolean
);

CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT,
    price DECIMAL(10, 2),
    quantity INT,
    sale_date TIMESTAMP
);

下文创建物化视图的示例未指定资源组。当未指定资源组时，由默认资源组（Interactive型资源组）的计算资源来创建和刷新物化视图。如果您想了解Interactive资源组和Job型资源组在创建和刷新物化视图的区别，或者想使用Job型资源组创建和刷新物化视图，请参见使用弹性资源创建或刷新物化视图。

创建全量刷新的物化视图

您可以基于AnalyticDB for MySQL内表、外表、已有的物化视图和视图创建全量刷新的物化视图。

本文以基表customer和sales为例，创建全量刷新的物化视图join_mv，刷新触发机制为手动刷新。

CREATE MATERIALIZED VIEW join_mv
REFRESH COMPLETE ON DEMAND
AS
SELECT 
sale_id,                
SUM(price * quantity) AS price                  
FROM customer
INNER JOIN (SELECT sale_id,customer_id,price,quantity FROM sales) sales ON customer.customer_id = sales.customer_id
GROUP BY customer.customer_id;

如需更新物化视图的数据，请手动执行REFRESH MATERIALIZED VIEW join_mv;。

创建增量刷新的物化视图

您可以基于AnalyticDB for MySQL内表创建增量刷新的物化视图。

下文将指导您创建一个多表关联的增量刷新物化视图。

在创建增量刷新的物化视图之前，请完成以下准备工作：

检查集群的内核版本是否满足3.1.9.0及以上版本。

开启集群级别的Binlog特性及基表的Binlog功能。

SET ADB_CONFIG BINLOG_ENABLE=true;
ALTER TABLE customer binlog=true;
ALTER TABLE sales binlog=true;

创建增量刷新的物化视图sales_mv_incre。该物化视图只涉及sales表的查询计算。

CREATE MATERIALIZED VIEW sales_mv_incre
REFRESH FAST NEXT now() + INTERVAL 3 minute
AS
SELECT 
sale_id,                
SUM(price * quantity) AS price                  
FROM sales
GROUP BY sale_id;

如果集群的内核版本为3.2.1.0及以上版本，您还可以创建增量刷新的多表关联物化视图join_mv_incre，刷新间隔为每3分钟自动刷新一次。

CREATE MATERIALIZED VIEW join_mv_incre
REFRESH FAST NEXT now() + INTERVAL 3 minute
AS
SELECT 
customer.customer_id,                
SUM(sales.price) AS price                  
FROM customer
INNER JOIN (SELECT customer_id,price FROM sales) sales ON customer.customer_id = sales.customer_id
GROUP BY customer.customer_id;

物化视图的语法详情和更多示例，请参见CREATE MATERIALIZED VIEW。

查看创建中的物化视图

通过SHOW PROCESSLIST WHERE info LIKE '%CREATE MATERIALIZED VIEW%';，可以查询正在创建的物化视图。

返回结果的每一行记录代表一个正在创建的物化视图。其中，user为创建物化视图的数据库账号，status为创建的状态，Info包含了物化视图的创建语句。更多关于返回结果的字段信息，请参见SHOW PROCESSLIST。

点击查看返回结果示例

+-------+---------------------------------------+-------+-------------------+-------+-------------------+----+-------+----------------------------------------------------------------------------------------------------+
|Id     |ProcessId                              |User   |Host               |DB     |Command            |Time|State  |Info                                                                                                |
+-------+---------------------------------------+-------+-------------------+-------+-------------------+----+-------+----------------------------------------------------------------------------------------------------+
|31801  |2025012714472702101701716603151*****   |wenjun |21.17.xx.xx:49534  |demo1  |INSERT_FROM_SELECT |2   |RUNNING|/*process_id=2025012714472702101708007503151*****,access_port=62042,access_ip=59.82.xx.xx/          |
|       |                                       |       |                   |       |                   |    |       |CREATE MATERIALIZED VIEW join_mv                                                                    |
|       |                                       |       |                   |       |                   |    |       |REFRESH COMPLETE ON DEMAND                                                                          |
|       |                                       |       |                   |       |                   |    |       |AS                                                                                                  |
|       |                                       |       |                   |       |                   |    |       |SELECT                                                                                              |
|       |                                       |       |                   |       |                   |    |       |sale_id,                                                                                            |               
|       |                                       |       |                   |       |                   |    |       |SUM(price * quantity) AS price                                                                      |                  
|       |                                       |       |                   |       |                   |    |       |FROM customer                                                                                       |
|       |                                       |       |                   |       |                   |    |       |INNER JOIN (SELECT customer_id,price FROM sales) sales ON customer.customer_id = sales.customer_id  |
|       |                                       |       |                   |       |                   |    |       |GROUP BY customer.customer_id;                                                                      |
+-------+---------------------------------------+-------+-------------------+-------+-------------------+----+-------+----------------------------------------------------------------------------------------------------+

如果SHOW PROCESSLIST未查询到任何记录，说明物化视图已创建完成（包括已创建物化视图的表结构，且物化视图中已加载初始数据）。

使用弹性资源创建或刷新物化视图

弹性资源是指Job型资源组的计算资源。创建以及后续刷新物化视图，都会消耗集群的计算资源。在未指定资源组（未定义MV_PROPERTIES='{"mv_resource_group":"job_rg_name"}'）的情况下，创建和刷新物化视图会使用默认资源组（名称为user_default的Interactive型资源组）的预留计算资源。

如果创建物化视图时，指定了Job型资源组，那么创建和后续刷新物化视图，都会使用该Job型资源组。您还可以通过"mv_refresh_hints":{"elastic_job_max_acu":"value"}定义物化视图可以使用的资源量，用法详情请参见mv_properties。

使用默认Interactive型资源组和Job型资源组的区别在于：使用Job型资源组，无需提前购买资源，有效避免了资源浪费，因此可降低成本。但物化视图的刷新性能不如Interactive型资源组，因为每次刷新物化视图，Job型资源组都需要临时拉起计算资源，拉起资源的时间大约为秒级或分钟级。

如果集群满足以下条件，您可以使用Job型资源组的弹性资源去创建和刷新物化视图。

集群的产品系列为企业版、基础版或湖仓版。
集群内核版本需为3.1.9.3及以上版本。

以上文的customer表为例，使用Job型资源组my_job_rg的弹性资源创建和刷新物化视图，且优先级为高。

CREATE MATERIALIZED VIEW job_mv
MV_PROPERTIES='{
  "mv_resource_group":"my_job_rg",
  "mv_refresh_hints":{"query_priority":"HIGH"}
}'
REFRESH COMPLETE ON DEMAND
START WITH now()
NEXT now() + INTERVAL 1 DAY
AS
SELECT * FROM customer;

物化视图的刷新触发机制

物化视图反映最近一次刷新的数据状态，可能不是基表的最新数据。物化视图也不会在每次查询时自动刷新数据。为了确保物化视图数据的及时性和准确性，您可以设置物化视图定时自动刷新、基表覆盖写时自动刷新，或者手动刷新。

刷新物化视图时，根据物化视图的刷新策略，刷新全量数据或增量数据。

关于刷新触发机制和刷新策略的区别、应用场景等更多详情，请参见刷新物化视图。

使用限制

不支持对物化视图执行INSERT、DELETE或UPDATE操作。
不支持删除或重命名物化视图的基表或基表中的列。如需修改基表需要先删除物化视图。
默认情况下，一个AnalyticDB for MySQL集群支持的物化视图数量上限如下：
- 内核版本等于或大于3.1.4.7：最多支持创建64个物化视图。
- 内核版本低于3.1.4.7：最多支持创建8个物化视图。
说明
如果物化视图的数量已达到上限，您可以联系技术支持，申请提高物化视图数量上限。
集群扩缩容期间，物化视图无法进行全量刷新，因为全量刷新为异步任务。增量刷新不受影响。
增量刷新的物化视图限制如下：
- 内核版本为3.2.3.0以下的集群，不支持分区表作为增量刷新的物化视图的基表。
- 内核版本为3.2.3.1以下的集群，增量刷新的物化视图，基表不支持INSERT OVERWRITE和TRUNCATE，执行会报错。
- 增量刷新只支持定时自动刷新，不允许手动刷新。定时自动的增量刷新，刷新间隔最短5秒（s），最长5分钟（min）。
- 增量刷新的物化视图，query_body有以下限制：
  - 由于物化视图要保证结果和您查询基表的结果完全一致，且要支持任意DML变更，所以并不是所有query_body都可以增量刷新。如果创建的物化视图无法增量刷新，创建时会报错。
  - 不允许出现非确定性的表达式作为条件，如：now()、rand()等。
  - 仅支持以下聚合函数：COUNT、SUM、MAX、MIN、AVG、APPROX_DISTINCT和COUNT(DISTINCT)。
  - 当query_body中使用MAX、MIN、APPROX_DISTINCT或COUNT(DISTINCT)聚合函数时，增量物化视图的基表只允许执行INSERT操作，禁止执行DELETE、UPDATE、REPLACE、INSERT ON DUPLICATE KEY UPDATE等会导致数据删除的操作。
  - 除COUNT(DISTINCT)外，其余聚合函数均不支持DISTINCT关键字。
  - COUNT(DISTINCT)仅支持INTEGER类型。
  - AVG不支持DECIMAL类型。
  - 聚合操作不支持HAVING关键字。
  - 不支持窗口函数。
  - 不支持排序操作。
  - 不支持UNION、EXCEPT、INTERSECT等集合操作。
- 增量刷新的多表物化视图，还有以下限制：
  - 多表物化视图目前仅支持使用INNER JOIN。
  - 多表物化视图中默认最多关联5张表。若您有需求，可提交工单联系技术支持，根据集群规格调大该限制。
  - 多表物化视图中的关联字段需为表的原始字段，且关联字段的数据类型相同，均有INDEX索引。
弹性物化视图（即使用了Job型资源组的物化视图）限制如下：
- 集群的产品系列需为企业版、基础版或湖仓版。
- 集群内核版本需为3.1.9.3及以上版本。

常见问题

物化视图如何只保留最近一年的数据?

在定义物化视图时，使用日期列作为分区键（PARTITION BY）并设置生命周期（LIFECYCLE），可实现仅保留最近一年的数据。

假设sales表每天都会产生新的销售数据，那么物化视图使用销售日期（sales_date）作为分区键，并设置生命周期为365（即最多保留365个分区），即可实现仅保留最近一年的数据。

CREATE MATERIALIZED VIEW sales_mv_lifecycle
PARTITION BY VALUE(DATE_FORMAT(sale_date, '%Y%m%d')) LIFECYCLE 365
REFRESH FAST NEXT now() + INTERVAL 100 second
AS
SELECT 
sale_date,                
SUM(price * quantity) AS price                  
FROM sales
GROUP BY sale_date;

常见报错

Query execution error: : Can not create FAST materialized view, because demotable doesn't support getting incremental data

报错原因：增量刷新的物化视图，基表demotable没有开启Binlog功能。增量刷新的物化视图，需要所有涉及的基表开启Binlog功能。

解决方法：执行ALTER TABLE demotable binlog=true;开启基表的Binlog功能。

Query execution error: : PRIMARY KEY id must output to MV.

报错原因：增量刷新的物化视图查询（query_body）为无GROUP BY的非聚合查询，此时物化视图的主键需要与基表的主键相同，但物化视图查询的SELECT列表中没有输出基表的主键列。

错误示例如下：

CREATE MATERIALIZED VIEW wrong_example1
REFRESH FAST ON DEMAND
 NEXT now() + interval 200 second
AS
SELECT product_id,price -- 基表sales的主键为sale_id，但SELECT列表中没有sale_id。
FROM sales;

解决方法：在物化视图查询的SELECT列表中输出基表的主键列。

正确示例为：

CREATE MATERIALIZED VIEW correct_example1
REFRESH FAST ON DEMAND
 NEXT now() + interval 200 second
AS
SELECT sale_id,product_id, price -- 在SELECT列表中添加基表主键sale_id。
FROM sales;

Query execution error: : MV PRIMARY KEY must be equal to base table PRIMARY KEY.

报错原因：增量刷新的物化视图查询（query_body）为无GROUP BY的非聚合查询，此时物化视图的主键需要与基表的主键相同，但物化视图的定义（mv_definition）中，将基表主键和非基表主键的列定义为了物化视图的主键。

错误示例如下：

CREATE MATERIALIZED VIEW wrong_example2
(PRIMARY KEY(sale_id,product_id)) -- product_id不是基表sales的主键列。
REFRESH FAST ON DEMAND
 NEXT now() + interval 200 second
AS
SELECT sale_id,product_id,price
FROM sales;

解决方法：修改物化视图的主键，将不是基表主键的列移除。

正确示例如下：

CREATE MATERIALIZED VIEW correct_example2
(PRIMARY KEY(sale_id)) -- 物化视图的主键列移除product_id。
REFRESH FAST ON DEMAND
 NEXT now() + interval 200 second
AS
SELECT sale_id,product_id,price
FROM sales;

Query execution error: : FAST materialized view must define PRIMARY KEY

报错原因：报错可能有以下两个原因：

增量刷新物化视图没有定义正确的主键。
在物化视图的查询（query_body）中对基表的主键列使用了函数。

解决方法：请根据不同的原因，选择合适的解决方法。

没有定义正确的主键：修改物化视图的定义（mv_definition），确保主键满足如下规则。
- 存在GROUP BY时，主键必须为GROUP BY列。例如，GROUP BY a,b，主键必须是a和b。
- 无GROUP BY的聚合操作，主键应为全局唯一的常量。
- 无GROUP BY的非聚合操作，主键应与基表的主键相同。
基表的主键列使用了函数：修改物化视图查询（query_body），删除基表主键列的函数。

Query execution error: : The join graph is not supported.

报错原因：增量刷新的物化视图查询（query_body）中多表Join的关联列数据类型不同。例如，sales INNER JOIN customer ON customer.id=sales.id，如果customer.id与sales.id数据类型不同，就会出现该报错。

解决方法：执行ALTER TABLE tablename MODIFY COLUMN columnname newtype;修改多表Join关联列的数据类型。详情请参见变更列的数据类型。

Query execution error: : Unable to use index join to refresh this fast MV.

报错原因：增量刷新的物化视图查询（query_body）中多表Join的关联列没有INDEX索引。例如,sales INNER JOIN customer ON customer.id=sales.id，如果基表customer中的customer.id或基表sales中的sales.id没有创建INDEX索引，就会出现该报错。

解决方法：执行ALTER TABLE tablename ADD KEY idx_name(columnname);为基表中的列添加索引。详情请参见增加索引。

Query execution error: : Query exceeded reserved memory limit

报错原因：查询在单个节点上占用的内存超过限制。

解决方法：占用内存较高的SQL一般包含Aggregation算子、TopN算子、Window算子以及Join算子。您可以利用SQL诊断功能，查询内存占用较高的Stage和算子，然后对内存占用较高的算子执行SQL调优。详情请参见内存指标和使用Stage和Task详情分析查询。