创建物化视图

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

权限要求

  • 创建视图需要有数据库或表级别的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>;

关键字说明

说明

OR REPLACE

根据是否存在重名物化视图选择对应的规则来创建物化视图,具体规则如下:

  • 若不存在重名物化视图,AnalyticDB for MySQL会直接创建一个新视图。

  • 若存在重名物化视图,AnalyticDB for MySQL会先删除原有的重名物化视图,再重新创建。

说明

集群版本为3.1.4.7或以上的AnalyticDB for MySQL集群才支持该参数。

  • 查看企业版基础版湖仓版集群的内核版本,请执行SELECT adb_version();。如需升级内核版本,请联系技术支持。

  • 查看和升级数仓版集群的内核版本,请参见查看和升级版本

mv_name

物化视图名称。

MV DEFINITION

定义物化视图中表相关的特性。

  • 物化视图使用一张普通的表结构来存储数据。创建物化视图时支持使用普通表定义中所有的参数,如分区键、分布键、索引和冷热数据存储策略等。创建物化视图时建议定义分区键和主键,以提高后续查询性能。关于创建普通表支持的参数说明,请参见CREATE TABLE

  • 物化视图和普通表一样,默认全列建立索引。为减少存储空间和写入I/O,如果不需要全列建立索引,您可以指定INDEX关键字对特定列建立索引。在物化视图中建索引的方式与普通表的语法一致,建索引的语句,请参见CREATE TABLE

  • 与创建表的语法一致,创建物化视图时也不支持定义查询中没有输出的列。

REFRESH COMPLETE

定义物化视图的刷新模式为全量刷新。

未指定物化视图的刷新模式时,默认刷新模式为全量刷新。

REFRESH FAST

定义物化视图的刷新模式为增量刷新。

说明

3.1.9.0及以上版本支持单表增量刷新物化视图;3.2.0.0及以上版本支持多表增量刷新物化视图。详情请参见增量刷新物化视图(预发)

  • 查看企业版基础版湖仓版集群的内核版本,请执行SELECT adb_version();。如需升级内核版本,请联系技术支持。

  • 查看和升级数仓版集群的内核版本,请参见查看和升级版本

ON DEMAND

按需触发刷新,即您可以在需要刷新物化视图时手动触发刷新,或根据刷新需求配置NEXT自动触发刷新。

默认全量刷新触发模式为ON DEMAND

ON OVERWRITE

物化视图引用的基表在执行INSERT OVERWRITE语句导致数据被覆盖后,会触发全量刷新,适合通过Batchload批量导入数据的场景。

配置ON OVERWRITE时,不能定义START WITHNEXT

START WITH

定义自动全量刷新物化视图时的首次刷新时间。参数选填,若不填,默认首次刷新时间为当前时间点。

说明

支持使用时间函数,但只支持精确到秒,毫秒部分会被截断。

更多关于刷新物化视图的详情,请参见全量刷新物化视图

NEXT

定义自动全量刷新物化视图时的下次刷新时间。若需要设置自动刷新,NEXT参数必填。

说明

支持使用时间函数,但只支持精确到秒,毫秒部分会被截断。

更多关于刷新物化视图的详情,请参见全量刷新物化视图

QUERY REWRITE

开启或关闭查询改写功能。开启后查询可被自动改写到物化视图上,物化视图可以被当作缓存使用。取值如下:

  • DISABLE QUERY REWRITE:关闭当前物化视图的查询改写功能。

  • ENABLE QUERY REWRITE:开启当前物化视图的查询改写功能。

说明
  • 集群版本为3.1.4或以上的AnalyticDB for MySQL集群才支持开启该功能。如何查看集群版本,请参见查看版本。如需升级版本,请提交工单联系技术支持。

  • 未指定该参数时,默认当前物化视图的查询改写功能关闭。

QUERY BODY

定义物化视图的查询主体,查询主体可以是表、逻辑视图或者物化视图。其中:

  • 需要为查询中输出的表达式列定义别名,建议使用具有实际意义的别名。例如(SUM(price) AS total_price),表示设置表达式列SUM(price)的别名为total_price

  • 物化视图查询所涉及的基表不可被删除,且基表中的列不可被删除或修改。

  • 支持使用WITH语法进行查询。

使用限制

  • 不支持对物化视图执行INSERTDELETEUPDATE操作。

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

  • 默认情况下,一个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;

相关文档