全量刷新物化视图

PolarDB 集群参数在控制台与会话中修改方式存在差异，详细区别如下：

• 在 PolarDB 控制台 上修改 ：

  • 兼容性说明 ：部分集群参数在 PolarDB 控制台上均已添加 MySQL 配置文件的兼容性前缀 loose_ 。

  • 操作方法 ：找到并修改这些带 loose_ 前缀的参数。

• 在数据库会话中修改（使用命令行或客户端）：

  • 操作方法 ：当您连接到数据库，使用 SET 命令修改参数时，请去掉 loose_ 前缀，直接使用参数的原始名称进行修改。

语法说明

CREATE
  MATERIALIZED VIEW view_name [(column_list)]
  [REFRESH [COMPLETE|FAST]]
  [ON [COMMIT|DEMAND]]
  [START WITH now()] [NEXT now() + interval 1 hour]
  [[DISABLE|ENABLE] QUERY REWRITE]
  [MATERIALIZED OPTIONS = 'json_options']
  AS select_statement

权限要求

• 具有物化视图所在数据库的 CREATE 权限。

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

参数说明

mv_options 用法

mv_options 通过 MATERIALIZED OPTIONS = 'json_options' 子句指定，必须写在 AS select_statement 之前， json_options 建议使用合法的 JSON 对象。

• 支持版本 ：

  • MySQL 8.0.1 ，且内核小版本需为 8.0.1.1.55 及以上。

  • MySQL 8.0.2 ，且内核小版本需为 8.0.2.2.35 及以上。

• mv_options 支持以下字段 ：

  字段	类型	取值	说明
  order_key	STRING	列名或表达式文本。	设置物化视图容器表的 order_key 信息。
  engine	STRING	InnoDB XEngine	指定物化视图容器表使用的存储引擎，大小写不敏感。
  table_format	STRING	row column	指定容器表的行存或列存格式，大小写不敏感。 InnoDB 引擎仅支持 row XEngine 引擎支持 row 与 column ，通常和 engine: "xengine" 配合使用。
  refresh_policy	STRING	MASTER PREFER_TPRO PREFER_IMCI FORCE_TPRO FORCE_IMCI COST	指定全量刷新任务的下推策略，大小写不敏感。空串或未知值会按系统变量默认策略处理。 MASTER ：在主节点刷新。 PREFER_TPRO ：优先在行存只读（RO）节点刷新，若行存只读（RO）节点不存在，则在主节点刷新。 PREFER_IMCI ：优先在列存索引只读节点刷新，若列存索引只读节点不存在，则在主节点刷新。 FORCE_TPRO ：强制在行存只读（RO）节点刷新，若行存只读（RO）节点不存在，则报错。 FORCE_IMCI ：强制在列存索引只读节点刷新，若列存索引只读节点不存在，则报错。 COST ：基于查询代价，在行存只读（RO）节点或列存索引只读节点中选择其一执行刷新。
  unique_check	INT	1 0	用于 XEngine 列存容器表。 0 表示刷新期间放宽唯一性检查。 1 或省略表示保持默认检查。

  说明

  engine 、 table_format 、 refresh_policy 、 unique_check 暂只支持 MySQL 8.0.2 版本。

• 注意事项 ：

  • MATERIALIZED OPTIONS = '' 会被视为未设置扩展参数。

  • SHOW CREATE TABLE 输出物化视图定义时，会带出已设置的 MATERIALIZED OPTIONS 子句。

  • 语法层只接收字符串，后续解析失败时会记录 WARNING ，并按未解析到对应参数的默认行为继续处理。

• 示例 ：

  CREATE MATERIALIZED VIEW mv1
    REFRESH COMPLETE
    ON DEMAND
    START WITH now()
    NEXT now() + INTERVAL 1 hour
    MATERIALIZED OPTIONS = '{"engine":"xengine","table_format":"column","order_key":"col1"}'
    AS SELECT col1, col2 FROM tbl1;

示例

1. 创建表 table1 和 table2 作为物化视图的基表。

   -- 创建基表
   CREATE TABLE table1 (col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT);
   CREATE TABLE table2 (col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT);
   -- 向基表中插入数据
   INSERT INTO table1 VALUES (1, 'A', 100), (2, 'B', 200), (3, 'C', 300), (4, 'D', 400);
   INSERT INTO table2 VALUES (1, 'X', 10), (2, 'Y', 20), (3, 'Z', 30), (5, 'W', 50);

2. 创建物化视图。

   CREATE MATERIALIZED VIEW mv1
   REFRESH COMPLETE
   ON DEMAND
   START WITH now() NEXT now() + INTERVAL 1 hour
   SELECT
       SUM(t1.col3) AS sum_value,
       AVG(t1.col3) AS avg_value
   FROM table1 t1
   JOIN table2 t2 ON t1.col1 = t2.col1;

3. 创建带 mv_options 的物化视图。

   CREATE MATERIALIZED VIEW mv2
     REFRESH COMPLETE
     ON DEMAND
     START WITH now()
     NEXT now() + INTERVAL 1 hour
     MATERIALIZED OPTIONS = '{"engine":"xengine","table_format":"column","refresh_policy":"FORCE_IMCI"}'
       SELECT t1.col1, t1.col2, t1.col3
       FROM table1 t1 JOIN table2 t2 ON t1.col1 = t2.col1;

语法说明

DROP MATERIALIZED VIEW <view_name>;

示例

DROP MATERIALIZED VIEW mv1;

查看任务队列

通过系统视图 information_schema.materialized_view_refresh_queue ，可以查询刷新队列信息。

SELECT * FROM information_schema.materialized_view_refresh_queue;

暂停任务队列

STOP MATERIALIZED VIEW task FOR TABLE mv1;

STOP MATERIALIZED VIEW task FOR DATABASE mv1;

STOP MATERIALIZED VIEW task FOR ALL;

重启任务队列

RESTART MATERIALIZED VIEW task FOR TABLE mv1;

RESTART MATERIALIZED VIEW task FOR DATABASE mv1;

RESTART MATERIALIZED VIEW task FOR ALL;

验证暂停/重启状态

执行 STOP 或 RESTART 命令后，可通过查询 mysql.view_materialized_info 表的 IS_STOPPED 列来确认操作是否生效。

SELECT TABLE_SCHEMA, TABLE_NAME, IS_STOPPED FROM mysql.view_materialized_info;

• IS_STOPPED=1 表示已暂停定时刷新。

• IS_STOPPED=0 表示正在运行。