n  Doris环境：

²  交付开发组测试集群Doris 2.1.2环境

²  FE 节点：192.XX.XX.2 - 192.XX.XX.4

²  BE 节点：192.XX.XX.5 - 192.XX.XX.7

n  物化视图的理论：

Ø  物化视图是将预先计算（根据定义好的 SELECT 语句）好的数据集，存储在 Doris 中的一个特殊的表。物化视图既能对原始明细数据的任意维度分析，也能快速的对固定维度进行分析查询。

Ø  物化视图支持直查，也支持透明改写，透明改写指的是使用一组物化视图，优化器根据改写算法和代价模型自动选择可用的最优物化视图来响应查询。

Ø  Doris 可以分析查询 SQL 的结构信息，自动寻找满足要求的物化视图，并尝试进行透明改写，使用最优的物化视图来表达查询 SQL。

Ø  通过使用预计算的物化视图结果，可以大幅提高查询性能，减少计算成本。

n  物化视图的构建和维护示例：

Ø  为验证相关性能使用亿级别数据量的库表和数据创建物化视图。

基础表：

CREATE TABLE lineitem (

    l_shipdate    DATE NOT NULL,

    l_orderkey    bigint NOT NULL,

    l_linenumber  int not null,

    l_partkey     int NOT NULL,

    l_suppkey     int not null,

    l_quantity    decimal(15, 2) NOT NULL,

    l_extendedprice  decimal(15, 2) NOT NULL,

    l_discount    decimal(15, 2) NOT NULL,

    l_tax         decimal(15, 2) NOT NULL,

    l_returnflag  VARCHAR(1) NOT NULL,

    l_linestatus  VARCHAR(1) NOT NULL,

    l_commitdate  DATE NOT NULL,

    l_receiptdate DATE NOT NULL,

    l_shipinstruct VARCHAR(25) NOT NULL,

    l_shipmode     VARCHAR(10) NOT NULL,

    l_comment      VARCHAR(44) NOT NULL

)ENGINE=OLAP

DUPLICATE KEY(`l_shipdate`, `l_orderkey`)

COMMENT "OLAP"

DISTRIBUTED BY HASH(`l_orderkey`) BUCKETS 96

PROPERTIES (

    "replication_num" = "1",

    "colocate_with" = "lineitem_orders"

);

 

CREATE TABLE orders  (

    o_orderkey       bigint NOT NULL,

    o_orderdate      DATE NOT NULL,

    o_custkey        int NOT NULL,

    o_orderstatus    VARCHAR(1) NOT NULL,

    o_totalprice     decimal(15, 2) NOT NULL,

    o_orderpriority  VARCHAR(15) NOT NULL,

    o_clerk          VARCHAR(15) NOT NULL,

    o_shippriority   int NOT NULL,

    o_comment        VARCHAR(79) NOT NULL

)ENGINE=OLAP

DUPLICATE KEY(`o_orderkey`, `o_orderdate`)

COMMENT "OLAP"

DISTRIBUTED BY HASH(`o_orderkey`) BUCKETS 96

PROPERTIES (

    "replication_num" = "1",

    "colocate_with" = "lineitem_orders"

);

创建物化视图：

CREATE MATERIALIZED VIEW mv_test

        DISTRIBUTED BY RANDOM BUCKETS 6

        PROPERTIES ('replication_num' = '1')

        AS

        select l_shipdate, o_orderdate, l_partkey, l_suppkey, sum(o_totalprice) as sum_total

        from lineitem

        left join orders on lineitem.l_orderkey = orders.o_orderkey

        group by

        l_shipdate,

        o_orderdate,

        l_partkey,

        l_suppkey;

Ø  刷新物化视图

物化视图支持不同刷新策略，如定时刷新和手动刷新。也支持不同的刷新粒度，如全量刷新，分区粒度的增量刷新等。本次先全量刷新一下数据：

mysql> REFRESH MATERIALIZED VIEW mv_test COMPLETE;

Query OK, 0 rows affected (0.00 sec)

Ø  查看建好的物化视图与base表数据量情况：

n  物化视图数据刷新：

Ø  刷新原理：

物化视图是按照分区为单位进行刷新的，如果物化视图没有指定分区，那么每次都刷新物化视图的默认分区，相当于刷新物化视图的全部数据。

