创建物化视图

权限要求

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

• 物化视图所在数据库的创建表的权限（CREATE权限）。

• 物化视图所有基表的相关列（或整个表）的SELECT权限。

• 如需创建自动刷新的物化视图，还需要具备以下两个权限：

  • 从任意IP（即'%'）连接AnalyticDB for MySQL的权限。

  • 物化视图或物化视图所在数据库所有表的INSERT权限，否则物化视图中的数据无法刷新。

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

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

/*+ RC_DDL_ENGINE_REWRITE_XUANWUV2=false */ -- 指定表的引擎为XUANWU引擎。
CREATE TABLE customer (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(255),
    is_vip Boolean
);

/*+ RC_DDL_ENGINE_REWRITE_XUANWUV2=false */ -- 指定表的引擎为XUANWU引擎。
CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT,
    price DECIMAL(10, 2),
    quantity INT,
    sale_date TIMESTAMP
);

创建全量刷新的物化视图

您可以基于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功能。

  如果开启基表的Binlog功能报错，解决方案请参见下文的Query execution error: : Can not create FAST materialized view, because demotable doesn't support getting incremental data。

  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未查询到任何记录，说明物化视图已创建完成（包括已创建物化视图的表结构，且物化视图中已加载初始数据）。

全量物化视图的基表查询

全量刷新的物化视图，基表可以是AnalyticDB for MySQL内表、外表、已有的物化视图和视图。基表的查询没有限制。查询语法，可以参考SELECT。

增量物化视图的基表查询

增量刷新的物化视图，基表只能是AnalyticDB for MySQL内表。基表的查询有以下规则：

通用限制

通用限制适用于所有物化视图，包括全量物化视图和增量物化视图。

• 不支持对物化视图执行INSERT、DELETE或UPDATE操作。

• 不支持删除或重命名物化视图的基表或基表中的列。如需修改基表需要先删除物化视图。

• 默认情况下，一个AnalyticDB for MySQL集群支持的物化视图数量上限如下：

  • 内核版本等于或大于3.1.4.7：最多支持创建64个物化视图。

  • 内核版本低于3.1.4.7：最多支持创建8个物化视图。

  说明

  如果物化视图的数量已达到上限，您可以联系技术支持，申请提高物化视图数量上限。

全量物化视图的限制

集群增加或减少预留节点数期间，无法执行异步任务。物化视图全量刷新为异步任务，因此此时无法全量刷新。增量刷新不受影响。

增量物化视图的限制

• 增量物化视图对基表的限制：

  • 不支持XUANWU_V2表作为增量物化视图的基表。

    原因为XUANWU_V2引擎的表暂不支持开启Binlog。

  • 内核版本为3.2.3.0以下的集群，不支持分区表作为增量物化视图的基表。

  • 内核版本为3.2.3.1以下的集群，增量物化视图的基表不支持INSERT OVERWRITE和TRUNCATE，执行会报错。

  • 当query_body中使用MAX、MIN、APPROX_DISTINCT或COUNT(DISTINCT)聚合函数时，增量物化视图的基表只支持INSERT操作，不支持DELETE、UPDATE、REPLACE、INSERT ON DUPLICATE KEY UPDATE等会导致数据删除的操作。

• 增量物化视图的刷新触发机制的限制：

  只支持定时自动刷新，不支持手动刷新。刷新间隔最短5秒（s），最长5分钟（min）。

• 增量物化视图，query_body的规则与限制：

  • 不支持非确定性的表达式，如：NOW()、RAND()等。

  • 不支持ORDER BY排序操作。

  • 不支持HAVING子句。

  • 不支持窗口函数。

  • 不支持UNION、EXCEPT、INTERSECT等集合操作。

  • JOIN操作仅支持INNER JOIN。关联字段须满足所有条件：须为表的原始字段，数据类型须相同，且有INDEX索引。最多关联5张表。

    如需关联更多表，请提交工单联系技术支持。

  • 仅支持以下聚合函数：COUNT、SUM、MAX、MIN、AVG、APPROX_DISTINCT和COUNT(DISTINCT)。

  • AVG不支持DECIMAL类型。

  • COUNT(DISTINCT)仅支持INTEGER类型。

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

在定义物化视图时，使用日期列作为分区键（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功能。

如果开启基表的Binlog时，出现报错XUANWU_V2 engine not support ALTER_BINLOG_ENABLE now，说明基表demotable的引擎是XUANWU_V2，而XUANWU_V2引擎暂不支持Binlog功能。由于表的引擎无法变更，您需要关闭XUANWU_V2引擎的开关，重新创建XUANWU引擎的表，将XUANWU_V2表的数据迁移到XUANWU表，并基于XUANWU表创建增量刷新的物化视图。

关闭XUANWU_V2引擎的开关，方法如下：

• 如果基表是通过DTS、无感集成、控制台数据同步等工具自动创建的，那么您可以在集群级别关闭XUANWU_V2引擎的开关。关闭后，新建的表都会是XUANWU引擎。

  在集群全局，关闭XUANWU_V2引擎：SET adb_config RC_DDL_ENGINE_REWRITE_XUANWUV2=false;

• 如果基表是通过CREATE TABLE手动创建的，且需要创建增量物化视图的基表数量不多，那么可以选择当前建表语句关闭XUANWU_V2引擎的开关。仅当前建表语句，新建的表是XUANWU引擎。而其他建表语句，新建的表仍是XUANWU_V2引擎。

  在表级别，关闭XUANWU_V2引擎：/*+ RC_DDL_ENGINE_REWRITE_XUANWUV2=false */CREATE TABLE ...

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的聚合查询），主键必须为常量。

  • 非聚合查询，主键必须与基表的主键完全相同。例如，基表主键为PRIMARY KEY(sale_id,sale_date)，那么物化视图的主键也必须为PRIMARY KEY(sale_id,sale_date)。

• 基表的主键列使用了函数：修改物化视图查询（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详情分析查询。