本文介绍如何创建物化视图。
权限要求
创建视图需要有数据库或表级别的CREATE权限。
刷新物化视图需要有数据库或表级别的INSERT权限。
需要有物化视图所涉及的所有表的相关列(或整个表)的SELECT权限。
如果在创建物化视图时指定物化视图为自动刷新模式,需要具备通过服务器本地(即
127.0.0.1
)或者任意IP(即'%'
)刷新视图的权限。
语法
CREATE [OR REPLACE] MATERIALIZED VIEW <mv_name>
[MV DEFINITION]
[REFRESH [COMPLETE|FAST] [ON [DEMAND |OVERWRITE] [START WITH date] [NEXT date]]]
[QUERY REWRITE]
AS
<QUERY BODY>;
关键字说明
说明 | |
| 根据是否存在重名物化视图选择对应的规则来创建物化视图,具体规则如下:
说明 集群版本为3.1.4.7或以上的AnalyticDB for MySQL集群才支持该参数。
|
| 物化视图名称。 |
| 定义物化视图中表相关的特性。
|
| 定义物化视图的刷新模式为全量刷新。 未指定物化视图的刷新模式时,默认刷新模式为全量刷新。 |
| 定义物化视图的刷新模式为增量刷新。 说明 3.1.9.0及以上版本支持单表增量刷新物化视图;3.2.0.0及以上版本支持多表增量刷新物化视图。详情请参见增量刷新物化视图(预发)。
|
| 按需触发刷新,即您可以在需要刷新物化视图时手动触发刷新,或根据刷新需求配置 默认全量刷新触发模式为 |
| 物化视图引用的基表在执行 配置 |
| 定义自动全量刷新物化视图时的首次刷新时间。参数选填,若不填,默认首次刷新时间为当前时间点。 说明 支持使用时间函数,但只支持精确到秒,毫秒部分会被截断。 更多关于刷新物化视图的详情,请参见全量刷新物化视图。 |
| 定义自动全量刷新物化视图时的下次刷新时间。若需要设置自动刷新, 说明 支持使用时间函数,但只支持精确到秒,毫秒部分会被截断。 更多关于刷新物化视图的详情,请参见全量刷新物化视图。 |
| 开启或关闭查询改写功能。开启后查询可被自动改写到物化视图上,物化视图可以被当作缓存使用。取值如下:
|
| 定义物化视图的查询主体,查询主体可以是表、逻辑视图或者物化视图。其中:
|
使用限制
不支持对物化视图执行
INSERT
、DELETE
或UPDATE
操作。不支持删除或重命名物化视图中引用的基表或基表中的列。如需修改基表需要先删除物化视图。
默认情况下,一个AnalyticDB for MySQL集群支持创建的物化视图上限如下:
集群版本低于3.1.4.7:最多支持创建8个物化视图。
集群版本等于或高于3.1.4.7:最多支持创建64个物化视图。
说明查看企业版或湖仓版集群的内核版本,请执行
SELECT adb_version();
。如需升级内核版本,请联系技术支持。
示例
创建物化视图
myview1
,每五分钟刷新一次。CREATE MATERIALIZED VIEW myview1 REFRESH NEXT now() + interval 5 minute AS SELECT count(*) as cnt FROM base;
创建物化视图
myview2
,每周一凌晨2点刷新。CREATE MATERIALIZED VIEW myview2 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;
创建物化视图
myview3
,每天凌晨2点刷新。CREATE MATERIALIZED VIEW myview3 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;
创建物化视图
myview4
,每个月第一天凌晨2点刷新。CREATE MATERIALIZED VIEW myview4 REFRESH NEXT DATE_FORMAT(last_day(now()) + interval 1 day, '%Y-%m-%d 02:00:00') AS SELECT count(*) as cnt FROM base;
创建物化视图
myview5
,只刷一次。CREATE MATERIALIZED VIEW myview5 REFRESH START WITH now() + interval 1 day AS SELECT count(*) as cnt FROM base;
创建物化视图
myview6
,不自动刷新,完全依靠手动刷新。CREATE MATERIALIZED VIEW myview6 ( PRIMARY KEY (id) ) DISTRIBUTED BY HASH (id) AS SELECT id, name FROM base;
创建物化视图
myview7
,指定列建立索引,默认全部列建立索引。CREATE MATERIALIZED VIEW myview7 ( INDEX (name), PRIMARY KEY (id) ) DISTRIBUTED BY HASH (id) AS SELECT id, name, age FROM base;
创建物化视图
myview8
,指定分区键和注释。CREATE MATERIALIZED VIEW myview8 ( 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;