电子商务网站开发人员要求,深圳app外包公司排行榜,购物网站国外,松江品牌网站建设物化视图
物化视图是将预先计算#xff08;根据定义好的 SELECT 语句#xff09;好的数据集#xff0c;存储在 Doris 中的一个特殊的表。
物化视图的出现主要是为了满足用户#xff0c;既能对原始明细数据的任意维度分析#xff0c;也能快速的对固定维度进行分析查询。 …物化视图
物化视图是将预先计算根据定义好的 SELECT 语句好的数据集存储在 Doris 中的一个特殊的表。
物化视图的出现主要是为了满足用户既能对原始明细数据的任意维度分析也能快速的对固定维度进行分析查询。
适用场景
分析需求覆盖明细数据查询以及固定维度查询两方面。查询仅涉及表中的很小一部分列或行。查询包含一些耗时处理操作比如时间很久的聚合操作等。查询需要匹配不同前缀索引。
优势
对于那些经常重复的使用相同的子查询结果的查询性能大幅提升。Doris 自动维护物化视图的数据无论是新的导入还是删除操作都能保证 Base 表和物化视图表的数据一致性无需任何额外的人工维护成本。查询时会自动匹配到最优物化视图并直接从物化视图中读取数据。
自动维护物化视图的数据会造成一些维护开销会在后面的物化视图的局限性中展开说明。
物化视图 VS Rollup
在没有物化视图功能之前用户一般都是使用 Rollup 功能通过预聚合方式提升查询效率的。但是 Rollup 具有一定的局限性他不能基于明细模型做预聚合。
物化视图则在覆盖了 Rollup 的功能的同时还能支持更丰富的聚合函数。所以物化视图其实是 Rollup 的一个超集。
也就是说之前 ALTER TABLE ADD ROLLUP 语法支持的功能现在均可以通过 CREATE MATERIALIZED VIEW 实现。
使用物化视图
Doris 系统提供了一整套对物化视图的 DDL 语法包括创建查看删除。DDL 的语法和 PostgreSQL, Oracle 都是一致的。
创建物化视图
这里首先你要根据你的查询语句的特点来决定创建一个什么样的物化视图。这里并不是说你的物化视图定义和你的某个查询语句一模一样就最好。这里有两个原则
从查询语句中抽象出多个查询共有的分组和聚合方式作为物化视图的定义。不需要给所有维度组合都创建物化视图。
首先第一个点一个物化视图如果抽象出来并且多个查询都可以匹配到这张物化视图。这种物化视图效果最好。因为物化视图的维护本身也需要消耗资源。
如果物化视图只和某个特殊的查询很贴合而其他查询均用不到这个物化视图。则会导致这张物化视图的性价比不高既占用了集群的存储资源还不能为更多的查询服务。
所以用户需要结合自己的查询语句以及数据维度信息去抽象出一些物化视图的定义。
第二点就是在实际的分析查询中并不会覆盖到所有的维度分析。所以给常用的维度组合创建物化视图即可从而到达一个空间和时间上的平衡。
创建物化视图是一个异步的操作也就是说用户成功提交创建任务后Doris 会在后台对存量的数据进行计算直到创建成功。
具体的语法可查看CREATE MATERIALIZED VIEW 。
SinceVersion 2.0.0
在Doris 2.0版本中我们对物化视图的做了一些增强(在本文的最佳实践4中有具体描述)。我们建议用户在正式的生产环境中使用物化视图前先在测试环境中确认是预期中的查询能否命中想要创建的物化视图。
如果不清楚如何验证一个查询是否命中物化视图可以阅读本文的最佳实践1。
与此同时我们不建议用户在同一张表上建多个形态类似的物化视图这可能会导致多个物化视图之间的冲突使得查询命中失败(在新优化器中这个问题会有所改善)。建议用户先在测试环境中验证物化视图和查询是否满足需求并能正常使用。
支持聚合函数
目前物化视图创建语句支持的聚合函数有
SUM, MIN, MAX (Version 0.12)COUNT, BITMAP_UNION, HLL_UNION (Version 0.13)
更新策略
为保证物化视图表和 Base 表的数据一致性, Doris 会将导入删除等对 Base 表的操作都同步到物化视图表中。并且通过增量更新的方式来提升更新效率。通过事务方式来保证原子性。
比如如果用户通过 INSERT 命令插入数据到 Base 表中则这条数据会同步插入到物化视图中。当 Base 表和物化视图表均写入成功后INSERT 命令才会成功返回。
查询自动匹配
物化视图创建成功后用户的查询不需要发生任何改变也就是还是查询的 Base 表。Doris 会根据当前查询的语句去自动选择一个最优的物化视图从物化视图中读取数据并计算。
用户可以通过 EXPLAIN 命令来检查当前查询是否使用了物化视图。
物化视图中的聚合和查询中聚合的匹配关系
物化视图聚合查询中聚合sumsumminminmaxmaxcountcountbitmap_unionbitmap_union, bitmap_union_count, count(distinct)hll_unionhll_raw_agg, hll_union_agg, ndv, approx_count_distinct
其中 bitmap 和 hll 的聚合函数在查询匹配到物化视图后查询的聚合算子会根据物化视图的表结构进行改写。详细见实例2。
查询物化视图
查看当前表都有哪些物化视图以及他们的表结构都是什么样的。通过下面命令
MySQL [test] desc mv_test all;
-----------------------------------------------------------------------------------------
| IndexName | IndexKeysType | Field | Type | Null | Key | Default | Extra |
-----------------------------------------------------------------------------------------
| mv_test | DUP_KEYS | k1 | INT | Yes | true | NULL | |
| | | k2 | BIGINT | Yes | true | NULL | |
| | | k3 | LARGEINT | Yes | true | NULL | |
| | | k4 | SMALLINT | Yes | false | NULL | NONE |
| | | | | | | | |
| mv_2 | AGG_KEYS | k2 | BIGINT | Yes | true | NULL | |
| | | k4 | SMALLINT | Yes | false | NULL | MIN |
| | | k1 | INT | Yes | false | NULL | MAX |
| | | | | | | | |
| mv_3 | AGG_KEYS | k1 | INT | Yes | true | NULL | |
| | | to_bitmap(k2) | BITMAP | No | false | | BITMAP_UNION |
| | | | | | | | |
| mv_1 | AGG_KEYS | k4 | SMALLINT | Yes | true | NULL | |
| | | k1 | BIGINT | Yes | false | NULL | SUM |
| | | k3 | LARGEINT | Yes | false | NULL | SUM |
| | | k2 | BIGINT | Yes | false | NULL | MIN |
-----------------------------------------------------------------------------------------可以看到当前 mv_test 表一共有三张物化视图mv_1, mv_2 和 mv_3以及他们的表结构。
删除物化视图
如果用户不再需要物化视图则可以通过命令删除物化视图。
具体的语法可查看DROP MATERIALIZED VIEW
查看已创建的物化视图
用户可以通过命令查看已创建的物化视图的
具体的语法可查看SHOW CREATE MATERIALIZED VIEW
取消创建物化视图 CANCEL ALTER TABLE MATERIALIZED VIEW FROM db_name.table_name最佳实践1
使用物化视图一般分为以下几个步骤
创建物化视图异步检查物化视图是否构建完成查询并自动匹配物化视图
首先是第一步创建物化视图
假设用户有一张销售记录明细表存储了每个交易的交易 id销售员售卖门店销售时间以及金额。建表语句和插入数据语句为
create table sales_records(record_id int, seller_id int, store_id int, sale_date date, sale_amt bigint) distributed by hash(record_id) properties(replication_num 1);
insert into sales_records values(1,1,1,2020-02-02,1);这张 sales_records 的表结构如下
MySQL [test] desc sales_records;
------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
------------------------------------------------
| record_id | INT | Yes | true | NULL | |
| seller_id | INT | Yes | true | NULL | |
| store_id | INT | Yes | true | NULL | |
| sale_date | DATE | Yes | false | NULL | NONE |
| sale_amt | BIGINT | Yes | false | NULL | NONE |
------------------------------------------------这时候如果用户经常对不同门店的销售量进行一个分析查询则可以给这个 sales_records 表创建一张以售卖门店分组对相同售卖门店的销售额求和的一个物化视图。创建语句如下
MySQL [test] create materialized view store_amt as select store_id, sum(sale_amt) from sales_records group by store_id;后端返回下图则说明创建物化视图任务提交成功。
Query OK, 0 rows affected (0.012 sec)第二步检查物化视图是否构建完成
由于创建物化视图是一个异步的操作用户在提交完创建物化视图任务后需要异步的通过命令检查物化视图是否构建完成。命令如下
SHOW ALTER TABLE ROLLUP FROM db_name; (Version 0.12)
SHOW ALTER TABLE MATERIALIZED VIEW FROM db_name; (Version 0.13)这个命令中 db_name 是一个参数, 你需要替换成自己真实的 db 名称。命令的结果是显示这个 db 的所有创建物化视图的任务。结果如下
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| JobId | TableName | CreateTime | FinishedTime | BaseIndexName | RollupIndexName | RollupId | TransactionId | State | Msg | Progress | Timeout |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 22036 | sales_records | 2020-07-30 20:04:28 | 2020-07-30 20:04:57 | sales_records | store_amt | 22037 | 5008 | FINISHED | | NULL | 86400 |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------其中 TableName 指的是物化视图的数据来自于哪个表RollupIndexName 指的是物化视图的名称叫什么。其中比较重要的指标是 State。
当创建物化视图任务的 State 已经变成 FINISHED 后就说明这个物化视图已经创建成功了。这就意味着查询的时候有可能自动匹配到这张物化视图了。
第三步查询
当创建完成物化视图后用户再查询不同门店的销售量时就会直接从刚才创建的物化视图 store_amt 中读取聚合好的数据。达到提升查询效率的效果。
用户的查询依旧指定查询 sales_records 表比如
SELECT store_id, sum(sale_amt) FROM sales_records GROUP BY store_id;上面查询就能自动匹配到 store_amt。用户可以通过下面命令检验当前查询是否匹配到了合适的物化视图。
EXPLAIN SELECT store_id, sum(sale_amt) FROM sales_records GROUP BY store_id;
----------------------------------------------------------------------------------------------
| Explain String |
----------------------------------------------------------------------------------------------
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS: |
| slot 4 default_cluster:test.sales_records.mv_store_id |
| slot 5 sum(default_cluster:test.sales_records.mva_SUM__sale_amt) |
| PARTITION: UNPARTITIONED |
| |
| VRESULT SINK |
| |
| 4:VEXCHANGE |
| offset: 0 |
| |
| PLAN FRAGMENT 1 |
| |
| PARTITION: HASH_PARTITIONED: slot 4 default_cluster:test.sales_records.mv_store_id |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 04 |
| UNPARTITIONED |
| |
| 3:VAGGREGATE (merge finalize) |
| | output: sum(slot 5 sum(default_cluster:test.sales_records.mva_SUM__sale_amt)) |
| | group by: slot 4 default_cluster:test.sales_records.mv_store_id |
| | cardinality-1 |
| | |
| 2:VEXCHANGE |
| offset: 0 |
| |
| PLAN FRAGMENT 2 |
| |
| PARTITION: HASH_PARTITIONED: default_cluster:test.sales_records.record_id |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 02 |
| HASH_PARTITIONED: slot 4 default_cluster:test.sales_records.mv_store_id |
| |
| 1:VAGGREGATE (update serialize) |
| | STREAMING |
| | output: sum(default_cluster:test.sales_records.mva_SUM__sale_amt) |
| | group by: default_cluster:test.sales_records.mv_store_id |
| | cardinality-1 |
| | |
| 0:VOlapScanNode |
| TABLE: default_cluster:test.sales_records(store_amt), PREAGGREGATION: ON |
| partitions1/1, tablets10/10, tabletList50028,50030,50032 ... |
| cardinality1, avgRowSize1520.0, numNodes1 |
----------------------------------------------------------------------------------------------从最底部的test.sales_records(store_amt)可以表明这个查询命中了store_amt这个物化视图。值得注意的是如果表中没有数据那么可能不会命中物化视图。
最佳实践2 PV,UV
业务场景: 计算广告的 UVPV。
假设用户的原始广告点击数据存储在 Doris那么针对广告 PV, UV 查询就可以通过创建 bitmap_union 的物化视图来提升查询速度。
通过下面语句首先创建一个存储广告点击数据明细的表包含每条点击的点击时间点击的是什么广告通过什么渠道点击以及点击的用户是谁。
create table advertiser_view_record(time date, advertiser varchar(10), channel varchar(10), user_id int) distributed by hash(time) properties(replication_num 1);
insert into advertiser_view_record values(2020-02-02,a,a,1);原始的广告点击数据表结构为
MySQL [test] desc advertiser_view_record;
------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
------------------------------------------------------
| time | DATE | Yes | true | NULL | |
| advertiser | VARCHAR(10) | Yes | true | NULL | |
| channel | VARCHAR(10) | Yes | false | NULL | NONE |
| user_id | INT | Yes | false | NULL | NONE |
------------------------------------------------------
4 rows in set (0.001 sec)创建物化视图 由于用户想要查询的是广告的 UV 值也就是需要对相同广告的用户进行一个精确去重则查询一般为 SELECT advertiser, channel, count(distinct user_id) FROM advertiser_view_record GROUP BY advertiser, channel;针对这种求 UV 的场景我们就可以创建一个带 bitmap_union 的物化视图从而达到一个预先精确去重的效果。 在 Doris 中count(distinct) 聚合的结果和 bitmap_union_count聚合的结果是完全一致的。而bitmap_union_count 等于 bitmap_union 的结果求 count 所以如果查询中涉及到 count(distinct) 则通过创建带 bitmap_union 聚合的物化视图方可加快查询。 针对这个 Case则可以创建一个根据广告和渠道分组对 user_id 进行精确去重的物化视图。 MySQL [test] create materialized view advertiser_uv as select advertiser, channel, bitmap_union(to_bitmap(user_id)) from advertiser_view_record group by advertiser, channel;
Query OK, 0 rows affected (0.012 sec)注意因为本身 user_id 是一个 INT 类型所以在 Doris 中需要先将字段通过函数 to_bitmap 转换为 bitmap 类型然后才可以进行 bitmap_union 聚合。 创建完成后, 广告点击明细表和物化视图表的表结构如下 MySQL [test] desc advertiser_view_record all;
--------------------------------------------------------------------------------------------------------------
| IndexName | IndexKeysType | Field | Type | Null | Key | Default | Extra |
--------------------------------------------------------------------------------------------------------------
| advertiser_view_record | DUP_KEYS | time | DATE | Yes | true | NULL | |
| | | advertiser | VARCHAR(10) | Yes | true | NULL | |
| | | channel | VARCHAR(10) | Yes | false | NULL | NONE |
| | | user_id | INT | Yes | false | NULL | NONE |
| | | | | | | | |
| advertiser_uv | AGG_KEYS | advertiser | VARCHAR(10) | Yes | true | NULL | |
| | | channel | VARCHAR(10) | Yes | true | NULL | |
| | | to_bitmap(user_id) | BITMAP | No | false | | BITMAP_UNION |
--------------------------------------------------------------------------------------------------------------查询自动匹配 当物化视图表创建完成后查询广告 UV 时Doris 就会自动从刚才创建好的物化视图 advertiser_uv 中查询数据。比如原始的查询语句如下 SELECT advertiser, channel, count(distinct user_id) FROM advertiser_view_record GROUP BY advertiser, channel;在选中物化视图后实际的查询会转化为 SELECT advertiser, channel, bitmap_union_count(to_bitmap(user_id)) FROM advertiser_uv GROUP BY advertiser, channel;通过 EXPLAIN 命令可以检验到 Doris 是否匹配到了物化视图 mysql [test]explain SELECT advertiser, channel, count(distinct user_id) FROM advertiser_view_record GROUP BY advertiser, channel;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Explain String |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS: |
| slot 9 default_cluster:test.advertiser_view_record.mv_advertiser |
| slot 10 default_cluster:test.advertiser_view_record.mv_channel |
| slot 11 bitmap_union_count(default_cluster:test.advertiser_view_record.mva_BITMAP_UNION__to_bitmap_with_check(user_id)) |
| PARTITION: UNPARTITIONED |
| |
| VRESULT SINK |
| |
| 4:VEXCHANGE |
| offset: 0 |
| |
| PLAN FRAGMENT 1 |
| |
| PARTITION: HASH_PARTITIONED: slot 6 default_cluster:test.advertiser_view_record.mv_advertiser, slot 7 default_cluster:test.advertiser_view_record.mv_channel |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 04 |
| UNPARTITIONED |
| |
| 3:VAGGREGATE (merge finalize) |
| | output: bitmap_union_count(slot 8 bitmap_union_count(default_cluster:test.advertiser_view_record.mva_BITMAP_UNION__to_bitmap_with_check(user_id))) |
| | group by: slot 6 default_cluster:test.advertiser_view_record.mv_advertiser, slot 7 default_cluster:test.advertiser_view_record.mv_channel |
| | cardinality-1 |
| | |
| 2:VEXCHANGE |
| offset: 0 |
| |
| PLAN FRAGMENT 2 |
| |
| PARTITION: HASH_PARTITIONED: default_cluster:test.advertiser_view_record.time |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 02 |
| HASH_PARTITIONED: slot 6 default_cluster:test.advertiser_view_record.mv_advertiser, slot 7 default_cluster:test.advertiser_view_record.mv_channel |
| |
| 1:VAGGREGATE (update serialize) |
| | STREAMING |
| | output: bitmap_union_count(default_cluster:test.advertiser_view_record.mva_BITMAP_UNION__to_bitmap_with_check(user_id)) |
| | group by: default_cluster:test.advertiser_view_record.mv_advertiser, default_cluster:test.advertiser_view_record.mv_channel |
| | cardinality-1 |
| | |
| 0:VOlapScanNode |
| TABLE: default_cluster:test.advertiser_view_record(advertiser_uv), PREAGGREGATION: ON |
| partitions1/1, tablets10/10, tabletList50075,50077,50079 ... |
| cardinality0, avgRowSize48.0, numNodes1 |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------在 EXPLAIN 的结果中首先可以看到 VOlapScanNode 命中了 advertiser_uv。也就是说查询会直接扫描物化视图的数据。说明匹配成功。 其次对于 user_id 字段求 count(distinct) 被改写为求 bitmap_union_count(to_bitmap)。也就是通过 Bitmap 的方式来达到精确去重的效果。
最佳实践3
业务场景匹配更丰富的前缀索引
用户的原始表有 k1, k2, k3 三列。其中 k1, k2 为前缀索引列。这时候如果用户查询条件中包含 where k11 and k22 就能通过索引加速查询。
但是有些情况下用户的过滤条件无法匹配到前缀索引比如 where k33。则无法通过索引提升查询速度。
创建以 k3 作为第一列的物化视图就可以解决这个问题。 创建物化视图 CREATE MATERIALIZED VIEW mv_1 as SELECT k3, k2, k1 FROM tableA ORDER BY k3;通过上面语法创建完成后物化视图中既保留了完整的明细数据且物化视图的前缀索引为 k3 列。表结构如下 MySQL [test] desc tableA all;
--------------------------------------------------------------------
| IndexName | IndexKeysType | Field | Type | Null | Key | Default | Extra |
--------------------------------------------------------------------
| tableA | DUP_KEYS | k1 | INT | Yes | true | NULL | |
| | | k2 | INT | Yes | true | NULL | |
| | | k3 | INT | Yes | true | NULL | |
| | | | | | | | |
| mv_1 | DUP_KEYS | k3 | INT | Yes | true | NULL | |
| | | k2 | INT | Yes | false | NULL | NONE |
| | | k1 | INT | Yes | false | NULL | NONE |
--------------------------------------------------------------------查询匹配 这时候如果用户的查询存在 k3 列的过滤条件是比如 select k1, k2, k3 from table A where k33;这时候查询就会直接从刚才创建的 mv_1 物化视图中读取数据。物化视图对 k3 是存在前缀索引的查询效率也会提升。
最佳实践4
SinceVersion 2.0.0
在Doris 2.0中我们对物化视图所支持的表达式做了一些增强本示例将主要体现新版本物化视图对各种表达式的支持和提前过滤。
创建一个 Base 表并插入一些数据。
create table d_table (k1 int null,k2 int not null,k3 bigint null,k4 date null
)
duplicate key (k1,k2,k3)
distributed BY hash(k1) buckets 3
properties(replication_num 1);insert into d_table select 1,1,1,2020-02-20;
insert into d_table select 2,2,2,2021-02-20;
insert into d_table select 3,-3,null,2022-02-20;创建一些物化视图。
create materialized view k1a2p2ap3ps as select abs(k1)k21,sum(abs(k22)k33) from d_table group by abs(k1)k21;
create materialized view kymd as select year(k4),month(k4) from d_table where year(k4) 2020; // 提前用where表达式过滤以减少物化视图中的数据量。用一些查询测试是否成功命中物化视图。
select abs(k1)k21,sum(abs(k22)k33) from d_table group by abs(k1)k21; // 命中k1a2p2ap3ps
select bin(abs(k1)k21),sum(abs(k22)k33) from d_table group by bin(abs(k1)k21); // 命中k1a2p2ap3ps
select year(k4),month(k4) from d_table; // 无法命中物化视图因为where条件不匹配
select year(k4)month(k4) from d_table where year(k4) 2020; // 命中kymd局限性
如果删除语句的条件列在物化视图中不存在则不能进行删除操作。如果一定要删除数据则需要先将物化视图删除然后方可删除数据。单表上过多的物化视图会影响导入的效率导入数据时物化视图和 Base 表数据是同步更新的如果一张表的物化视图表超过 10 张则有可能导致导入速度很慢。这就像单次导入需要同时导入 10 张表数据是一样的。物化视图针对 Unique Key数据模型只能改变列顺序不能起到聚合的作用所以在Unique Key模型上不能通过创建物化视图的方式对数据进行粗粒度聚合操作目前一些优化器对sql的改写行为可能会导致物化视图无法被命中例如k11-1被改写成k1between被改写成和day被改写成dayofmonth遇到这种情况需要手动调整下查询和物化视图的语句。
异常错误
DATA_QUALITY_ERROR: The data quality does not satisfy, please check your data 由于数据质量问题或者 Schema Change 内存使用超出限制导致物化视图创建失败。如果是内存问题调大memory_limitation_per_thread_for_schema_change_bytes参数即可。 注意to_bitmap 的参数仅支持正整型, 如果原始数据中存在负数会导致物化视图创建失败。String 类型的字段可使用 bitmap_hash 或 bitmap_hash64 计算 Hash 值并返回 Hash 值的 bitmap。
更多帮助
关于物化视图使用的更多详细语法及最佳实践请参阅 CREATE MATERIALIZED VIEW 和 DROP MATERIALIZED VIEW 命令手册你也可以在 MySQL 客户端命令行下输入 HELP CREATE MATERIALIZED VIEW 和HELP DROP MATERIALIZED VIEW 获取更多帮助信息。