Ø  触发机制：

物化视图有三种触发刷新机制

手动触发：

用户通过 SQL 语句触发物化视图的刷新，目前有三种策略：

1，不关心具体刷新哪些分区，刷新完成后，物化视图的数据和基表保持同步

mysql> refresh materialized view mv_test auto;

Query OK, 0 rows affected (0.00 sec)

2，不管物化视图现存哪些数据，刷新物化视图的所有分区

mysql> REFRESH MATERIALIZED VIEW mv_test COMPLETE;

Query OK, 0 rows affected (0.01 sec)

3，不管物化视图现存哪些数据，只刷新指定的分区

mysql> refresh materialized view mv_test partitions(mv_test);

Query OK, 0 rows affected (0.00 sec)

定时触发：

通过在物化视图的创建语句中指定，如

REFRESH COMPLETE ON SCHEDULE EVERY 10 hour，代表全量刷新（refresh complete），物化视图每 10 小时刷新一次，并且刷新物化视图的所有分区。

REFRESH AUTO ON SCHEDULE EVERY 10 hour，代表全量刷新（refresh auto），物化视图每10小时刷新一次，并且自动计算需要刷新的分区。

自动触发（2.1.4版本开始支持）：

基表数据发生变更后，自动触发相关物化视图刷新，刷新的分区范围同定时触发。需要在物化视图的创建语句中指定：REFRESH ON COMMIT

如下图，构建了一个每1小时自动刷新一次的物化视图mv_1。

对其引用的基础表数据有了更新之后，到达设定的数据刷新时间点，会产生相应的task对物化视图数据进行更新并记录状态。

$ curl -s -v --location-trusted -u 'root:XXXXXX' -H "column_separator:|" \

> -H "columns: o_orderkey, o_custkey, o_orderstatus, o_totalprice, o_orderdate, o_orderpriority, o_clerk, o_shippriority, o_comment, temp" \

> -T "/data02/XXXXXX/orders.tbl.11" HTTP协议192.168.XX.XX:8035/api/tpch/orders/_stream_load;

*   Trying 192.168.XX.XX:8035...

