本文介绍如何创建物化视图。

权限要求

  • 需要有数据库或表级别的CREATE权限。
  • 需要有数据库或表级别的INSERT权限。
  • 需要有物化视图所涉及的所有表的相关列（或整个表）的SELECT权限。
  • 如果要启用自动刷新功能，需要创建者具备通过服务器本地（即127.0.0.1）或者任意IP（即'%'）刷新视图的权限。

语法

CREATE  MATERIALIZED VIEW <mv_name>
[MV DEFINITION]
[REFRESH COMPLETE [ON <DEMAND|OVERWRITE>] [START WITH date] [NEXT date]]
AS 
<QUERY BODY>;

关键字说明

关键字 说明
MV DEFINITION 定义物化视图中表相关的特性。物化视图使用一张普通的表结构来存储数据，您可以使用该关键字指定物化视图的主键，分区键，索引等。
说明 与创建表的语法一致，创建物化视图时也不支持定义查询中没有输出的列。
REFRESH COMPLETE 定义物化视图的刷新模式，当前仅支持COMPLETE全量刷新模式。
ON 定义全量刷新模式的触发方式，支持如下取值：
  • ON DEMAND：表示按需触发全量刷新，即您可以在需要刷新物化视图时手动触发刷新，或根据刷新需求配置自动触发刷新。
  • ON OVERWRITE：表示物化视图引用的基表在执行INSERT OVERWRITE语句导致数据被覆盖后，会触发全量刷新，适合通过batchload批量导入数据的场景。
说明
  • 仅当刷新模式REFRESH参数设置为COMPLETE时，该参数生效。
  • 未指定该参数时，默认全量刷新触发模式为ON DEMAND。
START WITH和NEXT 定义自动进行全量刷新物化视图时的首次刷新时间（即START WITH）和下次刷新时间（即NEXT）。
说明
  • START WITH参数选填，若不填，默认首次刷新时间为当前时间点。
  • 若需要设置自动刷新，NEXT参数必填。
  • 支持使用时间函数，但只支持精确到秒，毫秒部分会被截断。

更多关于刷新物化视图的详情，请参见刷新物化视图。

QUERY BODY 定义物化视图的查询主体，其中：
  • 需要为查询中输出的表达式列定义别名，建议使用具有实际意义的别名。例如（sum(price) AS total_price），表示设置表达式列sum(price)的别名为total_price。
  • 物化视图查询所涉及的基表不可被删除，且基表中的列不可被删除或修改。
  • 可以使用WITH语法进行查询，查询主体可以基于逻辑视图或者物化视图。

使用限制

  • 不支持对物化视图执行INSERT、DELETE或UPDATE操作。
  • 不支持删除或重命名物化视图中引用的基表或基表中的列。如需修改基表需要先删除物化视图。
  • 默认情况下，一个AnalyticDB MySQL版集群支持创建的物化视图上限如下：
    • 集群版本小于3.1.4.7：最多支持创建8个物化视图。
    • 集群版本等于或大于3.1.4.7：最多支持创建64个物化视图。
    说明 若您需要创建更多物化视图，请提交工单联系技术支持。

示例

# 每五分钟刷新一次
CREATE MATERIALIZED VIEW myview
REFRESH NEXT now() + interval 5 minute
AS
SELECT count(*) as cnt FROM base;

# 每周一晚上2点刷新
CREATE MATERIALIZED VIEW myview
REFRESH 
 START WITH DATE_FORMAT(now() + interval 7 - weekday(now()) day, '%Y-%m-%d 02:00:00') 
 NEXT DATE_FORMAT(now() + interval 7 - weekday(now()) day, '%Y-%m-%d 02:00:00')
AS
SELECT count(*) as cnt FROM base;

# 每天凌晨2点刷新
CREATE MATERIALIZED VIEW myview
REFRESH 
 START WITH DATE_FORMAT(now() + interval 1 day, '%Y-%m-%d 02:00:00')
 NEXT DATE_FORMAT(now() + interval 1 day, '%Y-%m-%d 02:00:00')
AS
SELECT count(*) as cnt FROM base;

# 每个月第一天凌晨2点刷新
CREATE MATERIALIZED VIEW myview
REFRESH NEXT DATE_FORMAT(last_day(now()) + interval 1 day, '%Y-%m-%d 02:00:00')
AS
SELECT count(*) as cnt FROM base;

# 只刷一次
CREATE MATERIALIZED VIEW myview
REFRESH START WITH now() + interval 1 day
AS 
SELECT count(*) as cnt FROM base;

# 不自动刷新，完全依靠手动刷新
CREATE MATERIALIZED VIEW myview (
  PRIMARY KEY (id)
) DISTRIBUTED BY HASH (id)
AS
SELECT id, name FROM base;

# 指定列建立索引，默认全部列建立索引
CREATE MATERIALIZED VIEW myview (
  INDEX (name),
  PRIMARY KEY (id)
) DISTRIBUTED BY HASH (id)
AS
SELECT id, name, age FROM base;

# 指定分区键和注释
CREATE MATERIALIZED VIEW c (
  name varchar(10),
  value double,
  KEY INDEX_ID(id) COMMENT 'id',
  CLUSTERED KEY INDEX(name, value),
  PRIMARY KEY(id)
) 
DISTRIBUTED BY hash(id)
PARTITION BY value(date_format(dat, "%Y%m%d")) LIFECYCLE 30
COMMENT 'MATERIALIZED VIEW c'
AS 
SELECT * FROM base;

使用建议

  • 创建物化视图时支持使用普通表定义中所有的参数，如分区键、二级分区键和冷热数据存储策略等。创建物化视图时建议定义分区键和主键，以提高后续查询性能。关于创建普通表支持的参数说明，请参见CREATE TABLE。
  • 若集群规格较小，尽量避免在同一时刻刷新集群内的所有物化视图，以免影响集群稳定性。
  • 物化视图和普通表一样，默认全列建立索引。为减少存储空间和写入I/O，如果不需要全列建立索引，您可以指定INDEX关键字对特定列建立索引。在物化视图中建索引的方式与普通表的语法一致，建索引的语句，请参见CREATE TABLE。