* Connected to 192.168.XX.XX (192.168.XX.XX) port 8035 (#0)

* Server auth using Basic with user 'root'

{

    "TxnId": 9357,

    "Label": "72b74065-b628-4b8f-a376-9d07XXXXXXXX",

    "Comment": "",

    "TwoPhaseCommit": "false",

    "Status": "Success",

    "Message": "OK",

    "NumberTotalRows": 10,

    "NumberLoadedRows": 10,

    "NumberFilteredRows": 0,

    "NumberUnselectedRows": 0,

    "LoadBytes": 1138,

    "LoadTimeMs": 779,

    "BeginTxnTimeMs": 1,

    "StreamLoadPutTimeMs": 3,

    "ReadDataTimeMs": 0,

    "WriteDataTimeMs": 679,

    "CommitAndPublishTimeMs": 66

}

n  物化视图元信息：

物化视图独有的特性可以通过 mv_infos() 查看，

       select * from mv_infos("database"="tpch") where Name="mv_test";

需重点关注以下字段：

State：如果变为 SCHEMA_CHANGE，代表基表的 Schema 发生了变化，此时物化视图将不能用来透明改写(但是不影响直接查询物化视图)，下次刷新任务如果执行成功，将恢复为 NORMAL。

SchemaChangeDetail：SCHEMA_CHANGE 发生的原因

RefreshState: 物化视图最后一次任务刷新的状态，如果为FAIL，代表执行失败，可以通过 tasks() 进一步定位

SyncWithBaseTables: 是否和基表数据同步（值为1代表数据一致），如果不同步，可通过 show partitions 进一步判断哪个分区不同步

每个物化视图都会默认有一个 Job 负责刷新数据，Job 用来描述物化视图的刷新策略等信息，每次触发刷新，都会产生一个 Task， Task 用来描述具体的一次刷新信息，例如刷新用的时间，刷新了哪些分区等

Ø  查看物化视图的 Job：

Ø  查看物化视图的 Task：

n  物化视图的关键参数：

物化视图是 Doris中一个重要的特性，它允许用户创建预计算的数据表，以加速查询速度。在构建Doris物化视图时，有如下关键参数：

Ø  grace_period：查询改写时允许物化视图数据的最大延迟时间（单位：秒）

Ø  excluded_trigger_tables: 这个参数控制数据刷新时忽略的表名，逗号分割。例如table1,table2。

Ø  refresh_partition_num: 单次 Insert 语句刷新的分区数量，默认为 1。物化视图刷新时会先计算要刷新的分区列表，然后根据该配置拆分成多个 Insert 语句顺序执行。遇到失败的 Insert 语句，整个任务将停止执行。物化视图保证单个 Insert 语句的事务性，失败的 Insert 语句不会影响到已经刷新成功的分区。

Ø  workload_group: 物化视图执行刷新任务时使用的 workload_group 名称。用来限制物化视图刷新数据使用的资源，避免影响到其它业务的运行。

Ø  partition_sync_limit: 设置当基础表的分区字段为时间时（如果是字符串类型的时间，可以设置 partition_date_format），可以用此属性配置同步基表的分区范围，配合 partition_sync_time_unit 一起使用。 例如设置为 2，partition_sync_time_unit 设置为 MONTH，代表仅同步基表近 2 个月的分区和数据。最小值为 1。 随着时间的变化物化视图每次刷新时都会自动增删分区，例如物化视图现在有 2,3 两个月的数据，下个月的时候，会自动删除 2 月的数据，增加 4 月的数据。

Ø  partition_sync_time_unit: 时间单位，支持 DAY/MONTH/YEAR（默认DAY）。

Ø  partition_date_format: 分区字段的时间格式，例如"%Y-%m-%d"。

Ø  enable_nondeterministic_function: 物化视图定义 SQL 是否允许包含 nondeterministic 函数，比如 current_date(), now(), random()等，如果 是 true, 允许包含，否则不允许包含, 默认不允许包含。

可以初始构建物化视图时不指定参数，后期根据业务需求灵活添加，如：

mysql> CREATE MATERIALIZED VIEW mv_1

    ->         DISTRIBUTED BY RANDOM BUCKETS 6

    ->         PROPERTIES ('replication_num' = '1')

    ->         AS

    ->         select o_orderdate, l_partkey, l_suppkey, sum(o_totalprice) as sum_total

    ->         from lineitem

    ->         left join orders on lineitem.l_orderkey = orders.o_orderkey

    ->         group by

    ->         o_orderdate,

    ->         l_partkey,

    ->         l_suppkey;

Query OK, 0 rows affected (0.59 sec)

mysql> ALTER MATERIALIZED VIEW mv_1 set("grace_period"="3000");

Query OK, 0 rows affected (0.03 sec)

mysql> ALTER MATERIALIZED VIEW mv_1 REFRESH AUTO ON SCHEDULE EVERY 1 hour;

Query OK, 0 rows affected (0.05 sec)

n  物化视图数据查询：

Ø  直查物化视图，物化视图可以看作是表，可以像正常的表一样直接查询，如：

mysql> select * from mv_1 where o_orderdate = '1992-02-03' limit 10;

+-------------+-----------+-----------+-----------+

| o_orderdate | l_partkey | l_suppkey | sum_total |

+-------------+-----------+-----------+-----------+

| 1992-02-03  |  15888445 |    138461 | 108255.63 |

| 1992-02-03  |  19862493 |    612551 | 291355.52 |

| 1992-02-03  |   4071769 |    571778 | 107707.54 |

| 1992-02-03  |  19869930 |    619988 | 158489.24 |

| 1992-02-03  |  19810040 |     60060 | 159526.65 |

| 1992-02-03  |   9229829 |    479839 | 232008.78 |

| 1992-02-03  |     66693 |    566694 | 162115.16 |

| 1992-02-03  |  14234117 |    734146 | 130111.55 |

| 1992-02-03  |   4039642 |     39643 |  35732.36 |

| 1992-02-03  |   4696111 |    196120 | 114700.32 |

+-------------+-----------+-----------+-----------+

10 rows in set (0.44 sec)

Ø  透明改写，物化视图创建成功后，用户的查询不需要发生任何改变，也就是还是查询的 Base 表。Doris 会根据当前查询的语句去使用一组物化视图，优化器根据改写算法和代价模型自动选择可用的最优物化视图来从中读取数据并计算。